CODEWITHSUNDEEP
phpmysql
James
James

Reputation: 2860

MYSQL search through cells with comma separated values

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

Upvotes: 1

Views: 1551

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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

Upvotes: 2

Related Questions