What's the best way to search a MySQL database with PHP?

Question

Say if I had a table of books in a MySQL database and I wanted to search the 'title' field for keywords (input by the user in a search field); what's the best way of doing this in PHP? Is the MySQL LIKE command the most efficient way to search?

Answer 1

Yes, the most efficient way usually is searching in the database. To do that you have three alternatives:

• LIKE, ILIKE to match exact substrings
• RLIKE to match POSIX regexes
• FULLTEXT indexes to match another three different kinds of search aimed at natural language processing

So it depends on what will you be actually searching for to decide what would the best be. For book titles I'd offer a LIKE search for exact substring match, useful when people know the book they're looking for and also a FULLTEXT search to help find titles similar to a word or phrase. I'd give them different names on the interface of course, probably something like exact for the substring search and similar for the fulltext search.

An example about fulltext: http://www.onlamp.com/pub/a/onlamp/2003/06/26/fulltext.html

Answer 2

Here's a simple way you can break apart some keywords to build some clauses for filtering a column on those keywords, either ANDed or ORed together.

$terms=explode(',', $_GET['keywords']);
$clauses=array();
foreach($terms as $term)
{
    //remove any chars you don't want to be searching - adjust to suit
    //your requirements
    $clean=trim(preg_replace('/[^a-z0-9]/i', '', $term));   
    if (!empty($clean))
    {
         //note use of mysql_escape_string - while not strictly required
         //in this example due to the preg_replace earlier, it's good
         //practice to sanitize your DB inputs in case you modify that
         //filter...
         $clauses[]="title like '%".mysql_escape_string($clean)."%'";
    }
}

if (!empty($clauses))
{   
    //concatenate the clauses together with AND or OR, depending on
    //your requirements
    $filter='('.implode(' AND ', $clauses).')';

    //build and execute the required SQL
    $sql="select * from foo where $filter";
}
else
{
    //no search term, do something else, find everything?
}

Answer 3

You may also check soundex functions (soundex, sounds like) in mysql manual http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_soundex Its functional to return these matches if for example strict checking (by LIKE or =) did not return any results.

Answer 4

Paul Dixon's code example gets the main idea across well for the LIKE-based approach.

I'll just add this usability idea: Provide an (AND | OR) radio button set in the interface, default to AND, then if a user's query results in zero (0) matches and contain at least two words, respond with an option to the effect:

"Sorry, No matches were found for your search phrase. Expand search to match on ANY word in your phrase?

Maybe there's a better way to word this, but the basic idea is to guide the person toward another query (that may be successful) without the user having to think in terms of the Boolean logic of AND and ORs.

Answer 5

Consider using sphinx. It's an open source full text engine that can consume your mysql database directly. It's far more scalable and flexible than hand coding LIKE statements (and far less susceptible to SQL injection)