Bin-BlogShow Popular Posts in WordPress – without a plugin
By Binny V A • Mar 22nd, 2009 • Category: Blogging, PHP, Scripts, Tutorials, WordPress
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…
- Top 10 – A Page Counter and Popular Posts plugin for WordPress
- Popularity Contest Plugin (beta)
- Wordpress Popular Posts
- MostWanted – a Popular Posts Plugin for WordPress
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,10Listing 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!
Follow me(@binnyva) on Twitter
I was using this on footer.. should add this again .. ( I lost it during an upgrade
)
Thanks very much for this tip.
Hey Thanks alot for the tip, I was searching this query on Google but did not find any solution. Thanks for the tip
Binny , i will try this.
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?
Thanks Binny,
made my work more easier..!
Wow, great tip. I am gonna try to implement it on my site. Thanks!
Great! I’ve tried it on of my blogs.
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.
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