Fine-tuning PHP search code. Anyone help please?

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

Fine-tuning PHP search code. Anyone help please?

Post by kdidymus »

Okay. So my site is now on-line, working well and I'm frantically adding my ancestors to my MySQL dB.

But I've come across an anomaly which I'm hoping someone may be able to assist me with.

First, please visit my site at http://www.didymus.org.uk and use the search engine on the left hand side to search for Walter Bending.

The results will show up after a few seconds. FIRST on that list is a John Bending with Walter Bending being 14th down the list!

Surely the search engine should list them in order of relevance?

Here is the code for my query.php page (the PHP page the search parameter is passed to):

Code: Select all

<?php
/* Program: query.php
 * Desc:    Displays search results.
 */
  // Get the search variable from URL
  $var = $_GET['q']; //search term
  $var = ucwords ($var);
 
// rows to return
$limit=2000; 
 
// 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 urn,surname,forename,middlenames,yearofbirth,relative FROM tree WHERE MATCH(surname,forename,middlenames,yearofbirth) AGAINST ('$var*' IN BOOLEAN MODE)";
 // 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
$count = 1 + $s ;
 
// now you can display the results returned
 
  echo "<table cellspacing='0' width='450' align='center'>";
  echo "<tr><td colspan='5'><hr /></td></tr>";
  echo "<tr $rowht><td><$text><b>SURNAME</b></font></td><td></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='5'><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><img src='tree/graphics/$relative.gif'></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'><font face='Arial' size='1' colour='#000000'>Search for <b>"$var&quot</b> | Showing results $b to $a of $numrows</font></p>";
  
?>
If it helps, I'm using a MyISAM database which is uses two indices. The first one is called PRIMARY and works on the Unique Reference Number (URN) of my ancestors. The second one (the one we're interested in) is called Search and works on the surname, forename, middlenames and yearofbirth columns (in that order).

It's probably something really simple but not being particularly technically minded...!

Also, I'm aware of SQL Injection. Is there a way to ensure that my query page is secure?!

I've tried using:

$var = mysql_real_escape_string($_GET['q']);

instead of:

$var = $_GET['q']; //search term

But this produces numerous errors.

Many thanks in advance.

Kris.
Last edited by kdidymus on Mon Jun 02, 2008 9:15 am, edited 2 times in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Fine-tuning PHP search code. Anyone help please?

Post by VladSun »

kdidymus wrote:Surely the search engine should list them in order of relevance?
If you use FULLTEXT search IN BOOLEAN MODE, you loose the relevance order.
http://dev.mysql.com/doc/refman/5.0/en/ ... olean.html
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: Fine-tuning PHP search code. Anyone help please?

Post by kdidymus »

Ah. Okay. This all comes down to my original conundrum.

WITHOUT Boolean, when I searched for Kris, it returned no results. When I searched for Kristian it returned my details.

I figured Boolean would provide the most efficient search option and I guess it does. But it's annoying that my visitors have to scan down a long list of results to find the one relevant to their search.

Does anybody know the best way of configuring the PHP so that fragments of words (i.e. Kris instead of Kristian) are returned, as well as searches for multiple words WITHOUT using Boolean?

TIA.

Kris.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Fine-tuning PHP search code. Anyone help please?

Post by kdidymus »

I found the following entry on another forum. Would this work for me and if so, any idea where I'd start?!
help

--------------------------------------------------------------------------------

here's what you wanna do:

take the search string and work it so it has pluses instead of spaces and commas:

$search_modded=str_replace(",","",$_GET[search]);
$search_modded=str_replace(" ","+",$search_modded);
$search_modded="+".$search_modded;

then....

SELECT *, ( (1.3 * (MATCH(title) AGAINST
('$search_modded' IN BOOLEAN MODE))) +
(0.6 * (MATCH(description) AGAINST ('$search_modded' IN BOOLEAN MODE)))
) AS relevance FROM jobs
WHERE ( MATCH(title,description) AGAINST
('$search_modded' IN BOOLEAN MODE) ) ORDER BY relevance DESC

