Help counting published posts.

[eluser]stondin[/eluser]
Hey there,

Im struggling with the following issue guys.
I would like to count the number of "published" posts in each of my blog categories.

Im not very SQL savvy so I'm hoping to find a solution here

$this->db->select("categories.*, (SELECT count(postcat_id) FROM postcats WHERE postcats.category_id = categories.category_id) AS cat_num_posts");

I know that this query returns a count of all the posts in each category but I need to only return the count for active categories which would be status = 1.

Any help would be appreciated.

Dan

[eluser]jcopling[/eluser]
Yep, you're almost there just add an additional WHERE clause to your sub-query like this:

Code:

$this->db->select("categories.*, (SELECT count(postcat_id) FROM postcats WHERE postcats.category_id = categories.category_id AND postcats.status=1) AS cat_num_posts");

I may also suggest adding a conditional to your COUNT function as your query would not return a category that 0 active posts in it.

If you are using a MSSQL DB you can do this with the ISNULL function like this:

Code:

$this->db->select("categories.*, (SELECT ISNULL(count(postcat_id),0) FROM...

or if you use MySQL you can use the IFNULL function like this:

Code:

$this->db->select("categories.*, (SELECT IFNULL(count(postcat_id),0) FROM...

That way you will return all of your categories no matter what.
Good luck!

[eluser]stondin[/eluser]
Hey jcopling,

This would definitely work if the status was in the categories table. unfortunately the status of a post is in the post table. I get this database error with your suggested code.

Error Number: 1054

Unknown column 'postcats.status' in 'where clause'

SELECT categories.*, (SELECT count(postcat_id) FROM postcats WHERE postcats.category_id = categories.category_id AND postcats.status=1) AS cat_num_posts FROM (`categories`) ORDER BY `category`

Any thoughts?

Dan

[eluser]jcopling[/eluser]
Am I understanding that the `postcats` table is linking between the `categories` table and the `post` table?

If this is correct, then you can add a JOIN to your subquery like this:

Code:

SELECT categories.*, (SELECT count(postcat_id) FROM postcats LEFT JOIN post on post.post_id = postcats.post_id WHERE postcats.category_id = categories.category_id AND post.status=1) AS cat_num_posts FROM (`categories`) ORDER BY `category`

Again, that is assuming I'm understanding you correctly.

[eluser]stondin[/eluser]
Thank you very much. That query worked great. Many thanks.
I'll do a little reading on JOIN so I understand what exactly is happening.

Many thanks again, this is a great community.
Dan

[eluser]jcopling[/eluser]
Glad that it worked!

Truthfully, an INNER JOIN would probably work here as well, as you will probably not have any records in your postcats table that are not in your post table, but it's a safe practice to ensure that you are receiving all of the categories regardless of the presence of a post.

[eluser]MMCCQQ[/eluser]
hi!

i got two table , users and events,, i need to know count of events that users clicked..

soo i user this code

Code:

$this->db->select("users.*, (SELECT count(id) FROM events WHERE events.iduser = users.iduser) AS cat_num_posts");

    $query = $this->db->get();

i got this error
Unknown table 'users'

SELECT users.*, (SELECT count(id) FROM events WHERE events.iduser = users.iduser) AS cat_num_posts

users table exists

[eluser]jcopling[/eluser]
You must add a FROM clause in your outer query, for example:

Code:

$this->db->select("users.*, (SELECT count(id) FROM events WHERE events.iduser = users.iduser) AS cat_num_posts");

    $query = $this->db->get('users');

which will produce the SQL statement:

Code:

SELECT users.*, (SELECT count(id) FROM events WHERE events.iduser = users.iduser) AS cat_num_posts FROM users

Hope that helps.