CODEWITHSUNDEEP
phpmysqlwhere-clause
Md. Khalakuzzaman Khan
Md. Khalakuzzaman Khan

Reputation: 194

write where clause query in MySQL from comma separated IDs

I have bunch blog posts in my posts table. Suppose, 

+----+----------------------+-------------------------+
| id | title                | categories              | 
+----+----------------------+-------------------------+
| 1  | title 1              | 234, 235, 243           | 
| 2  | title 2              | 237                     |
| 2  | title 3              | 234, 243                |
+----+----------------------+-------------------------+

Now, I am trying to select all of posts where categories 243. I already tried to use FIND_IN_SET function which can select posts if id is the first number in categories field.

My Current Query is like this- SELECT * FROM posts WHERE FIND_IN_SET(235, Category) <> 0 ORDER BY PostId DESC

Thanks in advance.

Upvotes: 2

Views: 264

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269653

You should know how bad an idea it is to store categories in such a list. Here are some reasons:

  • Storing numbers as strings is bad.
  • SQL has relatively poor string manipulation functions.
  • The resulting queries cannot use indexes.
  • Foreign key constraints cannot be declared.

So, you should fix the data to use a junction table.

Sometimes, we are struck with other peoples bad design decisions.

In this case, the problem would see to be the spaces in the list. Try this:

WHERE FIND_IN_SET(235, replace(Category, ' ', '') > 0

Or, alternatively:

WHERE CONCAT(', ', 235, ', ') LIKE CONCAT('%, ', Category, ', %')

However, I would encourage you to use relational data structures correctly and implement a junction table.

Upvotes: 3

Related Questions