Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm currently working on a plugin that I hinted at here and so far I have managed to code everything, only missing one piece of the puzzle.

This is how I get the total comment count for the current_user:

global $wpdb, $current_user;
get_currentuserinfo();
$userID = $current_user->ID;

$where = 'WHERE comment_approved = 1 AND user_id = ' . $userID ;
$comment_count = $wpdb->get_var("SELECT COUNT( * ) AS total 
     FROM {$wpdb->comments}
     {$where}");

echo 'Total Comments: ' . $comment_count;

I want to echo the current_user's total comment count on other users posts, so I don't want to count the comments on the author's own posts. Is such thing possible? And if so, how?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

I'm not aware of any WordPress functions that do this, so you can try to play with this kind of query (untested):

function get_custom_user_comments_count( $uid ){
    global $wpdb;

    $sql = "SELECT COUNT(*) as total 
            FROM {$wpdb->comments} as c  
            JOIN {$wpdb->posts} as p ON p.ID = c.comment_post_ID 
            WHERE c.comment_approved = '1' 
            AND p.post_status ='publish'  
            AND p.post_type ='post'  
            AND p.post_author != c.user_id            
            AND c.user_id = %d";

    $comment_count = $wpdb->get_var( $wpdb->prepare( $sql, $uid ) );
    return $comment_count;
}

and use it like this:

global $current_user;
get_currentuserinfo();
echo 'Total Comments: ' . get_custom_user_comments_count( $current_user->ID );

This assumes that the comments are written when the users are logged in, so the user_id column in the wp_comments table is populated.

Update:

Another approach would be to collect the comments count per user in the user meta. You could try for example to hook into the comment_post action. This approach could be implemented before the users write comments on the site.

share|improve this answer
    
How heavy is this query for the server? Is it much different than the one that I was already using for the total amount of comments? Optimisation is essential in this case. –  Christine Cooper May 25 '13 at 17:30
1  
I just tested this and it did not echo the comment count, I think it went into a different column. Can you kindly give it a test run and potential fix it? –  Christine Cooper May 25 '13 at 17:38
    
It's difficult to say in general. You could try using EXPLAIN to check the query and then profile it if you run this on your site. The JOIN queries are usually not as slick as a simple SELECT query, but WordPress is using lot of JOIN queries. I'm not sure you can get this information without combining the wp_comments table and wp_posts table together. –  birgire May 25 '13 at 17:50
1  
I'm only mobile today, but I will try it out tomorrow on my install ;-) –  birgire May 25 '13 at 18:11
1  
Hi again, I updated the answer, the problem was that I wrote $wpdb->get_var( $wpdb->prepare( $sql ), $uid ) instead of $wpdb->get_var( $wpdb->prepare( $sql, $uid ) ) ;-) I added another idea, to collect the comments count via hook, but that would mean you would have to do that, before the users start to comment on the site. –  birgire May 27 '13 at 0:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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