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.

Tim Fountain · Accepted Answer · 2010-09-01 19:17:02Z

33

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.

answered Sep 1, 2010 at 19:17

Tim Fountain

33.2k5 gold badges44 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Over a year ago

Tim, thank you! It took me about 30 minutes of playing it, i copied my page to test it, couldn't work out why it wasn't working, lots of checking of everything, then realised dreamweaver had added "2" to the end of my input name!!! grrr. I changed it to OR and it works a dream! Thank you. Now just off to find a function to remove stop words. Thank you for your help!! :)

Michael J. Calkins Over a year ago

For any laravel ppl reading this check this out: gist.github.com/clouddueling/4967617

dev101 Over a year ago

I would just add that when using OR mode it is imperative to enclose entire query condition inside parenthesis () because unexpected results may happen (although you wouldn't know that because there will be no MySQL error per se). I got results from unrelated categories, even when specific category was specified in separate AND condition. For example, replace above in Tim's answer: implode(' OR ', $searchTermBits) part with '(' . implode(' OR ', $searchTermBits) . ')'

Ondrej Bozek · Accepted Answer · 2015-12-10 13:56:56Z

7

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

edited Dec 10, 2015 at 13:56

answered Dec 7, 2012 at 12:38

Ondrej Bozek

11.6k7 gold badges58 silver badges71 bronze badges

Comments

MikeTheReader · Accepted Answer · 2010-09-01 19:14:37Z

1

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

answered Sep 1, 2010 at 19:14

MikeTheReader

4,1903 gold badges27 silver badges38 bronze badges

Comments

CogitoErgoSum · Accepted Answer · 2010-09-01 19:18:25Z

1

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.

answered Sep 1, 2010 at 19:18

CogitoErgoSum

2,9275 gold badges34 silver badges46 bronze badges

Comments

Sukanya Sen · Accepted Answer · 2017-04-29 00:04:51Z

1

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

answered Apr 29, 2017 at 0:04

Sukanya Sen

112 bronze badges

Comments

gen_Eric · Accepted Answer · 2010-09-01 19:19:55Z

0

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.

edited Sep 1, 2010 at 19:19

answered Sep 1, 2010 at 19:14

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

Collectives™ on Stack Overflow

PHP SQL SELECT where like search item with multiple words

6 Answers 6

3 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related