New to the community? Start here

Post & Comment Count

Is there a way to display the post and comment count of a user on their own blog sidebar? I’ve tried various approaches with no luck.

Tue, 28 Jul 2009 15:09:34
  • Andrew
    • Champion of Loops

    If you have the post and comment indexer plugins installed you can just run a count query on the wp_site_posts and wp_site_comments tables.

    The only tricky bit will be figuring out which user to pull data for if there is more than one administrator on a blog.

    Thanks,

    Andrew

  • drmike
    • DEV MAN’s Mascot

    Of the user or of the blog as a total for the blog? I know post count for a specific blog is kept as an option within that blog. If you use donncha’s sitewide plugin, you should be able to do post counts within that. Not sure if that would work with Andrews though.

  • GhostPool
    • Site Builder, Child of Zeus

    I want the post and comment count of the admin of the blog. I only allow one admin per blog so there won’t be any problem there.

    I do have both the post and comment indexer plugins installed – I’m not quite sure how I could count the posts and comments from them though. I’m a sql/php novice.

  • GhostPool
    • Site Builder, Child of Zeus

    I’ve discovered that the <?php the_author_ID(); ?> tag can generate the ID of the user’s blog you are visiting outside of the loop, but I have no idea how to integrate this into:

    echo $post_count = $wpdb->get_var("SELECT COUNT(*) FROM " . $wpdb->base_prefix . "site_posts WHERE post_author = ‘ ‘ ":wink:;

    Additionally, the comments code does not count comments outside of the admin’s own blog, the reason being they are assigned a comment_author_user_id of 0 when they post anywhere but their own blog. Is this a bug or to be expected?

    In this case the best approach seems to be to use their email address, although if they change it it doesn’t count all their old comments. :S

  • Andrew
    • Champion of Loops

    Hiya,

    I’ve discovered that the <?php the_author_ID(); ?> tag can generate the ID of the user

    That function call won’t help you much as it echoes the user id. You need a function that returns the user id. You could probably use the code from the the_author_ID() function though.

    Additionally, the comments code does not count comments outside of the admin’s own blog, the reason being they are assigned a comment_author_user_id of 0 when they post anywhere but their own blog.

    Are they logged in when commenting on other blogs?

    Thanks,

    Andrew

  • GhostPool
    • Site Builder, Child of Zeus

    Are they logged in when commenting on other blogs?

    That’s probably the reason why.

    That function call won’t help you much as it echoes the user id. You need a function that returns the user id. You could probably use the code from the the_author_ID() function though.

    I’m not sure I see the distinction. Why wouldn’t echoing it be sufficient?

  • GhostPool
    • Site Builder, Child of Zeus

    I seem to have got it working by applying the following queries to the code you provided Andrew. Some of this I’ve taken from elsewhere so I’m not sure if everything is necessary. For example I have no idea what LEFT OUTER JOIN refers to – still I got it working! Go me. :smiley:

    For posts:

    global $wpdb;

    $sql = "SELECT DISTINCT ID, post_author FROM" . $wpdb->base_prefix . "posts

    LEFT OUTER JOIN $wpdb->posts ON ($wpdb->posts.post_author =

    $wpdb->posts.ID)";

    $posts = $wpdb->get_results($sql);

    echo $post_count = $wpdb->get_var("SELECT COUNT(*) FROM " . $wpdb->base_prefix . "site_posts WHERE post_author = '" . $post->post_author . "'");

    For comments:

    global $wpdb;

    $sql = "SELECT DISTINCT ID, post_author FROM" . $wpdb->base_prefix . "posts

    LEFT OUTER JOIN $wpdb->posts ON ($wpdb->posts.post_author =

    $wpdb->posts.ID)";

    $posts = $wpdb->get_results($sql);

    echo $post_count = $wpdb->get_var("SELECT COUNT(*) FROM " . $wpdb->base_prefix . "site_comments WHERE comment_author_user_id = '" . $post->post_author . ");

  • GhostPool
    • Site Builder, Child of Zeus

    Although the above code works for calling the number of user comments, it does not seem to work correctly on new blogs with no posts. Instead of displaying 0 comments it displays 75. I’ve checked what happens if a user has a post, but no comment and it displays correctly as 0 – so it just seems to be new blogs with no posts that is problem.

    Can anyone see from the code why this may be?

    Thanks.

  • Andrew
    • Champion of Loops

    Hiya,

    You may want to consider hiring a developer to assist you with this.

    if ( !empty( $post->post_author ) || $post->post_author != 0 ) {

    POST COUNT QUERY GOES HERE

    }

    Really though, that’s not the best method of getting the user id. I’d really suggest hiring a developer to make you a proper plugin or even a widget if that would be better.

    Thanks,

    Andrew