in this example you can actually adjust the weight of the 2 columns that are searched...(title and description)...and it sorts it by relevance pretty good...

i got this off of http://dev.mysql.com/doc ... after searching for hours and hours of course...

if you want to see a working example go to

http://www.cbcjobs.com
Kris.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Fine-tuning PHP search code. Anyone help please?

Post by kdidymus »

Been playing with this:

Code: Select all

<?php
/* Program: query.php
 * Desc:    Displays search results.
 */
  // Get the search variable from URL
  $var = $_GET['q']; //search term
  $var = ucwords ($var);
 
// 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  
$search_modded=str_replace(",","",$_GET[$var]);
$search_modded=str_replace(" ","+",$search_modded);
$search_modded="+".$search_modded;
 
$query = "SELECT urn,surname,forename,middlenames,yearofbirth,relative ( (1.3 * (MATCH(urn) AGAINST 
('$search_modded' IN BOOLEAN MODE))) + 
(0.6 * (MATCH(surname) AGAINST ('$search_modded' IN BOOLEAN MODE))) + (0.6 * (MATCH(forename) AGAINST ('$search_modded' IN BOOLEAN MODE))) + (0.6 * (MATCH(middlenames) AGAINST ('$search_modded' IN BOOLEAN MODE))) + (0.6 * (MATCH(yearofbirth) AGAINST ('$search_modded' IN BOOLEAN MODE)))
) AS relevance FROM tree
WHERE ( MATCH(urn,surname,forename,middlenames,yearofbirth) AGAINST ('$search_modded' IN BOOLEAN MODE) ) ORDER BY relevance DESC";
 
 // 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='5'><hr /></td></tr>";
  echo "<tr $rowht><td><$text><b>SURNAME</b></font></td><td></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='5'><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><img src='tree/graphics/$relative.gif'></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>";
  
?>
All looks fine but when I try it out 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 52

Sorry, your search for "Kris Didymus" returned no results.

Click here to try the A-Z index
Hmm. I think I should quit trying to "tweak" and accept that the thing works, albeit not as I wanted it to!!
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Fine-tuning PHP search code. Anyone help please?

Post by kdidymus »

Okay. So I've restored my site to use a boolean search. If I type Walter in to my search engine it returns only those with Walter as a forename or middlename. If I type Walter Bending, it brings him up but he's fourteenth on the list!

What I can't seem to get my head around is how best to program the query.php.

Do I stick with a boolean search and lose the relevance (I've tried variations to add relevance to the returned results but to no avail) or is there a way to fine-tune all of this?

The original problem (and the reason I went for a boolean search) is that if I typed "Ann" it would only return that exact string and not words which include Ann (i.e. Anne, Annie, Annabel etc.).

I'll include my query.php for the final time below. If anybody can help I'd be extremely grateful.

Kris.

Code: Select all

<?php
/* Program: query.php
 * Desc:    Displays search results.
 */
  // Get the search variable from URL
  $var = $_GET['q']; //search term
  $var = ucwords ($var);
 
// 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 urn,surname,forename,middlenames,yearofbirth,relative FROM tree WHERE MATCH(surname,forename,middlenames,yearofbirth) AGAINST ('$var*' IN BOOLEAN MODE)";
 
 $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
$count = 1 + $s ;
 
// now you can display the results returned
 
  echo "<table cellspacing='0' width='450' align='center'>";
  echo "<tr><td colspan='5'><hr /></td></tr>";
  echo "<tr $rowht><td><$text><b>SURNAME</b></font></td><td></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='5'><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><img src='tree/graphics/$relative.gif'></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'><font face='Arial' size='1' color='#000000'>Searched for <b>"" . $var . "&quot</b> | Showing results $b to $a of $numrows</font></p>";
  
?>
Post Reply