How to list commenters and days since last commented

Question

As a thank you to the hundreds of people who have contributed to my blog through comments, I have created a page which (1) lists them, with (2) how many comments they have made. So far, so good! What would be a great final touch however would be to show (3) how many days since they last posted, and that's where it's gone past my abilities. Any good ideas?

Here's what I've got for the working part:

function top_comment_authors($amount = 250) {
global $wpdb;
$results = $wpdb->get_results('
    SELECT
    COUNT(comment_author) AS comments_count, comment_author
    FROM '.$wpdb->comments.'
    WHERE comment_author != "" AND comment_type = "" AND comment_approved = 1
    GROUP BY comment_author
    ORDER BY comments_count DESC, comment_author ASC
    LIMIT '.$amount
);
$output = '<div>';
foreach($results as $result) {
    $output .= '<p>'.$result->comment_author.' ('.$result->comments_count.' comments)</p>';
}
$output .= '</div>';
echo $output;
}

Answer 1

It looks like you could add the

MAX( comment_date ) last_commented_date

to the field list to get the latest date, each user commented.

Then use e.g. the human_time_diff() function to get a human readable text for how long ago each user commented.

human_time_diff( strtotime( $result->last_commented_date) ) 

For safety reasons, you should then prepare the SQL statement with $wpdb->prepare(), since it contains the user input $amount.

Answer 2

Thanks so much. Final (working) code for anyone else who discovers this:

function top_comment_authors($amount = 200) {
global $wpdb;
    $prepared_statement = $wpdb->prepare(
    'SELECT
    COUNT(comment_author) AS comments_count, comment_author, MAX( comment_date ) as last_commented_date
    FROM '.$wpdb->comments.'
    WHERE comment_author != "" AND comment_type = "" AND comment_approved = 1
    GROUP BY comment_author
    ORDER BY comments_count DESC, comment_author ASC
    LIMIT %d',
    $amount);
    $results = $wpdb->get_results($prepared_statement);
$output = '<div class="comments">';
foreach($results as $result) {
    $output .= '<p class="comment-author">'.$result->comment_author.' &bull; '.$result->comments_count.' comments, last comment '.human_time_diff(strtotime($result->last_commented_date)).' ago</p>';
}
$output .= '</div>';
echo $output;
}