mysql split search string by space and push into query

Question

The search query is:

"product1 prod2 prod4"

I need to make the mysql

SELECT * 
  FROM tableprod 
  WHERE (prod LIKE '%product1%' 
         AND prod LIKE '%prod2%' 
         AND prod LIKE '%prod4%')

using the mysql_real_escape_string for the input query...

Answer 1

Simple string manipulation:

$terms = explode(' ', $search);

$bits = array();
foreach ($terms as $term) {
    $bits[] = "prod LIKE '%".mysql_real_escape_string($term)."%'";
}

$query = "SELECT * FROM tableprod WHERE (".implode(' AND ', $bits).")";

Answer 2

If you can meet the constraints, you migh be better off just using a FULLTEXT index which would save you the trouble of having to split the string, plus you'd get the bonus of being able to use basic boolean operators for the search (and/or/not)

Answer 3

without a loop(i've always tried to avoid loops):

$str = "product1 prod2 prod4";
$sql = "select * from tableprod where (prod like '%" . str_replace( ' ', '%\' AND prod LIKE \'%', $str ) . '%\')';

if it's possible you will have more than one space inbetween items use preg_replace