Welcome Guest, Not a member yet? Register   Sign In
SQL Count Query

I am trying to retrieve all categories, and the number of posts in each category.
SELECT COUNT(blog_entries.post_id) AS post_count,
blog_categories.category_id, blog_categories.category_title, blog_categories.category_desc
FROM blog_categories, blog_posts
WHERE blog_posts.category_id = blog_categories.category_id
GROUP BY blog_categories.category_title
This works, but it only fetches the rows of categories which have posts under them. How do I return the categories which are empty as well?


What you're doing now is really a JOIN, but if you change it to a LEFT JOIN you'll get the ones with no posts also.

$this->db->select('COUNT(p.post_id) post_count, c.category_id, c.category_title, c.category_desc');
$this->db->join('blog_posts p', 'p.category_id = c.category_id', 'left');
$query = $this->db->get('blog_categories c');

Should work, you might get NULL for post_count on the ones with no posts, so just display 0 in your php in that case.

Theme © iAndrew 2016 - Forum software by © MyBB