CODEWITHSUNDEEP
phpsqlpdosql-like
NewDeveloper
NewDeveloper

Reputation: 105

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

Upvotes: 0

Views: 312

Answers (3)

Stéphane CLEMENT
Stéphane CLEMENT

Reputation: 372

You can also do something like :

// Populating join table.    
CREATE TABLE temp_table_myArray (myArray nvarchar(255));
    INSERT INTO temp_table_myArray (myArray) VALUES ('Cacaos');
    INSERT INTO temp_table_myArray (myArray) VALUES ('Bananas');

// Query using the join table.
    SELECT F.*
    FROM Farmers AS F
        INNER JOIN temp_table_myArray AS T ON (F.Available_products LIKE(CONCAT('%',T.myArray,'%')));

But I think there is a better PHP way to solve your problem...

Upvotes: 0

Barmar
Barmar

Reputation: 781848

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();

Upvotes: 1

Ankur Garg
Ankur Garg

Reputation: 605

You can try with the help of REGEXP .

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

Upvotes: 0

Related Questions