Multiple categories with one posts in sql query in php/mysql

Question

I Made two tables

In posts table I created category column, here I store multiple category_id like this 1,2,3.

The problem is if category column has single category_id like 1 or 2 or 3, I can easily query the data like this:

SELECT title FROM posts WHERE category = $category_id

However, I am unsure as to how to query the comma-delimited data. Is there a way to do this, or should I change the table structure ?

Add another table. Call it posts_categories with post_id and category_id. — aynber
– aynber, Commented Nov 10, 2016 at 13:38
Never, ever store data as comma separated items. It will only cause you lots of trouble. — jarlh
– jarlh, Commented Nov 10, 2016 at 13:39
Add a junction table which has 2 foreign keys refering to both Posts table and Categories table. like @aynber said. the 3th table will only refere foreign keys to the primary tables. — Asim
– Asim, Commented Nov 10, 2016 at 13:40

Asim · Accepted Answer · 2016-11-10 13:50:29Z

1

Here you can have: Categoryid: 1 Postid: 1,2,3

Result = Categoryid: 1 --> Postid 1,2,3

answered Nov 10, 2016 at 13:50

Asim

9762 gold badges10 silver badges31 bronze badges

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

Comments

roberto06 · Accepted Answer · 2016-11-10 13:42:59Z

0

You could either create a junction table (probably with an EAV design) or keep your unique table and use FIND_IN_SET, as such :

SELECT title FROM posts WHERE FIND_IN_SET(1, category) > 0

answered Nov 10, 2016 at 13:42

roberto06

3,8641 gold badge20 silver badges30 bronze badges