PHP/MySQL Search Engine
Posted: Fri Oct 17, 2008 5:31 am
Hello again. Long time no type!
Need a little help with putting the finishing touches to my website. The site is nearly complete but the one complaint I get time and time again is that when people use the search engine, the results are not sorted alphabetically.
http://www.didymus.org.uk/tree.php
Try typing in Alfred or William in the search box (top left) and hitting "Go!" You'll see that my code (below) finds all instances of these names and returns them but the results are NOT sorted in any order.
This is my existing query:
What I'd LIKE is to have the Alfreds and Williams sorted by SURNAME and then YEAR OF BIRTH.
The other important factor is returning PARTIAL matches. For example, I want Kris to be found within the name Kristian and Ann to be found within the name Anne.
Anybody have any idea how to best configure my search engine so that it finds a string (including the wildcards shown above) AND sorts the results in to a sensible order?
My full search engine code is shown below:
Many, MANY thanks for your help.
Need a little help with putting the finishing touches to my website. The site is nearly complete but the one complaint I get time and time again is that when people use the search engine, the results are not sorted alphabetically.
http://www.didymus.org.uk/tree.php
Try typing in Alfred or William in the search box (top left) and hitting "Go!" You'll see that my code (below) finds all instances of these names and returns them but the results are NOT sorted in any order.
This is my existing query:
Code: Select all
// Build SQL Query
$query = "SELECT urn,surname,forename,middlenames,yearofbirth,relative,bloodline FROM tree WHERE surname LIKE '%$var%' OR forename LIKE '%$var%' OR middlenames LIKE '%$var%' OR yearofbirth LIKE '%$var%'";The other important factor is returning PARTIAL matches. For example, I want Kris to be found within the name Kristian and Ann to be found within the name Anne.
Anybody have any idea how to best configure my search engine so that it finds a string (including the wildcards shown above) AND sorts the results in to a sensible order?
My full search engine code is shown below:
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>
<meta http-equiv='Content-Language' content='en-gb'>
<meta http-equiv='Content-Type' content='text/html; charset=windows-1252'>
<title>Didymus.org.uk | Search Results</title>
</head>
<body topmargin='0' leftmargin='0' rightmargin='0' bottommargin='0' bgcolor='#FFFFFF'>
<?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,bloodline FROM tree WHERE surname LIKE '%$var%' OR forename LIKE '%$var%' OR middlenames LIKE '%$var%' OR yearofbirth LIKE '%$var%'";
$numresults=mysql_query($query) or die(mysql_error());
$numrows=mysql_num_rows($numresults);
$link = "display.php?urn=";
$text = "font face='Arial' size='1'";
// If we have no results, offer A-Z as alternative.
if ($numrows == 0)
{
echo "<div align='center'>
<table border='0' style='border-collapse: collapse' cellpadding='0' id='table1'>
<tr>
<td align='center' valign='bottom'> </td>
<td align='center' valign='bottom'>
<img border='0' src='../graphics/pintop.gif' width='30' height='20'></td>
<td align='center' valign='bottom'> </td>
</tr>
<tr>
<td align='center' valign='top' bgcolor='#faf98e' height='11'></td>
<td align='center' valign='top' bgcolor='#faf98e' height='11'>
<img border='0' src='../graphics/pinbase.gif' width='30' height='11'></td>
<td align='left' valign='top' bgcolor='#faf98e' width='5' height='11' background='../graphics/ps2.gif'>
<img border='0' src='../graphics/ps1.gif' width='5' height='11'></td>
</tr>
<tr>
<td bgcolor='#faf98e' align='center'> </td>
<td bgcolor='#faf98e' align='center'>
<table border='0' cellspacing='0' id='table2'>
<tr>
<td align='center'>
<p><img border='0' src='../graphics/magnify.gif' width='24' height='24'><font face='Arial'><b>NO MATCHES</b></font><p><font face='Arial' size='1'><b>Sorry but we can't find "$var" in our database. Here are some hints to help you:</b><ul><li><p align='left'>Enter ONE word (i.e. <i>Didymus</i>, <i>Mark</i> or <i>1975</i>)</li><li><p align='left'>Search ONLY by Surname, Forename, Middle Name or Year of Birth</li><li><p align='left'>Check your spelling and try alternative spellings</li><li><p align='left'>Try using the A-Z Index by Surname or Forename</li></ul></font></p>
<p></p>
</td>
</tr>
</table>
</td>
<td bgcolor='#faf98e' width='5' background='../graphics/ps2.gif'> </td>
</tr>
<tr>
<td bgcolor='#faf98e' height='5' background='../graphics/ps4.gif'></td>
<td bgcolor='#faf98e' height='5' background='../graphics/ps5.gif'></td>
<td bgcolor='#faf98e' height='5' background='../graphics/ps3.gif'></td>
</tr>
</table>
</div>";
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 "<div align='center'>
<table border='0' style='border-collapse: collapse' cellpadding='0' id='table1'>
<tr>
<td align='center' valign='bottom'> </td>
<td align='center' valign='bottom'>
<img border='0' src='../graphics/pintop.gif' width='30' height='20'></td>
<td align='center' valign='bottom'> </td>
</tr>
<tr>
<td align='center' valign='top' bgcolor='#faf98e' height='11'></td>
<td align='center' valign='top' bgcolor='#faf98e' height='11'>
<img border='0' src='../graphics/pinbase.gif' width='30' height='11'></td>
<td align='left' valign='top' bgcolor='#faf98e' width='5' height='11' background='../graphics/ps2.gif'>
<img border='0' src='../graphics/ps1.gif' width='5' height='11'></td>
</tr>
<tr>
<td bgcolor='#faf98e' align='center'> </td>
<td bgcolor='#faf98e' align='center'>
<table border='0' cellspacing='0' id='table2'>
<tr>
<td align='center'>
<p align='center'><b><font face='Arial'>
<img border='0' src='../graphics/magnify.gif' width='24' height='24'>SEARCH RESULTS</font></b></p>
<p><font face='Arial' size='1'>You searched for <b>"$var"</b>. Showing $numrows hit(s) from our database:</font></p>
<div align='center'>
<table border='0' width='100%' cellspacing='0' cellpadding='0' id='table3'>
<tr>
<td align='center' valign='top'><table border='0' style='border-collapse: collapse' cellpadding='0' id='table4'>
<tr><td align='left' valign='top' height='10'></td>
<td width='10' align='left' valign='top' height='10'></td>
<td align='left' valign='top' height='10'></td>
<td width='10' align='left' valign='top' height='10'></td>
<td align='left' valign='top' height='10'></td>
<td width='10' align='left' valign='top' height='10'></td>
<td align='left' valign='top' height='10'></td>
<td width='10' align='left' valign='top' height='10'></td>
<td align='left' valign='top' height='10'></td>
</tr>
<tr><td align='left' valign='top'>
<img border='0' src='../graphics/key1.gif'></td>
<td width='10' align='left' valign='top'></td>
<td align='left' valign='top'>
<font face='Arial' size='1'><b>SURNAME</b></font></td>
<td width='10' align='left' valign='top'></td>
<td align='left' valign='top'>
<font face='Arial' size='1'><b>FORENAME(S)</b></font></td>
<td width='10' align='left' valign='top'></td>
<td align='left' valign='top'>
<font face='Arial' size='1'><b>YEAR</b></font></td>
<td width='10' align='left' valign='top'></td>
<td align='left' valign='top'>
<font face='Arial' size='1'><b>VIEW</b></font></td>
</tr><tr><td height='5' style='border-left-width: 1px; border-right-width: 1px; border-top-width: 1px; border-bottom-style: solid; border-bottom-width: 1px; padding: 0' bordercolor='#000000'></td><td height='5' style='border-left-width: 1px; border-right-width: 1px; border-top-width: 1px; border-bottom-style: solid; border-bottom-width: 1px; padding: 0' bordercolor='#000000'></td><td height='5' style='border-left-width: 1px; border-right-width: 1px; border-top-width: 1px; border-bottom-style: solid; border-bottom-width: 1px; padding: 0' bordercolor='#000000'></td><td height='5' style='border-left-width: 1px; border-right-width: 1px; border-top-width: 1px; border-bottom-style: solid; border-bottom-width: 1px; padding: 0' bordercolor='#000000'></td><td height='5' style='border-left-width: 1px; border-right-width: 1px; border-top-width: 1px; border-bottom-style: solid; border-bottom-width: 1px; padding: 0' bordercolor='#000000'></td><td height='5' style='border-left-width: 1px; border-right-width: 1px; border-top-width: 1px; border-bottom-style: solid; border-bottom-width: 1px; padding: 0' bordercolor='#000000'></td><td height='5' style='border-left-width: 1px; border-right-width: 1px; border-top-width: 1px; border-bottom-style: solid; border-bottom-width: 1px; padding: 0' bordercolor='#000000'></td><td height='5' style='border-left-width: 1px; border-right-width: 1px; border-top-width: 1px; border-bottom-style: solid; border-bottom-width: 1px; padding: 0' bordercolor='#000000'></td><td height='5' style='border-left-width: 1px; border-right-width: 1px; border-top-width: 1px; border-bottom-style: solid; border-bottom-width: 1px; padding: 0' bordercolor='#000000'></td><tr><td height='5'></td></tr></tr>";
/* Display results in a table */
$i=0;
while ($row = mysql_fetch_array($result))
{
extract($row);
echo " <tr><td align='left' valign='middle'><img src='../graphics/$relative.gif'</td>
<td width='10' align='left' valign='middle'></td>
<td align='left' valign='middle'><$text><font color='#$bloodline'>$surname</font></td>
<td width='10' align='left' valign='middle'></td>
<td align='left' valign='middle'><$text><font color='#$bloodline'>$forename $middlenames</td>
<td width='10' align='left' valign='middle'></td>
<td align='left' valign='middle'><$text><font color='#$bloodline'>$yearofbirth</font></td>
<td width='10' align='left' valign='middle'></td>
<td align='left' valign='middle'><a href='$link$urn'><$text><font color='#$bloodline'>VIEW</font></a></td>
</tr>";
$i++;
$count++;
}
echo "
</table></td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</td>
<td bgcolor='#faf98e' width='5' background='../graphics/ps2.gif'> </td>
</tr>
<tr>
<td bgcolor='#faf98e' height='5' background='../graphics/ps4.gif'></td>
<td bgcolor='#faf98e' height='5' background='../graphics/ps5.gif'></td>
<td bgcolor='#faf98e' height='5' background='../graphics/ps3.gif'></td>
</tr>
</table>
</body>
</html>";
?>