filips filips - 9 months ago 49
PHP Question

PHP select categories and count number

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.


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, count(*)
from posts p join
     categories c
     on find_in_set(, replace(p.category)) > 0
group by;