Page 1 of 1

Using multiple keywords in PHP / MySQL search

Posted: Thu May 29, 2008 9:05 am
by kdidymus
I've got a search function on my website which works fine, AS LONG as only one word is entered. If you enter more than one word separated by a space, plus sign or ampersand the search fails.

I've added my search code below. Does anyone know how I might be able to make it search for multiple words?

The site can be viewed at http://www.didymus.org.uk/tree.php

Thanks in advance.

Kris.

Code: Select all

<?php
/* Program: query.php
 * Desc:    Displays search results.
 */
  // 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=50; 
 
// check for an empty string and re-direct.
if ($trimmed == "")
  {
  header("Location: search2.php");
  exit;
  }
 
// check for a search parameter
if (!isset($var))
  {
  header("Location: search2.php");
  exit;
  }
?>
<html>
<head><title>Individual Biographical Information</title></head>
<body topmargin="0" leftmargin="0" rightmargin="0" bottommargin="0">
<?php
 
  //connect to your database
  include_once("*******.inc.php");
  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 (surname LIKE \"%$trimmed%\" OR forename LIKE \"%$trimmed%\" OR middlenames LIKE \"%$trimmed%\" OR yearofbirth LIKE \"%$trimmed%\") order by surname,forename"; // EDIT HERE and specify your table and field names for the SQL query
 
 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);
 $link = "http://www.didymus.org.uk/display.php?urn=";
 $rowht = "valign='top'";
 $rowcl = "bgcolor='#CCFFCC' valign='top'";
 $text = "font face='Arial' size='1'";
 $hltext = "font face='Arial' size='1' color='#000000'";
 $nw = "nowrap";
 
// If we have no results, offer A-Z as alternative.
 
if ($numrows == 0)
  {
  echo "<p align='center'><font face='Arial' size='2' color='#00000'>Sorry, your search for <b>"" . $trimmed . ""</b> returned no results.</font></p>";
 
// google
  echo "<p align='center'><font face='Arial' size='2' color='#00000'>Click </font><a href='a-zsn.php'><font face='Arial' size='2' color='#00000'>here</font></a><font face='Arial' size='2' color='#00000'> to try the A-Z index</font></p>";
exit;
  }
 
 
// 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(mysql_error());
 
 
 
// display what the person searched for
 
// begin to show results set
echo "<p align='center'><font face='Arial' size='3'><b>Results</b> for your search for <b>"" . $var . "&quot</b>";
$count = 1 + $s ;
 
// now you can display the results returned
 
  echo "<table cellspacing='0' width='450' align='center'>";
  echo "<tr><td colspan='4'><hr /></td></tr>";
  echo "<tr $rowht><td><$text><b>SURNAME</b></font></td><td><$text><b>FORENAME(S)</b></font></td><td><$text><b>BIRTH YEAR</b></font></td><td><$text><b>VIEW</b></font></td>\n</tr>";
  echo "<tr><td colspan='4'><hr /></td></tr>";
  /* Display results in a table */
  $i=0;
  while ($row = mysql_fetch_array($result))
  {
  extract($row);
  $bg = ($i%2) ? $rowht : $rowcl;
  echo "<tr $bg><td><$text>$surname</font></td><td><$text>$forename $middlenames</font></td><td><$text>$yearofbirth</font></td><td><a href='$link$urn'><$hltext>VIEW</font></td>\n</tr>";
  $i++;
  $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 align='center'>Showing results $b to $a of $numrows</p>";
  
?>

Re: Using multiple keywords in PHP / MySQL search

Posted: Thu May 29, 2008 9:29 am
by VladSun

Re: Using multiple keywords in PHP / MySQL search

Posted: Thu May 29, 2008 10:05 am
by kdidymus
I think you're right. I think I would.

However, when I run the ALTER TABLE command I get the following error:
The used table type doesn't support FULLTEXT indexes
Any idea what I've done wrong?

Kris.

Re: Using multiple keywords in PHP / MySQL search

Posted: Thu May 29, 2008 10:07 am
by VladSun
Full-text search is supported only on MyISAM table type.
:(
I suppose you are using InnoDB tables. If you don't use transactions, you may switch to MyISAM ...

Re: Using multiple keywords in PHP / MySQL search

Posted: Thu May 29, 2008 10:21 am
by kdidymus
Cool.

Done that. I'm looking at inserting the following code in to my query.php (code above), getting rid of "trimmed" and using the $var variable instead.

Code: Select all

SELECT surname, forename, middlenames, yearofbirth FROM tree
WHERE MATCH (surname,forename,middlenames,yearofbirth) AGAINST ('$var');
 
Thing is, it returns errors. Sorry to appear REALLY stupid here, but have I got this right or is there something obvious I'm overlooking?

ALSO - What do you mean by "transactions"? Will I still be able to add to and amend my database using MyISAM?

Kris.

Re: Using multiple keywords in PHP / MySQL search

Posted: Thu May 29, 2008 10:30 am
by VladSun
Echo the resulting SQL string and paste it here. Also, paste the error message.

I believe you don't use transactions - you may google for "mysql transactions" to see what's all about :)

Re: Using multiple keywords in PHP / MySQL search

Posted: Thu May 29, 2008 10:36 am
by kdidymus
When I run the search this is what I get in the results window (iframe):
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/kdidymus/public_html/query.php on line 42

Sorry, your search for "kris" returned no results.

Click here to try the A-Z index
And here is my EDITED query.php which is returning this error:

Code: Select all

<?php
/* Program: query.php
 * Desc:    Displays search results.
 */
  // Get the search variable from URL
  $var = $_GET['q'] ; //search term passed by search.php
 
// rows to return
$limit=50; 
 
// check for an empty string and re-direct.
if ($var == "")
  {
  header("Location: search2.php");
  exit;
  }
 
// check for a search parameter
if (!isset($var))
  {
  header("Location: search2.php");
  exit;
  }
?>
<html>
<head><title>Individual Biographical Information</title></head>
<body topmargin="0" leftmargin="0" rightmargin="0" bottommargin="0">
<?php
 
  //connect to your database
  include_once("*******.inc.php");
  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 surname, forename, middlenames, yearofbirth FROM tree WHERE MATCH (surname,forename,middlenames,yearofbirth) AGAINST ('$var');
"; // EDIT HERE and specify your table and field names for the SQL query
 
 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);
 $link = "http://www.didymus.org.uk/display.php?urn=";
 $rowht = "valign='top'";
 $rowcl = "bgcolor='#CCFFCC' valign='top'";
 $text = "font face='Arial' size='1'";
 $hltext = "font face='Arial' size='1' color='#000000'";
 $nw = "nowrap";
 
