MYSQL search through cells with comma separated values

Question

I am building an AJAX call which passes an array of variables (ids) through to a function which will then query a database to return rows if this ids exist in their 'catid' cell.

I know how to use an array to use an 'IN' MYSQL command but in this instance there are multiple ids stored in a cell for rows, seperated by a comma. This is stored as a varchar. Is there any way I can loop through the individual ids stored in a rows categories cell?

Here is a screenshot of an example row from the database. I have highlighted the 'catid' cell for rows. As you can see, a row can be part of many categories, with these category id's seperated by a comma)...

enter image description here

So that field is storing a literal string? As in 18,26,32 is a string in your database that you're trying to query. — Machavity♦
That's correct. I wouldn't have designed the database that way but it's not my database! — Jim

M Khalid Junaid M Khalid Junaid · Accepted Answer · 2014-05-13T13:48:37

This is a sign of bad DB design you should first look into Database Normalization and if you can change the structure then first normalize it by using a junction table ,As of now you can use FIND_IN_SET() to find your corresponding record id in your comma separated ids column

SELECT * FROM table 
WHERE  FIND_IN_SET('id1',column) > 0
AND FIND_IN_SET('id2',column) > 0
AND FIND_IN_SET('id3',column) > 0

Change the operator as per your wish i have shown example with AND operator,note you need to use FIND_IN_SET as many times as the no of ids you have in your array that you need to compare with your column

MYSQL search through cells with comma separated values

1 Answers