5

I'm running a multisite with a main site and 3 subsites (the number of subsites will be over 200 at the end). Now on every site I have a category called A, Category called B, and Category called C. On my main site I want to publish a count of all posts made on all subsites, all posts made on all subsites in category A, all posts made on all subsites in category B, all posts made on all subsites in category C. So I get the output like this:

  • All posts: 100
  • Posts in category A: 20
  • Posts in category B: 30
  • Posts in category C: 50

Of course a post can be in categories A abd B at the same time, and can only be counted one time.

I found the way to count all posts:

global $wpdb;
$post_count = $wpdb->get_var( 
    "SELECT COUNT(*) FROM wp_2_posts WHERE post_status = 'publish' AND post_type = 'post' " );
$post_count1 = $wpdb->get_var( 
    "SELECT COUNT(*) FROM wp_3_posts WHERE post_status = 'publish' AND post_type = 'post' " );
$post_count2 = $wpdb->get_var( 
    "SELECT COUNT(*) FROM wp_4_posts WHERE post_status = 'publish' AND post_type = 'post' " );

$sum = $post_count + $post_count1 + $post_count2;

echo "<p>All posts {$sum}</p>";

I'm having trouble with the posts in certain categories. Here's what I have so far:

global $wpdb;
$wp_count_terms = $wpdb->get_var( 
    "SELECT COUNT(*) FROM wp_2_term_taxonomy WHERE term_taxonomy_id = '22' " );
$wp_count_terms1 = $wpdb->get_var( 
    "SELECT COUNT(*) FROM wp_3_term_taxonomy WHERE term_taxonomy_id = '16' " );
$wp_count_terms2 = $wpdb->get_var( 
    "SELECT COUNT(*) FROM wp_4_term_taxonomy WHERE term_taxonomy_id = '13' " );

$sum = $wp_count_terms + $wp_count_terms1 + $$wp_count_terms2;

echo "<p>Posts in category A: {$sum}</p>";

But it's not working correctly. Am I on the right track at all?

1

Found the answer, posting the code below so it may help future generations.

<?php

global $wpdb;

$count_terms = $wpdb->get_var ("SELECT COUNT(*)
FROM wp_2_term_taxonomy, wp_2_posts, wp_2_term_relationships 
WHERE wp_2_posts.ID = wp_2_term_relationships.object_id 
AND wp_2_term_relationships.term_taxonomy_id = wp_2_term_taxonomy.term_taxonomy_id 
AND wp_2_term_taxonomy.term_id = '22' 
AND wp_2_posts.post_type = 'post' 
AND wp_2_posts.post_status = 'publish' ");

$count_terms1 = $wpdb->get_var ("SELECT COUNT(*)
FROM wp_3_term_taxonomy, wp_3_posts, wp_3_term_relationships 
WHERE wp_3_posts.ID = wp_3_term_relationships.object_id 
AND wp_3_term_relationships.term_taxonomy_id = wp_3_term_taxonomy.term_taxonomy_id 
AND wp_3_term_taxonomy.term_id = '16' 
AND wp_3_posts.post_type = 'post' 
AND wp_3_posts.post_status = 'publish' ");


$count_terms2 = $wpdb->get_var ("SELECT COUNT(*)
FROM wp_4_term_taxonomy, wp_4_posts, wp_4_term_relationships 
WHERE wp_4_posts.ID = wp_4_term_relationships.object_id 
AND wp_4_term_relationships.term_taxonomy_id = wp_4_term_taxonomy.term_taxonomy_id 
AND wp_4_term_taxonomy.term_id = '13' 
AND wp_4_posts.post_type = 'post' 
AND wp_4_posts.post_status = 'publish' ");



$sum = $count_terms + $count_terms1 + $count_terms2;
echo "<p>Category A sum: {$sum}</p>";

?>
1
  • Thanks for coming back to post what you found! You can mark this answer as accepted if you like (that way it won't show up as unanswered). One more thing that would be helpful though is if you could edit this post to explain what the solution does, rather than just posting the code? Thanks heaps
    – Tim Malone
    Jun 29 '16 at 4:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.