从多类别获取帖子（没有wordpress，纯php和mysql）

Question

There are 2 MySQL tables: 'posts' and 'category'. Unfortunately there is no taxonomy table.

Categories:

id: integer
name: string

Posts:

id: integer
name: string
body: text
tag: string
category: string

So, in category there is:

 id    name
 1     Books
 2     Animals

And Post:

 id    name     body      tag      category
 1     My Post  Hi post   my-post  1
 2     Post 2   2nd Post  post-2   1;2

This is te SQL query:

SELECT * FROM posts WHERE category = '1'

Only returns post id 1

SELECT * FROM posts WHERE category = '2'

Returns nothing

How can I get both posts with just one SQL query?

Answer 1

Personally, I would steer well clear of that structure and create a new table PostCategoryto house your associated categories for each post so:

postID | categoryID
1      | 1
2      | 1
2      | 2

Then use a distinct select and an inner join in your sql:

select distinct post.* from post 
inner join postcategory on post.id = postcategory.postID 
where postcategory.categoryID = 2;

As @McAdam331 quite rightly said, using a string to store lookup values you're going to be querying is B.A.D for performance and in general for db design