Show Popular Posts in WordPress – without a plugin

March 22, 2009 admin Blogging, PHP, Scripts, Tutorials, WordPress 20

WordPress Helo Effect

A list of the popular posts of the blog is a standard feature in many blogs. There are quite a few plugins that offer this feature…

Now, lets see how to do it without using a plugin. In case you are new to this blog, I am currently working on the Plugin Killer Series – a series of post in which I explain how to duplicate the functionality provided by some wordpress plugins – without having to install the plugin.

These plugins work by adding a view counter for each post – whenever a user visits a page, it will increment the count for that page by one. Unfortunately, WordPress does not provide this feature. But there is another indicator for the popularity of a post – its comment count.

The Code

Getting Comment Count – SQL

So the first step is to get the list of post with the most comments. To get that, we can use the following SQL statement…

SELECT id,post_title FROM wp_posts ORDER BY comment_count DESC LIMIT 0,10

Listing The Data – PHP/SQL

This query will return just 10 posts. If you want more(or less) post, just change the number after LIMIT accordingly. The PHP code for executing the query and getting its result is…

$popular_posts = $wpdb->get_results("SELECT id,post_title FROM {$wpdb->prefix}posts ORDER BY comment_count DESC LIMIT 0,10");
foreach($popular_posts as $post) {
	// Do something with the $post variable
}

Final Output as Links in a List – HTML/PHP/SQL

Next step is to get the URL of each post. The recommended way of doing this is by using the ‘get_permalink‘ function. Another thing we have to do is to map the result as an HTML list…

<li><h3>Popular Posts</h3>
<ul class="bullets">
<?php 
$popular_posts = $wpdb->get_results("SELECT id,post_title FROM {$wpdb->prefix}posts ORDER BY comment_count DESC LIMIT 0,10");
foreach($popular_posts as $post) {
	print "<li><a href='". get_permalink($post->id) ."'>".$post->post_title."</a></li>\n";
}
?>
</ul>
</li>

Note: The HTML part of the code may need to be changed – depending on your theme.

Add this code to the sidebar.php file in your theme – and you are done!

20 Comments

  3. Hey Thanks alot for the tip, I was searching this query on Google but did not find any solution. Thanks for the tip 🙂

  5. What if we want to display the top 5 as simple links after the first post/article on the main page? How can we display this? How could we setup a cron file to output this code to a static include file to be called from the main page?

  9. Thanks for posting, I was leaning towards this technique and your post made me choose it. Although I wish this could be expanded to take a some sort of an average between views and comments… I need to research a bit more, maybe make a post about it if I get it working. 🙂

  10. Great tip! I would add the improvement since all pages, revisions and posts are stored in the wp_posts table. Update the MYSQL statement to be this:

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

    This narrows the results to ONLY posts, not pages, and only those posts that are published, not just saved revisions.

    Cheers!
    Brian

  11. Really nice exemple!

    May I ask you something I didnt find anywere. You will be my savior if you can answer me 🙂

    What I want is to make a query who can show most popular post from a specific category and his sub-categories only.

    Here a example of what I’m trying to do.

    Videos
    –>Activities
    –>Bike
    –>Swimming
    –>Horse Riding
    –>Movie
    –>Action
    –>Romance
    –>Song
    –>Techno
    –>Hip Hop
    Home
    Others categories…

    So what I want to do is to build a query who will show post made in the videos category (including all his subcategories, sub-sub categories, etc)

    I see you already answer my first question (how to show top 10 popular post) now how can we specify the parent category and all his childrens as limit?

    I see a lot of plugin who can exclude certain category, but that not the way I want it… I wish some one may have the answer 🙂

    Thx in advance!

  12. Opps sory here the structure of my category:

    Videos (ID12)
    –>Activities
    –>Bike
    –>Swimming
    –>Horse Riding
    –>Movie (ID13)
    –>Action
    –>Romance
    –>Song (ID14)
    –>Techno
    –>Hip Hop
    Home (ID15)
    Others categories… (ID...)

  14. Hi! I’m trying to get a code to put on a page of the most commentes post! I find this tutorial, but I had already tested this code, and it just works on themes files, not in page, even using a plugin that enables php on pages…

    Can you help me find a code that I could use on pages?

  19. tnx for this post.
    but i want to know how can i show the most popular post base on the post views.
    cause you wrote ORDER BY comment_count.
    i added PostViews plugins.now how to change it base on post views ,not the comments_count
    😉

Comments are closed.