How to: PHP MySQL search engine | Part 2

Posted March 12, 2010 by Nick Vogt in Programming

See this post for a better search engine implementation.
-------

This guide / tutorial is a follow up to my previous PHP MySQL search engine guide. In this guide, we will be implementing a couple of useful search features into our search engine. The features we will be adding are a checkbox that will determine if we want to match case or not, and the ability to use an asterisk (*) to search for partial words

Matching case or not is very easy to implement using a simple conditional statement:

<?php
if($_GET['case'] != 'match')
{
$qry = strtolower($qry);
}

if($_GET['case'] != 'match')
{
$searchin = strtolower($searchin);
}
?>

If the match case checkbox is not selected, it converts the search query to lower case and the search keywords to lower case.

Next we change our search engine to by default only match full words, unless an asterisk is detected at the end of the term. A search for "star" will no longer turn up "started" or "stargate", but a search for "star*" will. We do this by adding a space to the beginning and end of the search keywords like so:

<?php
$searchin = ' ' . $sqlrow[title] . ' ';
$searchin.= $sqlrow[description] . ' ';
$searchin.= $sqlrow[tags] . ' ';
?>

We then use substr to see if there is an asterisk at the end of the search query term (we do this for each one in the array); if there is we only add a space to the beginning; if there isn't we add a space to the beginning and end. Notice also that we created a temporary variable called $term, which will store the value for each iteration through the search query array.

<?php
if(substr($value, -1) == '*')
{
$term = ' ' . substr($value, 0, -1);
}
else
{
$term = ' ' . $value . ' ';
}
?>

Here is the code along with some html:

<?php
mysql_connect('ACCOUNT', 'USERNAME', 'PASSWORD');
mysql_select_db('DATABASE');

$qry = $_GET['query'];
if(isset($qry))
{
  $qry = trim($qry);
  if($_GET['case'] != 'match')
  {
    $qry = strtolower($qry);
  }
  for($i = 1; $i <= substr_count($qry, ' '); $i ++)
  {
    $qry = str_replace('  ', ' ', $qry);
  }
  $qryarray = explode(' ', $qry);
  $qryarray = array_unique($qryarray);
  $resultarray = array();
  $sqlqry = mysql_query('SELECT * FROM mypost');
  for($i = 1; $i <= mysql_num_rows($sqlqry); $i ++)
  {
    $sqlrow = mysql_fetch_array($sqlqry);
    $searchin = ' ' . $sqlrow[title] . ' ';
    $searchin.= $sqlrow[description] . ' ';
    $searchin.= $sqlrow[tags] . ' ';
    if($_GET['case'] != 'match')
    {
      $searchin = strtolower($searchin);
    }
    $match = 'yes';
    foreach($qryarray as $key => $value)
    {
      if(substr($value, -1) == '*')
      {
        $term = ' ' . substr($value, 0, -1);
      }
      else
      {
        $term = ' ' . $value . ' ';
      }
      if(strpos($searchin, $term) === false)
      {
        $match = 'no';
        break;
      }
    }
    if($match == 'yes')
    {
      $resultarray[] = array(
      'title' => $sqlrow[title],
      'description' => $sqlrow[description],
      'tags' => $sqlrow[tags]);
    }
  }
}
?>
<html>
<body>

<form action="index.php" method="get">
<input type="text" name="query" value="<?php echo $_GET['query']; ?>">
<input type="checkbox" name="case" value="match"
<?php if($_GET['case'] == 'match') echo ' checked="checked"'; ?>>
Match case
<input type="submit" value="Search">
</form><br><br>

<?php
foreach($resultarray as $key => $value)
{
  echo $value[title] . '<br>';
  echo $value[description] . '<br>';
  echo $value[tags] . '<hr><br>';
}
?>

</body>
</html>

Tags	php mysql search

Comment on this post

Features

Post Series

Popular Tags