List the most popular wordpress posts according to comment count

May 23, 2010 · Print This Article

If you want to have a section on the home page where you list the most popular posts of the month, according to comment count. it’s always best to write the code yourself if you can. Once we learn how to query WordPress database, tasks like this become easy.

To query WordPress database, we can use the $wpdb->get_results() method. As the parameter, we simply pass a SQL query. In this particular example, let’s say that we only want to display a list of popular posts within our sidebar. We can use the following query:

$p_result = $wpdb->get_results("SELECT id, post_title, comment_count FROM {$wpdb->prefix}posts WHERE post_type='post' ORDER BY comment_count DESC LIMIT 10");

WordPress allows us to set a custom prefix for your database tables, so we need to fetch that prefix dynamically by using {wpdb->prefix}posts.

Next, we must ensure that we only display posts, and not anything else, such as attachments or pages.

Finally, we ORDER BY comment_count. That’s the point of all this right? Display the most commented postings?

Now that our SQL query has been executed, we can simply use a foreach statement to filter through the results, and display them on the page:

$p_result = $wpdb->get_results("SELECT id, post_title, comment_count FROM {$wpdb->prefix}posts WHERE post_type='post' ORDER BY comment_count DESC LIMIT 10");

foreach($p_result as $post) : ?>
<li> <?php echo $post->post_title; ?></li>
<?php endforeach; ?>

Note that the $post variable will have access to any of the columns that we fetched. In our case, we’d have access to:
$post->id : The id of the post
$post->post_title : The title of the post
$post->comment_count : The number of comments for that particular post.

If you need the permalink, you can either also SELECT from the “guid” column, or you can just use the get_permalink($post->id) method, and pass in the id of the post accordingly.

As of WordPress 2.9, you can achieve this exact effect by using query_posts(), and passing orderby=’comment_count‘.


Your choice for site templates and wordpress themes

Comments

Got something to say?

You must be logged in to post a comment.