PHP MySQL custom blog, listing categories

Question

I am designing a custom blog in PHP. On my panel I have the heading "Categories" and I list the categories and how many posts are in each category. There are 2 related tables, posts, and post_categories.

POSTS - post_id, post_title, post, category_id

POST_CATEGORIES - category_id, category_name, post_id

I'd prefer not to add a category of uncategorised as this will cause me issues with future features I plan to design. My SQL for this is:

SELECT *, COUNT(*) AS category_post_count
FROM post_categories, posts
WHERE posts.category_id = post_categories.category_id
GROUP BY category_name

Now if a post is not set with a category_id, it is given a category_id of 0 and will not related to anything in post_categories. How can I group these in my SQL statement AS uncastegorised?

Thanks.

What is the need of "post_id" in the table POST_CATEGORIES ? does this mean that for EACH POST you will have NEW category_id ? It doesn't make sense. CATEGORY TO POSTS should be 1:n . You have 1:1 which is probably not the best way for a blogging application. — Stewie
– Stewie, Commented Nov 8, 2010 at 15:24
Not sure how to make your query work exactly for you but I can offer some other info. I know you dont want to create a row for the uncategorized but why? Is it bcause you dont want that to appear on other areas of your site? If thats the case then alter your SQl to not return results that have category_id of 0. If its an absolute no to have a row with uncategorized then you could do two queries one that returns your posts and one that returns your categories table then use PHP to do the math and if one of the posts has a category id of 0 then give it the category of uncategorized. — Scott
– Scott, Commented Nov 8, 2010 at 15:28

Frode · Accepted Answer · 2010-11-08 15:44:53Z

3

You can do a left join to also include any posts that don't have a category.

SELECT *, COUNT(*) as category_post_count
FROM post_categories LEFT JOIN posts
  ON posts.category_id = post_categories.category_id
GROUP BY category_name;

answered Nov 8, 2010 at 15:44

Frode

5,7102 gold badges27 silver badges25 bronze badges

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

4 Comments

DRKM Over a year ago

Great, thank you. If they don't have a category_name, how would I call them from the results?

Frode Over a year ago

The fields category_id, category_name and post_id from post_categories will all be NULL for posts without a category, so in PHP you can simply check if $row['category_name'] === NULL.

DRKM Over a year ago

Thanks for your help with this so far. I've just given the SQL a test and it doesn't seem to return anything as null, even when executed in PhpMyAdmin?

DRKM Over a year ago

Seems to work with RIGHT JOIN, not LEFT JOIN. Thanks for your help.

Collectives™ on Stack Overflow

PHP MySQL custom blog, listing categories

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related