CODEWITHSUNDEEP
phpsqlmysqlselect
Dan
Dan

Reputation: 267

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.

Upvotes: 8

Views: 48432

Answers (6)

Sukanya Sen
Sukanya Sen

Reputation: 11

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]%')";
}

Upvotes: 1

Ondrej Bozek
Ondrej Bozek

Reputation: 11481

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

Upvotes: 7

gen_Eric
gen_Eric

Reputation: 227240

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.

Upvotes: 0

CogitoErgoSum
CogitoErgoSum

Reputation: 2907

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.

Upvotes: 1

Tim Fountain
Tim Fountain

Reputation: 33148

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.

Upvotes: 33

MikeTheReader
MikeTheReader

Reputation: 4190

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.)

Upvotes: 1

Related Questions