Jump to the main content.

Covering all things web, one article at the time.

Want to write for WebD&D? We are looking for writers. Interested? Drop us an e-mail at hi (at) webdosanddonts.com.

How to count posts in multiple categories for WordPress

Written by Siniša on November 29, 2009.

As we promised in WebD&D v2 introductory article, we’re doing a little series of articles about custom WordPress stuff we did to achieve certain features here on WebD&D. So here is the first one of the series…

One of the things that we needed throughout the site was a count of posts within various combinations of categories. Unfortunately, that kind of feature isn’t supported in WordPress out-of-the-box. We’ll get a little technical here so those of you who just want the final solution–feel free to fast forward to the end of the article and grab the code.

How we did it

WordPress is, as you probably know, a database driven platform, so first thing we need to do is to identify the right tables and understand their relationships in order to retrieve the correct data from them.

Basically our goal here is to identify terms that have taxonomy ‘category’ and filter the ones we’re interested in. For filtering only specific categories well need to join terms table. Then we have to find out which posts belong to those categories. That’s why we’ll join the wp_term_relationships table. And last but not least, we’ll throw posts table to the mix.

Let’s say we wanted to count all posts from “Advanced” and “Intermediate” categories. Assuming that table prefix is the default one, our SQL query should look something like:

SELECT	COUNT( DISTINCT cat_posts.ID ) AS post_count
FROM 	wp_term_taxonomy AS cat_term_taxonomy INNER JOIN wp_terms AS cat_terms ON
		cat_term_taxonomy.term_id = cat_terms.term_id
	INNER JOIN wp_term_relationships AS cat_term_relationships ON
		cat_term_taxonomy.term_taxonomy_id = cat_term_relationships.term_taxonomy_id
	INNER JOIN wp_posts AS cat_posts ON
		cat_term_relationships.object_id = cat_posts.ID
WHERE 	cat_posts.post_status = 'publish' AND
	cat_posts.post_type = 'post' AND
	cat_term_taxonomy.taxonomy = 'category' AND
	cat_terms.slug IN ('advanced', 'intermediate')

The name of the posts table is somewhat misleading because it contains not only posts but pages, post revisions, and whatnot… Hence the condition post_type = 'post'. Being picky like we are, we only want published posts so we expanded our condition with a little post_status = 'publish'.

Another thing I want to point out is the COUNT DISTINCT… A well know feature in WordPress is that a post can belong to any number of categories so without counting only distinct post IDs we could end up with a wrong (much bigger) count.

Now we’ll change that query a bit and wrap it in a PHP function. That will allow us to pass any number of category slugs.

function wdd_in_category_count($catslugs = '', $display = true) {
	global $wpdb;

	$post_count = 0;
	$slug_where = '';
	$catslugs_arr = split(',', $catslugs);

 	foreach ($catslugs_arr as $catslugkey => $catslug) {
		if ( $catslugkey > 0 ) {
			$slug_where .= ', ';
		 }

 		$slug_where .= "'" . trim($catslug) . "'";
	}

	$slug_where = "cat_terms.slug IN (" . $slug_where . ")";

	$sql =	"SELECT	COUNT( DISTINCT cat_posts.ID ) AS post_count " .
		"FROM 	" . $wpdb->term_taxonomy . " AS cat_term_taxonomy INNER JOIN " . $wpdb->terms . " AS cat_terms ON " .
				"cat_term_taxonomy.term_id = cat_terms.term_id " .
			"INNER JOIN " . $wpdb->term_relationships . " AS cat_term_relationships ON " .
				"cat_term_taxonomy.term_taxonomy_id = cat_term_relationships.term_taxonomy_id " .
			"INNER JOIN " . $wpdb->posts . " AS cat_posts ON " .
				"cat_term_relationships.object_id = cat_posts.ID " .
		"WHERE 	cat_posts.post_status = 'publish' AND " .
			"cat_posts.post_type = 'post' AND " .
			"cat_term_taxonomy.taxonomy = 'category' AND " .
			$slug_where;

	$post_count = $wpdb->get_var($sql);

	if ( $display ) {
		echo $post_count;
	} 

	return $post_count; 

}

Our little function here has a $catslugs input parameter that expects category slugs in a string separated with commas. We also used $wpdb variables instead of hard coding the table names, witch is always a good practice.

How can you use it

Download Multiple category post counter function for WordPress

First download the code, uncompress and paste it somewhere in your functions.php file. Then find the place in your template file where you want to display number of posts form certain categories and call the wdd_in_category_count() function.

