Support » Fixing WordPress » Counting posts within categories

  • Resolved Steven


    I’m continuing this post from a previous post so that I can focus on the main problem.

    This code ouputs the number of posts in one or more categories:

    function get_post_count($categories) {
    	global $wpdb;
    	$post_count = 0;
      foreach($categories as $cat) :
    		$querystr = "
    			SELECT count
    			FROM $wpdb->term_taxonomy
    			WHERE term_id = $cat";
      	$result = $wpdb->get_var($querystr);
      	$post_count += $result;
      return $post_count;

    Now I have to figure out how to subtract the number of posts which is not published.

    I think I will needing to use a LEFT JOIN (or RIGHT JOIN).
    But I’m not good using JOIN.

    I probably need to do something similar to this:

    SELECT * FROM $wpdb->posts
    LEFT JOIN $wpdb->term_relationships ON
    ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON
    ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    WHERE $wpdb->posts.post_status = 'publish'
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->term_taxonomy.term_id = 4
    LIMIT $num

    Suggestions anyone? 🙂

Viewing 13 replies - 1 through 13 (of 13 total)
  • Any suggestions anyone?
    Any help is appreciated 🙂

    I think I’m on a wild track with a LEFT JOIN right?
    I should maybe use sub selection. Something like

    $querystr = "
    		FROM $wpdb->posts, $wpdb->term_taxonomy
    		WHERE posts.post_type = 'post'
    		AND posts.post_status = 'publish'
    		AND ID = term_id
    		AND term_id = 21;
    		$result = $wpdb->get_var($querystr);

    After reading about the WordPress Taxanomy, I’m a bit uncertain if I should use wp_term_relationships or wp_term_taxonomy.

    All I need is to count the number of published posts in a category. Shouldn’t bee that hard….

    Nope, this doesn’t work either….

    $table_prefix = $wpdb->prefix;
    $querystr = "
    			SELECT (*)
    			FROM {$table_prefix}posts, {$table_prefix}term_relationships, {$table_prefix}term_taxonomy
    			WHERE {$table_prefix}posts.ID = {$table_prefix}term_relationships.object_id
    			AND {$table_prefix}term_relationships.term_taxonomy_id = {$table_prefix}term_taxonomy.term_taxonomy_id
    			AND {$table_prefix}term_taxonomy.taxonomy = 'category'
    			AND {$table_prefix}term_taxonomy.term_id = 21
    			AND post_status = 'publish'
    			AND post_type != 'page'

    Finally, Thanks to all the help here I managed to solve it.
    This code gets the number of published posts in one or more categories:

    function get_post_count($categories) {
    	global $wpdb;
    	$post_count = 0;
    		foreach($categories as $cat) :
    			$querystr = "
    				SELECT count
    				FROM $wpdb->term_taxonomy, $wpdb->posts, $wpdb->term_relationships
    				WHERE $wpdb->posts.ID = $wpdb->term_relationships.object_id
    				AND $wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id
    				AND $wpdb->term_taxonomy.term_id = $cat
    				AND $wpdb->posts.post_status = 'publish'
    			$result = $wpdb->get_var($querystr);
      		$post_count += $result;
       return $post_count;

    I also tested trying to count unpublished posts. That however didn’t work. According to Writing Posts, a post has three states; Published, Pending Review, and Unpublished. But looking at other code I find the following post_status: draft, future, publish and private.
    Changing the $wpdb->posts.post_status = ‘publish’ to any of the other three, only returns 0 (zero) even though I have created 2 posts and not published them. So I have no clue on how to count those.

    And if you try to use the wp_count_posts(), you will not get the number of posts for a specific category. I’m not quite sure what it actually reads.

    Hey! Thanks for the code… this is just what I was looking for.

    Dear spstieng,

    Your description of the code is exactly what I’m looking for. But I have 1 question though, how do we use the code? If I want to count posts that belong to cat A and cat B, where do I input the catID in the above code? Thanks for your help!

    @spstieng This was exactly what I was looking for, thanks for documenting. Didn’t realize how complicated this had to be!

    I’m agree with you hungzai. Where do I input the catID ? I tried like this but there something wrong !
    <?php $cat1= get_post_count(3); echo $cat1; ?>

    Any Idea ?

    Is this the best current way to get JUST the number (yes only the number) of posts in Category 3?

    Is there a plugin or can that code be made into one (so we’d have a simple template tag to display the post count for a certain category?

    hi Dgold, can you explain me how you run this code ? or someone else ?

    I found a plugin that does it!

    This plugin gives you a simple new template tag to put in your post or in your theme.

    Displays or returns the number of posts within a category. It can be used with a post ID, post slug / nicename or empty used within The Loop.

    <?php _category_count(); ?>
    <?php _category_count(3); ?>
    <?php _category_count('about'); ?>
    <?php echo _get_category_count(); ?>
    <?php echo _get_category_count(3); ?>
    <?php echo _get_category_count('about'); ?>

    Thanx for your promt answer Dgold. This plugin is perfect for me.



    with <?php echo _get_category_count(3); ?>

    i would like to count the number of posts of this category AND the sub-category. how to do that?


Viewing 13 replies - 1 through 13 (of 13 total)
  • The topic ‘Counting posts within categories’ is closed to new replies.