Page 1 of 1

Problems with search engine code

Posted: Tue May 20, 2008 6:05 am
by kdidymus
Okay. So I know this is a matter of debugging but I'm not experienced enough to know what's wrong here. That's why I'm throwing myself (once again) at your collective and infinitely more knowledgeable mercies!

I'm trying to build a MySQL Search Engine.

It's in two parts.

PART 1 - search.php

Code: Select all

<?php
/*  Program name: search.php
 *  Description:  Search the family tree.
 */
  echo "
<html>
<head>
<title>Search</title>
</head>
 
<body>
<form name='form' action='query.php' method='get'>
  <input type='text' name='q' />
  <input type='submit' name='Submit' value='Search' />
</form>";
?>
</body></html>
PART 2 - query.php

Code: Select all

<?php
 
  // Get the search variable from URL
  $var = $_GET['q'] ; //search term passed by search.php
  $trimmed = trim($var); //trim whitespace from the stored variable
 
// rows to return
$limit=10; 
 
// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }
 
// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }
  //connect to your database
  include_once("*******.inc");
  $cxn = mysql_connect("$host","$user","$password"); //(host, username, password)
 
  //specify database
  mysql_select_db($database) or die("Unable to select database");
 
// Build SQL Query  
  $query = "select * from tree where urn,surname,forename,yearofbirth like \"%$trimmed%\"  
  order by surname"; // EDIT HERE and specify your table and field names for the SQL query
 
 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);
 
// If we have no results, offer a google search as an alternative
 
if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";
 
// google
 echo "<p><a href=\"http://www.google.com/search?q=" 
  . $trimmed . "\" target=\"_blank\" title=\"Look up 
  " . $trimmed . " on Google\">Click here</a> to try the 
  search on google</p>";
  }
 
// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }
 
// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");
 
// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";
 
// begin to show results set
echo "Results";
$count = 1 + $s ;
 
// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  $title = $row["1st_field"];
 
  echo "$count.)&nbsp;$title" ;
  $count++ ;
  }
 
$currPage = (($s/$limit) + 1);
 
//break before paging
  echo "<br />";
 
  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&nbsp&nbsp;";
  }
 
// calculate number of pages needing links
  $pages=intval($numrows/$limit);
 
// $pages now contains int of pages needed unless there is a remainder from division
 
  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }
 
// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
 
  // not last page so give NEXT link
  $news=$s+$limit;
 
  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }
 
$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
  
?>
It all looks fine but when I try a search I get the following error:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/kdidymus/public_html/query.php on line 35

Results
Sorry, your search: "cosway" returned zero results

Click here to try the search on google

Couldn't execute query
Any clue as to what I'm doing wrong here? I didn't write the script. I've adapted it from an example one found on the web.

Thanks in advance.

Kris.

Re: Problems with search engine code

Posted: Tue May 20, 2008 8:55 am
by kdidymus
I've debugged a little and the following additional info might help:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'surname,forename,yearofbirth like "%cosway%" order by surname,forename limit 0,1' at line 1
It's driving me mad!

KD.

Re: Problems with search engine code

Posted: Tue May 20, 2008 1:19 pm
by califdon
// Build SQL Query
$query = "select * from tree where urn,surname,forename,yearofbirth like \"%$trimmed%\"
order by surname";
When you get an SQL error that says "check the manual that corresponds to your MySQL server version for the right syntax to use near..." it means that the place where MySQL couldn't understand what you are asking occurred just before the point that it references. In this case, it said "near surname...", so what it is confused by is the comma following "urn". A comma isn't part of the syntax of the WHERE clause. Look at the syntax for a WHERE clause and figure out what you should be doing: http://w3schools.com/sql/sql_where.asp

Re: Problems with search engine code

Posted: Wed May 21, 2008 3:07 am
by kdidymus
Don.

You're a star.

Code: Select all

$query = "select * from tree WHERE (urn LIKE \"%$trimmed%\" OR surname LIKE \"%$trimmed%\" OR forename LIKE \"%$trimmed%\" OR yearofbirth LIKE \"%$trimmed%\") order by surname,forename";
Worked like a dream. Check out http://www.didymus.org.uk/search.php and search for e.g. cosway, didymus or sarah.

Thank you.

KD.

Re: Problems with search engine code

Posted: Wed May 21, 2008 3:19 pm
by califdon
Glad you resolved it. Nice beginning on your genealogical site! Those are always interesting to develop and involve non-trivial technical issues. If you encounter other problems, return here and we'll see if we can help you.

Re: Problems with search engine code

Posted: Thu May 22, 2008 9:00 am
by kdidymus
I've found nothing but helpful people on this forum. I've posted a number of problems and there's always someone on hand to help out.

I already have a working HTML site but decided to change to a PHP / MySQL format to make life easier. It's in it's infant stages and I want to make sure the code works properly before adding the final flashy touches.

You can rest assured I'll be popping back from time to time to get some much needed help!

Thanks again for yours.

KD.