Search code examples
phpsqlpdosql-like

Select statement with where clause Like in array

I know how to perform an SQL LIKE % query for a single value like so:

$sql = "SELECT * FROM Farmers WHERE Available_products LIKE ('%Bananas%')";

but how do I do this if the search terms for my LIKE comes from an array? For example, let's say we have an array like this:

$_POST['Products']="Cacaos,Bananas";
$array=implode(',', $_POST['Products']);
$sql = "SELECT * FROM Farmers WHERE Available_products LIKE ('%$array%')";

I want to get all the records in database that the column Available_Products contains Cacaos or Bananas or Both

Solution

  • Convert the array to a regular expression and use REGEX instead of LIKE

    $_POST['Products'] = array('Cacaos', 'Bananas');
$regex = implode('|', $_POST['Products']);
$sql = "SELECT * FROM Farmers WHERE Available_products REGEX :regex";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':regex', $regex);
$stmt->execute();