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:

PHP functions for advanced searching:

I hope these links have been helpful if you’re working on something similar :-).

5 comments

  • 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.

  • J.D.

    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.

  • J.D.

    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. 🙂

Leave a Reply to J.D. Cancel reply

Your email address will not be published. Required fields are marked *