Building a custom PHP search tool

How to search several columns of a MySQL table with keywords and multiple parameters

The Project - Resource Center

I want to centralise all of Redwood Resources on the site into one manageable place. To accomplish this I will need to put all our resources details into a MySQL table and give users the capability to search that table and display the results. I also want to be able to link to the page with the search results showing.

The table

Below is the structure of the table I will use, I plan on searching the title and keywords field. I have created an area within the Marketing Administration System to upload the resource details.

Field Type
id int
title varchar
url varchar
desc text
date date
type varchar
priority int
keywords text

The Search

Searching a table is a reasonably simple process but to get a more complex and powerful search tool and to save myself time I searched for a search class. I found a great search class by Stephen Bartholomew which fitted my requirements perfectly, The class can perform a full text search over multiple columns of a MySQL database table, matching entries containing all search terms parsed to it.

NOTE: I would like to point out now that I am not ordering the results in relevancy, to do that is beyond what this search class can do and not part of my requirements. The search class only brings back results that include all the keywords no partial results. This gives me an exact list of resources to what the user requires.

The search class

class MysqlSearch
{
function find($keywords)
{
 # Create a keywords array
 $keywords_array = explode(" ",$keywords);

 # Select data query
 if(!$this->searchcolumns)
 {
     $this->searchcolumns = "*";
     $search_data_sql = "SELECT ".$this->searchcolumns." FROM ".$this->table;
 }
 else
 {
     $search_data_sql = "SELECT ".$this->entry_identifier.",".$this->searchcolumns." FROM ".$this->table;
 }

 # Run query, assigning ref
 $search_data_ref = mysql_query($search_data_sql);

 # Define $search_results_array, ready for population
 # with refined results
 $search_results_array = array();

 if($search_data_ref)
 {
     while($all_data_array = mysql_fetch_array($search_data_ref))
     {
  # Get an entry indentifier
  $my_ident = $all_data_array[$this->entry_identifier];

  # Cycle each value in the product entry
  foreach($all_data_array as $entry_key=>$entry_value)
  {
      # Cycle each keyword in the keywords_array
      foreach($keywords_array as $keyword)
      {
   # If the keyword exists...
   if($keyword)
   {
       # Check if the entry_value contains the keyword
    
       if(stristr($entry_value,$keyword))
       {
    # If it does, increment the keywords_found_[keyword] array value
    # This array can also be used for relevence results
    $keywords_found_array[$keyword]++;
       }
   }
   else
   {
       # This is a fix for when a user enters a keyword with a space
       # after it.  The trailing space will cause a NULL value to
       # be entered into the array and will not be found.  If there
       # is a NULL value, we increment the keywords_found value anyway.
       $keywords_found_array[$keyword]++;
   }
   unset($keyword);
      }
    
      # Now we compare the value of $keywords_found against
      # the number of elements in the keywords array.
      # If the values do not match, then the entry does not
      # contain all keywords so do not show it.
      if(sizeof($keywords_found_array) == sizeof($keywords_array))
      {
   # If the entry contains the keywords, push the identifier onto an
   # results array, then break out of the loop.  We're not searching for relevence,
   # only the existence of the keywords, therefore we no longer need to continue searching
   array_push($search_results_array,"$my_ident");
   break;
      }
  }
  unset($keywords_found_array);
  unset($entry_key);
  unset($entry_value);
     }
 }

 $this->numresults = sizeof($search_results_array);
 # Return the results array
 return $search_results_array;
    }
    
    function setidentifier($entry_identifier)
    {
 # Set the db entry identifier
 # This is the column that the user wants returned in
 # their results array.  Generally this should be the
 # primary key of the table.
 $this->entry_identifier = $entry_identifier;
    }

    function settable($table)
    {
 # Set which table we are searching
 $this->table = $table;
    }
    
    function setsearchcolumns($columns)
    {
 $this->searchcolumns = $columns;
    }
}

$mysearch->find($searchterm) will return an array of all entries containing the search terms parsed to it. The class returns the values in the column you set as the primary key, for me it was the ID number.
You can then use a foreach loop to unwind the array and print the primary key or use it in a database call.
To limited which columns are searched, use the setsearchcolumns() method.
example: $mysearch->setsearchcolumn("title,keywords");
So with the class included to submit the search you add the code below and use a standard form input field to submit the keywords.

// Includes
require_once('search.php'); 

if($_GET['kw'] != '') {
  $kw = $_GET['kw'];
} else {
  $kw = $_POST['kw'];
}
$kw = str_replace('\"', '', $kw); 
$kw = str_replace("\'", "", $kw);
$kw = str_replace("-", " ", $kw);
$kw = makesafe($kw);

$mysearch = new MysqlSearch;
$mysearch->setidentifier("id");
$mysearch->settable("res");
$mysearch->setsearchcolumns("title,keywords");
$results_array = $mysearch->find($kw);

For my resource center I want users to be able to select which file types they are after. To do this I am using checkboxes under the text input field. Then checking the value and adding the file type to an array I then build part of the MySQL SELECT code ready for searching the resource table.

// Build $type IN()
$type = ' AND type IN(';
$displaytext = "(";
$typearray = array();

// if all values are blank search all file types
if($_POST['pdf'] == '' && $_GET['pdf'] == '' &&
$_POST['video'] == '' && $_GET['video'] == '' &&
$_POST['tutorial'] == '' && $_GET['tutorial'] == '' &&
$_POST['webcast'] == '' && $_GET['webcast'] == '') 
{
	$_GET['pdf'] = 'y';
	$_GET['video'] = 'y';
	$_GET['webcast'] = 'y';
	$_GET['tutorial'] = 'y';
}

// check through each file type
if($_POST['pdf'] == 'yes' || $_GET['pdf'] == 'y') {
	$typearray[] = 'PDF';
}
if($_POST['video'] == 'yes' || $_GET['video'] == 'y') {
	$typearray[] = 'Multimedia Demo';
}
if($_POST['tutorial'] == 'yes' || $_GET['tutorial'] == 'y') {
	$typearray[] = 'Tutorial';
}
if($_POST['webcast'] == 'yes' || $_GET['webcast'] == 'y') {
	$typearray[] = 'Webcast';
}

// Write SQL  IN() code
$max = count($typearray);
$i = 1;
foreach($typearray as $filetype) {
	if($i < $max){
		$type .= "'$filetype', ";
	} else {
		$type .= "'$filetype')";
	}
	$i++;
}

If you have a keen eye you may have spotted I have $_POST and $_GET within the code. I want the ability to link to the page with the search already done for the user and this includes the file types so I have to check both form posted data and URL query strings in my code. Below is a quick example of how you would link to the page with the search looking for brochures in PDF format.

 www.yoursite.com/search?kw=search%20term&pdf=y

We are now ready to search the resource table. I build the SELECT query in stages, first using the results from the keyword Search I add the row id’s to the query then add the code for checking file type which we built earlier. The final part of the code is how I order the results. I control the order of the results using a number system.

The Results

For the results I use my execute_query() function to get the results as an array and then go through each result displaying the title as a link a short description and use an icon for file type.

You can see the final result here: Redwood Resource Center

Published 14.01.09