// If we have no results, offer A-Z as alternative.
 
if ($numrows == 0)
  {
  echo "<p align='center'><font face='Arial' size='2' color='#00000'>Sorry, your search for <b>"" . $var . ""</b> returned no results.</font></p>";
 
// google
  echo "<p align='center'><font face='Arial' size='2' color='#00000'>Click </font><a href='a-zsn.php'><font face='Arial' size='2' color='#00000'>here</font></a><font face='Arial' size='2' color='#00000'> to try the A-Z index</font></p>";
exit;
  }
 
 
// 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(mysql_error());
 
 
 
// display what the person searched for
 
// begin to show results set
echo "<p align='center'><font face='Arial' size='3'><b>Results</b> for your search for <b>"" . $var . "&quot</b>";
$count = 1 + $s ;
 
// now you can display the results returned
 
  echo "<table cellspacing='0' width='450' align='center'>";
  echo "<tr><td colspan='4'><hr /></td></tr>";
  echo "<tr $rowht><td><$text><b>SURNAME</b></font></td><td><$text><b>FORENAME(S)</b></font></td><td><$text><b>BIRTH YEAR</b></font></td><td><$text><b>VIEW</b></font></td>\n</tr>";
  echo "<tr><td colspan='4'><hr /></td></tr>";
  /* Display results in a table */
  $i=0;
  while ($row = mysql_fetch_array($result))
  {
  extract($row);
  $bg = ($i%2) ? $rowht : $rowcl;
  echo "<tr $bg><td><$text>$surname</font></td><td><$text>$forename $middlenames</font></td><td><$text>$yearofbirth</font></td><td><a href='$link$urn'><$hltext>VIEW</font></td>\n</tr>";
  $i++;
  $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 align='center'>Showing results $b to $a of $numrows</p>";
  
?>
Any ideas?!!!

Kris.

Re: Using multiple keywords in PHP / MySQL search

Posted: Thu May 29, 2008 10:52 am
by kdidymus
Okay.

So figured that bit out. Edited my query.php to read:

Code: Select all

$query = "SELECT surname,forename,middlenames FROM tree WHERE MATCH(surname,forename,middlenames) AGAINST ('$var')";
If you go to http://www.didymus.org.uk/tree.php and search for Kristian - hey presto! It returns my entry.

HOWEVER, type Kris and it doesn't find me.

Hmmm.

Kris.

Re: Using multiple keywords in PHP / MySQL search

Posted: Thu May 29, 2008 1:49 pm
by VladSun
You need to use FULLSEARCH in boolean mode if there are less than 3 rows in the table - there is a threshold defined, which has to be reached in order to have any result.

Re: Using multiple keywords in PHP / MySQL search

Posted: Fri May 30, 2008 12:40 am
by kdidymus
Yeehaaah!

I made this a boolean search and added the wildcard (*) after the $var string in my PHP code. It now works perfectly. If you type Kris, my details appear. If you type Kristian, my details appear. If you're REALLY bold and type Kris Didymus, guess what? Yep! My details appear!

You're a star. Your suggestion to use a fulltext search on an index was inspired. I never knew that existed!

Thanks again.

Kris.