Building a PHP and MySQL search function
I’ve been working on the search feature of komparison. Specifically, making sure multi-word searches like peanut butter crackers work well.
Let’s say we have some a food item stored as Crackers, wheat, sandwich, with peanut butter filling and the user searches for peanut butter crackers. If the search is coded simplistically like:
SELECT * FROM some_table WHERE some_field LIKE '%peanut butter crackers%'
or:
SELECT * FROM some_table WHERE some_field LIKE '%peanut%butter%crackers%'
Then we won’t get any results because the ordering is wrong: “peanut butter crackers” is not like “crackers, peanut butter”.
What we want to so is search for each query term individually:
- peanut
- butter
- cracker
So our query would look something like this:
SELECT * FROM some_table WHERE (some_field LIKE '%peanut%' AND some_field LIKE '%butter%' AND some_field LIKE '%crackers%')
Here is some simple code that accomplishes what we just discussed: PHP: Multi-word MySQL Search Algorithm and Output . There is a lot more stuff we can do: spell checking, suggesting alternative queries, ordering search results (the list goes on and on!) So that means I still have a lot of work to do on my search function. There are oodles of features that I want to implement. But you have to start somewhere and right now I have a basic search feature implemented.
Here are some of the pages I’ve been reading to brush up on search topics:
- search algorithm classes in PHP – PHPBuilder.com
- How do you implement a “Did you mean”? – Stack Overflow
- “Similar Posts” like functionality using MS SQL Server? – Stack Overflow
- How to Write a Spelling Corrector (in Python)
- Simple Keyword Relevance — r937.com
- search algorithm php – Google Search
- Word comparison algorithm – Stack Overflow
- Book: Foundations of Statistical Natural Language Processing – Christopher D. Manning, Hinrich Schuetze
- Pro PHP Security / Preventing SQL Injection (page 1)
- Pro PHP Security / Preventing SQL Injection (page 2)
PHP functions for advanced searching:
I hope these links have been helpful if you’re working on something similar :-).
I was very glad to find the link to: “PHP: Multi-word MySQL Search Algorithm and Output”, but after 6 hours of trying to make it work, I’m stumped! It seems that it works on older versions of php, but not on my 5.1 version. Can you help me in any way?
Catherine,
If JD is using it on komparison then it should work with PHP5, our servers are running PHP 5.2.
Yo guys. I did have to make some changes to get the code to work. I wanted it to generate the multiword SQL querystring for me, so I modified it to be a function. The function now simply returns a SQL querystring, which you can then use however you like. Here’s a rather ugly version of the code, hope it helps:
// call the function, it returns a query string
$queryString = multiquery("some search terms", "table_to_select_from");
//print the querystring, use this querystring to get whatever recordset you want
echo $queryString;
// Original code: http://www.acuras.co.uk/articles/2-php-multi-word-mysql-search-algorithm-and-output
function multiquery($multiquery, $vartable) {
// escape the input string, I know mysql_escape_string is deprecated, but I don't want to mess with another function tonight...
$multiquery = mysql_escape_string($multiquery);
// checks if a search has been submitted
if(!empty($multiquery))
{
// the table to search
$table = $vartable;
// explode search words into an array
$arraySearch = explode(" ", $multiquery);
// table fields to search
$arrayFields = array(0 => "Long_Desc", 1 => "Shrt_Desc", 2 => "ComName", 3 => "ManufacName");
$countSearch = count($arraySearch);
$a = 0;
$b = 0;
$query = "SELECT * FROM ".$table." WHERE (";
$countFields = count($arrayFields);
while ($a < $countFields)
{
while ($b < $countSearch)
{
$query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
$b++;
if ($b < $countSearch)
{
$query = $query." AND ";
}
}
$b = 0;
$a++;
if ($a < $countFields)
{
$query = $query.") OR (";
}
}
// just make a querystring "$query", don't actually get the query_result here
$query = $query.")";
//$query_result = mysql_query($query);
//return the querystring for use outside the function
return $query;
//disable this portion, since we're not working with a query result anymore (just a querystring to be used later)
if(1 < 1)
{
echo '<p>No matches found for "'.$search.'"</p>';
}
else
{
echo '<p>Search Results for "'.$search.'":</p>'."\n\n";
// output list of articles
while($row = mysql_fetch_assoc($query_result))
{
// output whatever you want here for each search result
echo '<a href="index.php?id='.$row['id'].'">'.$row['title'].'</a><br />';
}
}
}
else
{
// display a welcome page
}
}
// end search function
THE END.
Just a note on that code, it doesn’t appear to use any input string escaping, which means grabbing from any of the PHP superglobals, and any other untrusted source, will need to be escaped before running through the function. Otherwise you open yourself up to nasty SQL injection attacks.
Thanks for pointing that out John. I clean the input string before I send it to the function.
However, it would be wise to include some rudimentary cleaning [string escaping] in the function itself (just in case it ever got called with raw input). So I’m going to update the code in this post. 🙂