WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Counting posts within categories (14 posts)

  1. spstieng
    Member
    Posted 6 years ago #

    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;
      endforeach;
    
      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
    ORDER BY RAND()
    LIMIT $num

    Suggestions anyone? :)

  2. spstieng
    Member
    Posted 6 years ago #

    Any suggestions anyone?
    Any help is appreciated :)

  3. spstieng
    Member
    Posted 6 years ago #

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

    $querystr = "
    		SELECT COUNT(*)
    		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....

  4. spstieng
    Member
    Posted 6 years ago #

    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'
    		";
  5. spstieng
    Member
    Posted 6 years ago #

    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;
       endforeach; 
    
       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.

  6. Mindshare Studios, Inc.
    Member
    Posted 6 years ago #

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

  7. hungzai
    Member
    Posted 5 years ago #

    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!

  8. isaacwcom
    Member
    Posted 5 years ago #

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

  9. seby
    Member
    Posted 5 years ago #

    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 ?

  10. Dgold
    Member
    Posted 5 years ago #

    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?

  11. seby
    Member
    Posted 5 years ago #

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

  12. Dgold
    Member
    Posted 5 years ago #

    I found a plugin that does it!

    http://www.web-templates.nu/2008/08/25/wp-extra-template-tags/

    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'); ?>
  13. seby
    Member
    Posted 5 years ago #

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

  14. csseur3
    Member
    Posted 5 years ago #

    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?

    bye

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags