SQL search multiple values in same field

Question

I'm building a simple search algorithm and I want to break my string with spaces, and search my database on it, like so:

$search = "Sony TV with FullHD support";   $search = explode( ' ', $search );  SELECT name FROM Products WHERE name LIKE %$search[1]% AND name LIKE %$search[2]% LIMIT 6 

Is this possible?

Answer 1

Yes, you can use SQL IN operator to search multiple absolute values:

SELECT name FROM products WHERE name IN ( 'Value1', 'Value2', ... ); 

If you want to use LIKE you will need to use OR instead:

SELECT name FROM products WHERE name LIKE '%Value1' OR name LIKE '%Value2'; 

Using AND (as you tried) requires ALL conditions to be true, using OR requires at least one to be true.

Answer 2

Try this

Using UNION

$sql = ''; $count = 0; foreach($search as $text) {   if($count > 0)      $sql = $sql."UNION Select name From myTable WHERE Name LIKE '%$text%'";   else      $sql = $sql."Select name From myTable WHERE Name LIKE '%$text%'";    $count++; } 

Using WHERE IN

$comma_separated = "('" . implode("','", $search) . "')";  // ('1','2','3') $sql = "Select name From myTable WHERE name IN ".$comma_separated ;