PHP SQL SELECT where like search item with multiple words

Question

I have a select where like query for a seach form which is as follows:

<?php 
$bucketsearch = sanitizeone($_POST["bucketsearch"], "plain");
$bucketsearch = strip_word_html($bucketsearch);
?>

 if(isset($_POST['search'])){
                  $result=MYSQL_QUERY( "SELECT * FROM buckets where bucketname like '%$bucketsearch%' order by bucketname");
              }else{
              $result=MYSQL_QUERY( "SELECT * FROM buckets order by bucketname");
          }

My problem is that if someone searches for instance for "apple and pear" i do not get any results that contain any of the words, i can only make it return results (well 1 result) with all the words in it.

Can anyone help me make this search a bit more versitle?? Thanks in advance.

Answer 1

So you want an AND search using each of the words entered, rather than the exact string? Howabout something like this:

$searchTerms = explode(' ', $bucketsearch);
$searchTermBits = array();
foreach ($searchTerms as $term) {
    $term = trim($term);
    if (!empty($term)) {
        $searchTermBits[] = "bucketname LIKE '%$term%'";
    }
}

...

$result = mysql_query("SELECT * FROM buckets WHERE ".implode(' AND ', $searchTermBits).");

this will give you a query like:

SELECT * FROM buckets WHERE bucketname LIKE '%apple%' AND bucketname LIKE '%and%' AND bucketname LIKE '%pear%'

change the AND to an OR if you want to match any of the search terms rather than all. Further improvements could involve defining some stop words like 'and' to give better results.

Answer 2

I think that the best solution would be to use Regular Expressions. It's cleanest and probably the most effective. Regular Expressions are supported in all commonly used DB engines.

In MySQL there is RLIKE operator so your query would be something like:

SELECT * FROM buckets WHERE bucketname RLIKE "(?=.*apple)(?=.*and)(?=.*pear)"

Did't tested it, hope that my expression is right for MySQL regexp "dialect".

More on MySql regexp support:
http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp