Fine-tuning PHP search code. Anyone help please?
Posted: Mon Jun 02, 2008 7:15 am
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):
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.
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 " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<
Prev 10</a>  ";
}
// 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 " <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"</b> | Showing results $b to $a of $numrows</font></p>";
?>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.