This function below creates a list of links of the top commented posts on your Wordpress blog.
<?php
function listPopularPosts() {
global $wpdb;
$strBuidler = '';
$result = $wpdb->get_results("SELECT comment_count, ID, post_title FROM $wpdb->posts ORDER BY comment_count DESC LIMIT 0 , 5");
foreach ($result as $post) {
setup_postdata($post);
$postId = $post->ID;
$title = $post->post_title;
$commentCount = $post->comment_count;
if ($commentCount != 0) {
$strBuidler .= '<li>';
$strBuidler .= '<a href="' . get_permalink($postId) . '" title="' . $title . '">' . $title . '</a> ';
$strBuidler .= '(' . $commentCount . ')';
$strBuidler .= '</li>';
}
}
return $strBuidler;
}
?>
Call the function in your sidebar of footer like this:
<h2><?php _e('Popular Posts'); ?></h2>
<ul>
<?php echo(listPopularPosts()); ?>
</ul>
Awesome post
This works well. How would you limit the output to the 5 or 10 most commented?
Hello Loren,
You can easily change the number listed by changing the value in the SQL Statement. So, below where it says “LIMIT 0 , 5″ at the end of the statement, you would change 5 to the number desired.
$result = $wpdb->get_results("SELECT comment_count, ID, post_title FROM $wpdb->posts ORDER BY comment_count DESC LIMIT 0 , 5");It seems as though that if you had a lot of posts that your script would have to query the whole DB to find just the top 5 most actively discussed.
1. Wouldnt it be better to change the query to limit to the last 60 days so that not only would you only have the more relevant articles but also so you dont have to query the whole DB? Or would you still have to query the db anyway?
2. Ideally, it would be nice to set this up to rebuild as a cron job once per day and just include what is built by the daily job… no? Do wordpress offer anything like that? For instance, why build the category list every single time the page is hit when mine hasn’t changed in the last 6 months.
What if you want to display them on the main page in the loop instead of the sidebar? It seems as though this only works in the disbar.
Hello Chaz,
This works on the home/main page as well.
Hello Chaz,
You are correct in stating that it queries the whole database. If you wanted to limit so that it queries a specified date range, that’s possible as well. Cheers!
hi Richard
Great code, thanks for that. One question if thats ok. How would you add more details to the list? Id love to be able to add more of the content, perhaps even categories. Is this possible? Thanks again for the code!
Hey David,
Thanks for your comments! You can definitely add more content. You just need to change the SQL Query. For example, the query below will pull the content for the post as well!
$result = $wpdb->get_results("SELECT comment_count, ID, post_title, post_content FROM $wpdb->posts ORDER BY comment_count DESC LIMIT 0 , 5");