0

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;
}
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.

| improve this answer | |
  • Thanks so much. Final (working) code for anyone else who discovers this below. – Chris Rand Nov 27 '16 at 11:18
  • Glad to hear it worked for you. You can always accept your own answer to mark the question as resolved.. @ChrisRand – birgire Nov 27 '16 at 12:01
1

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;
}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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