Problems with search engine code

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Problems with search engine code

Post 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.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Problems with search engine code

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Problems with search engine code

Post 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
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Problems with search engine code

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Problems with search engine code

Post 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.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Problems with search engine code

Post 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.
Post Reply