PHP SQL SELECT where like search item with multiple words

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.

Answers


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.


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


For your simple case here, you could replace your "and" with a "%" (but I'm guessing you're looking for a more comprehensive answer. (This would also be order specific, as apple would have to come before pear.)


Er. Not the best solution I'd think but you can break up the words into an array and loop them out into multiple LIKES. Do some replaces to yank out ANDs, ORs etc and then run an explode.

Then just loop.

$sql = SELECT * from Buckets where";

Loop the array, $sql .= " bucketname LIKE '%" . $arrayEl[i] . "% OR'. Just make sure on the last iteration to not include the last OR or append a last line of 0=1 etc.

Not elegant, not efficient but in this case it'll work. You'd honestly be better off running a full text search if its a text field.


I wanted something with basic SQL for my multi word search in MySQL database. So I come up with the following.

$terms=explode(" ",$search);
$count=count($terms);
$sql="SELECT * FROM product WHERE";                     
for($i=0;$i<$count;$i++)
{
    if($i!=$count-1)
        $sql = $sql.
        " (pname LIKE '%$terms[$i]%' OR category LIKE '%$terms[$i]%' OR    
          sub_category LIKE '%$terms[$i]%' OR 
          description LIKE '%$terms[$i]%') AND ";
    else
        $sql = $sql.
        "(pname LIKE '%$terms[$i]%' OR category LIKE '%$terms[$i]%' OR 
         sub_category LIKE '%$terms[$i]%' OR 
         description LIKE '%$terms[$i]%')";
}    

You can split "apple and pear" into 2 strings; "apple" and "pear". Maybe, then you can do WHERE bucketname LIKE '%apple%' OR bucketname LIKE '%pear%'. I don't know if this helps.


Need Your Help

Does HTML5/Canvas Support Double Buffering?

javascript html5 canvas double-buffering

What I'd like to do is draw my graphics on a buffer and then be able to copy it as is to the canvas so I can do animation and avoid flickering. But I couldn't find this option. Anyone know how I ca...

Why does python `any` return a bool instead of the value?

python function internal

and and or return the last element they evaluated, but why doesn't Python's built-in function any?