Comment Count for each Comment Author

Question

In WordPress 3.4.1: I am trying to add the Comment Author's total number of comments beside their info. (name + date/time) in the comment's list.

Here is the SQL that I am using:

$count = $wpdb->get_var('SELECT COUNT(comment_ID) FROM '. $wpdb->comments. ' WHERE comment_author_email = "' . get_comment_author_email() .'" ' AND comment_approved = '1' AND comment_type = '');

Basically The Author's email will be checked and the total number of comments will be displayed. But also I want only approved comments to be counted and also I don't want pingbacks/trackbacks to be counted. The above code is returning a syntax error and probably is wrong in terms of logic. Any help is appreciated. thanks.

Answer 1

Place this in your functions.php theme file:

<?php
function ps_count_user_comments() {
    global $wpdb;
    $count = $wpdb->get_var(
    'SELECT COUNT(comment_ID) FROM ' . $wpdb->comments. ' 
    WHERE comment_author_email = "' . get_comment_author_email() . '" 
    AND comment_approved = "1" 
    AND comment_type IN ("comment", "")'
    );

    return $count . ' comments';
}
?>

This code will count the author comments and do NOT include Trackbacks/Pingbacks.

Then You print it like this:

<?php echo ps_count_user_comments(); ?>

You can try to run this in your SQL and change [email protected] to your mail. I assume your database prefix is wp_ but if its not, just change wp_comments to your prefix.

SELECT COUNT(comment_ID) FROM wp_comments 
WHERE comment_author_email = "[email protected]"  
AND comment_approved = "1" 
AND comment_type IN ("comment", "")

Answer 2

You can use the built-in WordPress comment query for that. I also use get_comments(); for this feature on my template.

For the parameters, I simply use 3 parameters, the same as you want.

    $comail = get_comment_author_email();
    $args = array(
                'author_email' => $comail,
                'status'  => 'approve',
                'type'  => 'comment',
                );

wp_comment_query is limitless. So anything is possible. Multiple comment-type is also supported with the 'type__in' parameter.

Next, you can echo the count($comments);. You can also use 'count'=>'true', parameter.

Answer 3

Bikran solutions can also uses as function, so you can use in several places at your theme. Also, I think he's code is needing an extra line before count($comments):

$comments = get_comments( $args );

So the ready-to-use function would be:

function my_count_comments($comail){
    $args = array(
                'author_email' => $comail,
                'status'  => 'approve',
                'type'  => 'comment',
                );
    $comments = get_comments( $args );
    return count($comments);
}

And uses:

echo my_count_comments(get_comment_author_email());