Comment Count for each Comment Author

1 Views

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:

Read More
$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.

Post Views: 1

Related posts

Leave a Reply

3 comments

  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 mail@example.com 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 = "mail@example.com"  
AND comment_approved = "1" 
AND comment_type IN ("comment", "")

  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.

  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());