匹配MySQL查询中的数组值

Question

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).

Answer 1

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;