4

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

Improve this question
2
  • What kind of datatype of fruit_type, INT, CHAR, VARCHAR? Commented Jan 27, 2013 at 22:55
  • fruit_type is a varchar (because of the comma) Commented Jan 28, 2013 at 6:40

2 Answers 2

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you for your answer! But the query <code>SELECT * FROM fruit WHERE fruit_type IN ('1,5,8')</code> doesn't work. If i try <code>SELECT * FROM fruit WHERE fruit_type IN (1,5,8)</code> it works! But only on an excact match, so if the value of fruit_type is 3,5,7 it is no match...
If fruit_type is an int, there is no need for encasing in quotes or backticks.
fruit_type is a varchar. I don't have the test server available anymore right now. I will test again tomorrow morning. Thanks for the help!
Unfortunately it's not working like the way I intent to use it. The cell value in the MySQL table is in the example "1,3,9". So what I want is to match ANY of the value's 1 or 3 or 9 with ANY of the value's from $match (1 or 5 or 8).
Tanks, Jake. I already changed the DB structure and now I'm using an inner join. It works great!
2

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;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.