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?
}