Using multiple keywords in PHP / MySQL search

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

Using multiple keywords in PHP / MySQL search

Post 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>";
  
?>
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Using multiple keywords in PHP / MySQL search

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Using multiple keywords in PHP / MySQL search

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Using multiple keywords in PHP / MySQL search

Post 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 ...
There are 10 types of people in this world, those who understand binary and those who don't
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Using multiple keywords in PHP / MySQL search

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Using multiple keywords in PHP / MySQL search

Post 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 :)
There are 10 types of people in this world, those who understand binary and those who don't
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Using multiple keywords in PHP / MySQL search

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

Re: Using multiple keywords in PHP / MySQL search

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Using multiple keywords in PHP / MySQL search

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Using multiple keywords in PHP / MySQL search

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