Match array values in MySQL query
mysql, php
Solution
The problem doesn't lie in the query, but in the DB structure. You shouldn't put multiple IDs in a single column (at least not if you want to query on it). You can get it to work, but it will always be very slow.
Instead of the column `fruit_type`, you should have a table `fruit_type`.
CREATE TABLE fruit_fruittype (fruit_id INT, fruit_type_id INT, PRIMARY KEY (`fruit_id`,`fruit_type_id`));
Add a row for each fruit type per fruit.
Now you easily query the fruits for types:
SELECT fruit.* FROM fruit INNER JOIN fruit_fruittype ON fruit.id = fruit_fruittype.fruit_id WHERE fruit_type_id IN (1, 5, 8) GROUP BY fruit.id;
Problem
I will illustrate my question with a fruit example: I have an array with some values of fruit_type id's. ``` $match= array("1","5","8"). ``` I have a cell (fruit_type) in the table 'fruit'. The value of this cell is like this: "1,3,9". It contains all the fruit_type id's that belong to this row. Now I want to make my SELECT query to return all the rows that have any, a combination of all of the id's 1,5 or 8. This query won't work, because this will only work if the cell value is '1,5,8' and not 1 or 5 or 8 (or a combination or all of them): ``` SELECT * FROM fruit WHERE fruit_type IN ('".implode("','",$match)."') ``` Any ideas? EDIT (I think the question wasn't clear enough.. So what I would like in this example is: A query that will match ANY of the (cell) value's 1 or 3 or 9 with ANY of the value's from $match (1 or 5 or 8).