PHP/MySQL Search Engine

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

PHP/MySQL Search Engine

Post by kdidymus »

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:

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%'";
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:

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'>&nbsp;</td>
 
        <td align='center' valign='bottom'>
 
        <img border='0' src='../graphics/pintop.gif' width='30' height='20'></td>
 
        <td align='center' valign='bottom'>&nbsp;</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'>&nbsp;</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'>&nbsp;</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'>&nbsp;</td>
 
        <td align='center' valign='bottom'>
 
        <img border='0' src='../graphics/pintop.gif' width='30' height='20'></td>
 
        <td align='center' valign='bottom'>&nbsp;</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'>&nbsp;</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'>&nbsp;</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>";
 
?>
Many, MANY thanks for your help.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: PHP/MySQL Search Engine

Post by kdidymus »

Yet again I seem to have solved my own problem with a little imagination.

I have added a drop-down box which specifies whether the user is searching for a Forename, Surname or Year of Birth. This is "got" by the query.php and converted to $sortfield and the results are sorted by relevance ACCORDING to the user's selection.

The code is now:

Code: Select all

$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%' ORDER BY $sortfield";
This seems to work fantastically well.

Thank you anyway. I shall take myself off and read my PHP manual some more!
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: PHP/MySQL Search Engine

Post by onion2k »

It's actually surprisingly common that you'll solve your own issues if you post them here. I think it's because you have to write it down in a clear manner to post it, which clarifies the problem and lets you see a solution yourself.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: PHP/MySQL Search Engine

Post by kdidymus »

Ah but I have another problem. You see, if you select FORENAME and type in WILLIAM (for example) because the PHP code is searching forename, middlenames AND surname for the string, it brings up various people with the surname of WILLIAMS before it lists anybody whose first name is WILLIAM.

What I wanted to do is something like 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);
 
  $sortfield = $_GET['S1']; //sort field
 
if($sortfield == 'forename,surname,yearofbirth'){
  $searchfor=forename;
}else if($sortfield == 'surname,forename,yearofbirth'){
  $searchfor=surname;
}else if($sortfield == 'yearofbirth,forename,surname'){
  $searchfor=yearofbirth;
}
 
  // 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 '$searchfor' LIKE '%$var%' ORDER BY $sortfield";
But it doesn't work. I keep getting a syntax error when the code runs.

Any clue as to what I've done wrong or (even better) if there's a simple way of doing this?

Basically, I have a form which allows you to specify whether you're searching for forenames, surname or year of birth. This is the value of the submitted form field for each:

forename = forename,surname,yearofbirth
surname = surname, forename,yearofbirth
yearofbirth = yearofbirth,forename,surname

Haylp!
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: PHP/MySQL Search Engine

Post by kdidymus »

No. No. NO!

I've done it again. Applied a little logic. Now I have REMOVED the options drop-down box and regardless of what they're searching for it's run as a wildcard search and returns in surname,forename,yearofbirth order.

I think I've cracked it!

Sorry folks. I'll close the door on my way out!!

KD.
Post Reply