Add Snippet To Project
If you’re looking for a way to increase engagement on your WordPress site, highlighting your top commentators can be a great way to do it. By showcasing the users who have contributed the most to your content, you can encourage others to join in on the conversation and build a sense of community around your site. In this article, we’ll show you how to create a list of your top commentators in WordPress.
function wpturbo_list_top_commentators() {
global $wpdb;
$commentators = $wpdb->get_results("
SELECT COUNT(comment_author_email) AS comments_count, comment_author_email, comment_author, comment_author_url
FROM $wpdb->comments
WHERE comment_author_email != '' AND comment_type = '' AND comment_approved = 1
GROUP BY comment_author_email
ORDER BY comments_count DESC
LIMIT 10
");
if ( $commentators ) {
echo '<ul>';
foreach ( $commentators as $commentator ) {
$avatar = get_avatar( $commentator->comment_author_email, 60 );
echo '<li>';
echo '<a href="' . $commentator->comment_author_url . '" rel="nofollow">' . $avatar . '</a>';
echo '<div class="commentator-info">';
echo '<h3>' . $commentator->comment_author . '</h3>';
echo '<p>' . sprintf( _n( '%s comment', '%s comments', $commentator->comments_count, 'wpturbo' ), number_format_i18n( $commentator->comments_count ) ) . '</p>';
echo '</div>';
echo '</li>';
}
echo '</ul>';
}
}
The code snippet above defines a function called wpturbo_list_top_commentators() which lists the top 10 commentators in WordPress based on the number of comments they’ve left on your website.
First, we use a global variable $wpdb to access WordPress database and run a custom SQL query that selects relevant fields from the wp_comments table. This query retrieves the count of comments, email address, name and website URL of commentators who have left valid comments (comment_type = ” and comment_approved = 1), and groups them by email address. Then, it orders the comments in descending order based on the number of comments.
global $wpdb;
$commentators = $wpdb->get_results("
SELECT COUNT(comment_author_email) AS comments_count, comment_author_email, comment_author, comment_author_url
FROM $wpdb->comments
WHERE comment_author_email != '' AND comment_type = '' AND comment_approved = 1
GROUP BY comment_author_email
ORDER BY comments_count DESC
LIMIT 10
");If the query retrieves any results, we proceed to print the commentator list. First, we create a list using ul (unordered list) HTML tag.
if ( $commentators ) {
echo '<ul>';
}Then, for each commentator in the query result we create an li (list item) and print their avatar, name, and the number of comments they’ve left. To get the avatar we use the built-in get_avatar() function that accepts the commentator’s email address and size of the avatar as parameters.
foreach ( $commentators as $commentator ) {
echo '<li>';
$avatar = get_avatar( $commentator->comment_author_email, 60 );
echo '<a href="' . $commentator->comment_author_url . '" rel="nofollow">' . $avatar . '</a>';
echo '<div class="commentator-info">';
echo '<h3>' . $commentator->comment_author . '</h3>';
echo '<p>' . sprintf( _n( '%s comment', '%s comments', $commentator->comments_count, 'wpturbo' ), number_format_i18n( $commentator->comments_count ) ) . '</p>';
echo '</div>';
echo '</li>';
}Notice that we use the _n() function to handle singular and plural forms of the word "comment" based on the number of comments for each commentator. We format the number with number_format_i18n() to ensure proper localization support. Finally, the commentator-info div group the name and the count. We add a nofollow attribute to the link to discourage accidental clicks.
Finally, we close the unordered list with the closing ul tag.
if ( $commentators ) {
echo '</ul>';
}