PHP选择类别和计数

Question

Table categories:

id, name,    
-------------
1   category1
2   category2
3   category3
4   category4

Table posts:

id, category, title
--------------------
1   1, 3      title1
2   4, 2      title2
3   1, 4      title3

I would like to have a show categories and counter posts in this category. In the category column in the table posts I IDs of categories, which are separated by ', '. How to do that is searched when category = category ID and show counter with a minimum of SQL queries.

Answer 1

You should fix your data structure to have one row per post and category. A comma-separated list is not a SQLish way to store data for many reasons. You should change this to a junction table.

Here are some reasons why:

• Numbers should be stored as numbers and not strings.
• Foreign key relationships should be properly defined, and you can't do that with a string to a number.
• An attribute should contain one value.
• MySQL doesn't have very good support for string processing functions.
• Queries using the column cannot be optimized using indexes.

You should have a table called PostCategories for this information.

Sometimes, we are stuck with other peoples bad design decisions. If so, you can use a query such as:

select c.id, count(*)
from posts p join
     categories c
     on find_in_set(c.id, replace(p.category)) > 0
group by c.id;