WordPress.org

1. spstieng
   Member
   Posted 4 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 4 years ago #
   

   Any suggestions anyone?
   Any help is appreciated :)

3. spstieng
   Member
   Posted 4 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 4 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 4 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 4 years ago #
   

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

7. hungzai
   Member
   Posted 4 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 4 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 4 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 4 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 4 years ago #
    

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

12. Dgold
    Member
    Posted 4 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 4 years ago #
    

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

14. csseur3
    Member
    Posted 4 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.