CODEWITHSUNDEEP
phpmysqlsearch
Philip Morton
Philip Morton

Reputation: 131959

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

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?

Upvotes: 14

Views: 17827

Answers (6)

9jamkt
9jamkt

Reputation: 37

I think Like is the most efficient way if it's a word. Multi words may be split with explode function as said already. It may then be looped and used to search individually through the database. If same result is returned twice, it may be checked by reading the values into an array. If it already exists in the array, ignore it. Then with count function, you'll know where to stop while printing with a loop. Sorting may be done with similar_text function. The percentage is used to sort the array. That's the best.

Upvotes: 1

Dave Cheney
Dave Cheney

Reputation: 5765

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)

Upvotes: 2

micahwittman
micahwittman

Reputation: 12476

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.

Upvotes: 1

Greg
Greg

Reputation:

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.

Upvotes: 1

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340306

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

Upvotes: 22

Paul Dixon
Paul Dixon

Reputation: 300975

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

Upvotes: 10

Related Questions