I have a table of categories and a table of articles (actually, they're not populated yet) and I'm trying to figure out the most efficient way to keep track of the number of articles in each category. All articles have a category ID that is a foreign key to the categories table. I want to have a page that displays a list of categories and then shows the number of articles in each category in parentheses. So is there SQL to get the count of articles in each category and join that with the category names or what? Do I update a field in the categories table whenever a new article is added to it? Any examples are great. Thx.
Jake
I dont think its easy to do all this stuff in 1 statement. Its probably better to do this in pieces 😉. I would loop through the category table and for each records do a count on the article table using the categoryID... This should work fine...
.
select category.name, count(article.id) from category left join article on category.id = article.category group by category
You may have to convert "NULL"s in the count column to zeros. This CAN be done in SQL, but I do it in PHP.
Thanks! I'll give it a try.