For example, if you had a movie review blog and you wanted to display number of reviews for Horror and SF movies, put something like this:

<p>Total number of Horror and SF reviews: <?php wdd_in_category_count('sf, horror') ?>.</p>

And there you have it… That’s how you count sheep posts within multiple categories.

Please reply with your questions, comments and suggestions.

About the author

Siniša Kolundžić is a wannabe rock drummer and web enthusiast from Croatia. Making his daily bread as a business application developer he always finds the time to contribute to WebD&D.

19 Responses to “How to count posts in multiple categories for WordPress”

  1. Kouba says:

    Are you a professional journalist? You write very well.

  2. tzs says:

    Thx! I was search this!

    It works if you can count posts by categories for an author:

    function wdd_in_category_count($catslugs = ”, $display = true) {
    global $wpdb;

    $post_count = 0;
    $slug_where = ”;
    $catslugs_arr = split(‘,’, $catslugs);

    foreach ($catslugs_arr as $catslugkey => $catslug) {
    if ( $catslugkey > 0 ) {
    $slug_where .= ‘, ‘;
    }

    $slug_where .= “‘” . trim($catslug) . “‘”;
    }

    $slug_where = “cat_terms.slug IN (” . $slug_where . “)”;

    $sql = “SELECT COUNT( DISTINCT cat_posts.ID ) AS post_count ” .
    “FROM ” . $wpdb->term_taxonomy . ” AS cat_term_taxonomy INNER JOIN ” . $wpdb->terms . ” AS cat_terms ON ” .
    “cat_term_taxonomy.term_id = cat_terms.term_id ” .
    “INNER JOIN ” . $wpdb->term_relationships . ” AS cat_term_relationships ON ” .
    “cat_term_taxonomy.term_taxonomy_id = cat_term_relationships.term_taxonomy_id ” .
    “INNER JOIN ” . $wpdb->posts . ” AS cat_posts ON ” .
    “cat_term_relationships.object_id = cat_posts.ID ” .
    “WHERE cat_posts.post_status = ‘publish’ AND ” .
    “cat_posts.post_type = ‘post’ AND ” .
    “cat_posts.post_author = ‘”.get_the_author_id().”‘ AND ” .
    “cat_term_taxonomy.taxonomy = ‘category’ AND ” .
    $slug_where;

    $post_count = $wpdb->get_var($sql);

    if ( $display ) {
    echo $post_count;
    }

    return $post_count;

    }

  3. Very useful, thank you – I’ve been looking for a way to filter the count, something http://codex.wordpress.org/Function_Reference/wp_count_posts lacks!

  4. James says:

    Absolutely fabulous! Huge amounts of thanks due!

  5. disney mp3 players says:

    Nice post. Love your work hope to see some more cool stuff soon.

  6. courtenaya says:

    scale impact intensity gps

  7. Lucius says:

    Very useful, thank you very much!

  8. danielle says:

    thank you. thank you. thank you.

  9. Sinisa says:

    Hvala brate, veoma je korisno. Ovo sam ganjao po celom netu.

  10. Zdenek says:

    Very helpful. Thank you. Vielen Dank. Спасибо. ありがとうございます. Dekuji. Merci!

  11. Job says:

    If you want to do something similar but without poking around with SQL, you can use WordPress’ oqn query function to achieve the same result:


    // $ids is an array of all IDs you want to check overlap of
    function category_overlap_count($ids){
    $query = array();
    $query['category__and'] = $ids; // This filters our query to include only posts that are in ALL of the specified queries
    $query['posts_per_page'] = -1; // Making sure we select all posts (and not limit the query by our regular post per page setting)

    $results = get_posts( $query ); // Run the query

    $i = count($results); // Count the results

    return $i; // Return the count
    }

  12. Harsh maur says:

    hey really found useful i was actually struck with the custom taxonomy count as it always showed published as well as drafts. I hope this code will work

  13. Alexk says:

    hello
    thank you for your code,
    I need a code for count posts joint in two categories , your way count all posts but I need only joint posts.

    how can i do this ?

  14. Henry says:

    Do you have an example of this? I am not a coder but think I know what the code achieves. I’d like like to see it in action first and then implement.

    Cheers

  15. Mark says:

    Is there any way to expand this so that it works with custom post types as well?

    Thanks!

    • Mark says:

      Sorry, posted that before seeing this line: “cat_posts.post_type = ‘post’ AND ” .

      Is there a way to make it so that only posts that reside in both categories simultaneously are counted?

Leave a Reply

Spam is bad, mmkay?