CODEWITHSUNDEEP
phpmysql
StefR
StefR

Reputation: 111

Match array values in MySQL query

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

Upvotes: 4

Views: 8856

Answers (2)

Arnold Daniels
Arnold Daniels

Reputation: 16553

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;

Upvotes: 2

Giacomo1968
Giacomo1968

Reputation: 26066

You are wrapping each of the individual numbers in your implode in quotes when you do this:

SELECT * FROM fruit WHERE fruit_type IN ('" . implode("','",$match) . "')

And the resulting query would look like this:

SELECT * FROM fruit WHERE fruit_type IN ('1','5','8')

So it should be:

SELECT * FROM fruit WHERE fruit_type IN (" . implode(",",$match) . ")

So the resulting query looks like this:

SELECT * FROM fruit WHERE fruit_type IN (1,5,8)

Also if you are doing this in PHP then I would recommend just echoing the output right before it get’s processed my the DB command to actually see what the final MySQL statement is. I’m pretty sure if you were to have done that you would have seen the issue right away.

Upvotes: 5

Related Questions