Counting posts within categories

  • Resolved Steven

    (@spstieng)


    16 years, 8 months 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? ??

Viewing 13 replies - 1 through 13 (of 13 total)
  • Thread Starter Steven

    (@spstieng)

    Any suggestions anyone?
    Any help is appreciated ??

    Thread Starter Steven

    (@spstieng)

    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….

    Thread Starter Steven

    (@spstieng)

    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'
		";

    Thread Starter Steven

    (@spstieng)

    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.

    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!

    https://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'); ?>

    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?

    bye

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