Quick Tip: “Popular Posts By Comment Count” SQL Query in WordPress
videos

Quick Tip: “Popular Posts By Comment Count” SQL Query in WordPress

on May 2nd 2010 with 51 comments
Tutorial Details
  • Difficulty: Moderate
  • Estimated Completion Time: 5 Minutes
  • Topics: WordPress, MySQL

You might have noticed that the Tuts+ sites have a section on the home page where we list the most popular posts of the month, according to comment count. While there are numerous plugins available, it’s always best to write the code yourself if you can. Too much abstraction is never a good thing! Luckily, once we learn how to query WordPress’ database, tasks like this become a cinch!


Subscribe to our YouTube page to watch all of the video tutorials!

Prefer to watch this video on Screenr?

The SQL Query

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:

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

id, post_title, and comment_count our columns within the database.

MySQL DB

Because WordPress allows you to set a custom prefix for your database tables, we need to fetch that prefix dynamically, by using {wpdb->prefix}posts. In this case, that results in “wp_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.

$pop = $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($pop 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.

Once you’ve learned how to directly interact with the WordPress database, you then have a lot of power at your disposal; this is only the tip of the iceberg!

One Last Thing

This tutorial was created to demonstrate how to specifically query WordPress’ tables in the database. Truthfully, as of WordPress 2.9, you can achieve this exact effect by using query_posts(), and passing orderby=’comment_count‘. But remember, it all boils down to the same thing: passing a SQL query to the database.

Any questions? Thanks for viewing or reading!

 
Enjoyed this Post?

Subscribe to our RSS Feed, Follow us on Twitter or simply recommend us to friends and colleagues!

By

Rollover to read this author's bio or click through to see a full list of posts by this author.

I'm the editor of Nettuts+, and love making the process of building for the web as easy as possible. Also, I'm finishing up my latest book on Laravel Testing! If you want be friends, say hi on Twitter.  

Plus Premium
 
 
Note: Want to add some source code? Type <pre><code> before it and </code></pre> after it. Find out more
  • http://www.dans-blog.com Dan Walker

    Great little post there =]

    It’s always better to write your own code, that way if it ever stops working or you fancy tweaking it a bit you know exactly how – just remember to leave comments! :P

  • http://360signals.com Maor

    Thanks for the great quick tip!

    By the way, A shorter way to target the table with the prefix is to write down $wpdb->posts.

    Anyway, very useful tip! Thanks!

  • http://wpcanyon.com Slobodan Kustrimovic

    Or simply like this (bellow)?

    query_posts(array(‘orderby’ => ‘comment_count’, ‘showposts’ => $numPosts));

    if (have_posts()) : while (have_posts()) : the_post();
    html here
    endwhile; endif;

    wp_reset_query();

    • http://www.jeffrey-way.com Jeffrey Way
      Author

      Right – but I really wanted to show how to specifically query the database. But you’re right, maybe I didn’t get that point across. I’ll update the article portion to clarify that.

      • http://nexik.net Nexik

        Writing the own SQL is better, you don’t need to grab all columns if need only 3. This maybe isn’t familiar to home-made wordpress owners, but if you want to build bigger website you must think about speed of your code.

    • http://www.deluxeblogtips.com Deluxe Blog Tips

      Yes, I prefer using query_posts() than raw SQL query. It’s easy to control, and much safer for beginners.

    • http://wptricks.net WP Tricks

      I think this method is more WordPress Like and better code… :)

  • http://izulcybercafe.com Izulcybercafe

    Thank you for the SIMPLE tip :P

  • http://www.xcellence-it.com/ Xcellence IT

    Hey, thanks for this great tips, very useful to achieve those things from WP which it does not support out of box.

    Thanks for Sharing.. Keep writing.

  • http://psdho.me PSDhome – Everyday free PHOTOSHOP files

    Thanks a lot. Great tutorial.

  • http://php.quicoto.com quicoto

    Nice one. Simple like that :)

    Also agree on.. if you can better code it yourself than use a plugin.

    Regards

  • sanil shakya

    thank you very much for the turorial mr. way,
    i have been looking for custom query for wp, its really very helpful

  • Damion

    Thank you for this im just getting into making WP themes and this will help allot.

  • http://www.jeffadams.co.uk Jeff Adams

    I love nuts and bolts tips like this, their simple and effective without having to go through a huge tut. Great stuff!

  • http://www.impressivewebs.com Louis

    If I’m not mistaken, this method will still include trackbacks and pingbacks in the comment count, so the results will not be equal to the real comments (if the post allows pings/trackbacks). On my site, I’m using a filter function in my functions.php file to separate comments from pings/trackbacks, then hide the trackbacks (since they’re generally pretty useless).

    • http://wpcanyon.com Slobodan Kustrimovic

      Well pingbacks/trackbacks are also a measure of popularity.

      • http://www.impressivewebs.com Louis

        Yeah, and they’re also spam bait. I allow p/tb on my site, but I don’t publish them, and I only allow legit ones to “count”. I don’t think anyone should publish a list of p/tb on their posts, because it encourages spammers to try to get backlinks that way — even though they are nofollow. Unfortunately, the stupidity of spammers is just as annoying as their persistence.

  • http://www.jordanwalker.net Jordan Walker

    Great way to count how many people are involved in your daily discussions.

  • http://ds.laroouse.com esranull

    very nice post thanks

  • http://www.midaym.com midaym

    also note that since you did not show them how to get a permalink for posts, just pulling the title itself is sort of useless for what the title of this post claims to be achieving. Which is why the query_posts method exists in the first place.

    • http://www.jeffrey-way.com Jeffrey Way
      Author

      I think I referenced using get_permalink($post->id), didn’t I? Look, I’m not saying that this is the only way to do it. But it’s always important to note what you CAN do, should you one day need to query the tables.

      • http://pixelcoder.co.uk Alistair

        Always useful to know the many ways to get something done in the tech field. Meaning that when you hit a wall down one road there are plenty more avenues to explore.

        Thanks for the freebie. Another great contribution.

      • Damion

        I used <a href="guid; ?>”> its shorter and easyer to remember.

  • http://www.hirephpdeveloperindia.com Nehal

    Hi,

    First of all i would like to thank you for giving valuable guidance about going deep into wordpress. I am also agree with you on this for using wordpress power at it’s best so you can use wordpress in your way. Many thanks for sharing this. I hope you will keep updating about other useful function as well.

  • Leaf.

    How would one best adopt this “orderby comment count” into the standard loop for index and archive.php?

    My goal is to achive this output, but I’m sure of the best way to approach it.

    - Show all sticky posts
    - static html block
    - show remain posts (all non-stickies) orderd by comment count

    All advice much welcomed.

  • http://www.searchengineoptimization.co.uk Mike

    This is nice short post on WP DB, again useful piece of code which can be used in many ways as per you need!
    I will sure try it to amend and having more useful contents on blog home page………..

  • http://www.minilibra.com Bambang Sugiarto

    nice tips, i agree with Nexix, yes, it’s correct, writing your own SQL is better and more faster since you don’t need to grab all columns and can limited the rows that you want to display..

  • http://www.5-squared.com 5-Squared

    Nice one mate! Thanks!

  • http://cmdesign.ca webdesigner

    Just what I need.
    Thank you so much!

  • http://bcwebtech.info webdesigner

    If anyone help me I will appreciate it.

    How insert the comment programmatically?

    I desperately need to know!

  • Kristof

    as others pointed out there are other ways to make this.
    What this tutorial shows in the first place is:

    You’re not TIED to the loop or the template tags when developing a WP Theme.
    It’s may be the simplest way, but not the most flexible for querying the WordPress-DB

  • http://cmdesign.ca/apply.html webdesign

    Need help!

    How to insert post programatically ?

    Any idea?

    Thanks!

  • http://www.searchengineoptimisation.com Phil

    Great little post there.

    Thanks for the great quick tip. Useful to achieve those things from WP …

  • http://www.indiaseo.com NICK

    Hey, thanks for this great tips, very useful to achieve those things from WP which it does not support out of box.

    Thanks for Sharing.. Keep writing.

  • http://www.indiaseo.com sam

    First of all i would like to thank you for giving valuable guidance about going deep into wordpress. I am also agree with you on this for using wordpress power at it’s best so you can use wordpress in your way. Many thanks for sharing this. I hope you will keep updating about other useful function as well.
    This is nice short post on WP DB, again useful piece of code which can be used in many ways as per you need!.
    thanks for sharing.

  • http://thegymbuzz.com Brett Sanders

    Excellent post. Very easy to understand.

    Do you know if it is possible to show most popular posts in the search results… basically order the search results so the posts with the most comments are listed first?

    And how would I go about starting this?

    Thanks

  • http://jocurionline.madland.ro Delgado

    And if I want to display the popular posts only in one category ? This code work for all categories

  • http://www.afrancois.co.uk Alex

    You could also check if the post is actually published if you want to sort by date or another variable too using:

    post_status=’publish’

    $pop = $wpdb->get_results(“SELECT id, post_title, comment_count FROM {$wpdb->prefix}posts WHERE post_type=’post’ AND post_status=’publish’ ORDER BY post_date DESC LIMIT 10″);

    i would also add a call to the WordPress post formatting function, setup_postdata(), that automatically populates the required variables.

    at the start of the foreach loop so you can use friendly functions like the_title, the_permalink etc:

  • http://www.dren.it liliansi

    Hi! Anyone knows a metod to exclude some post?

  • http://www.facebook.com/profile.php?id=100002414771667&v=info daniel mamann

    When I originally commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get four e-mails with the same comment. Is there any way you can remove me from that service? Thanks!

  • http://blog.koskoz.info koskoz

    This query doesn’t grab the most popular post from the last month.
    This one does :

    SELECT id, post_title, comment_count FROM {$wpdb->prefix}posts WHERE post_type=’post’ AND MONTH(post_date) = MONTH(NOW()) AND YEAR(post_date) = YEAR(NOW()) ORDER BY comment_count DESC LIMIT 10

  • http://alivincent.com/Blog Email Marketing Solution

    Try to follow as many people that are following you. Proper Twitter etiquette dictates that you pay the favor forward and participate in the same fashion. Avoid adding people you are following in bulk. Allow the number to grow organically though your involvement.

  • http://retrouverlesommeil.com insomnie

    surprenant dépendance bien pour tout. votre site internet est tout sans façon surprenant et lumineux

  • http://www.saudkhan.com Omer

    i want to know how i can ‘{{{{orderby’ => ‘comment_count’,}}}} i dont want posts thru comments counts i need them thru page view

    how can i do that ????

  • MetalloR

    SQL is a standard language for accessing databases.

    Our SQL tutorial will teach you how to use SQL to access and manipulate data in:

    MySQL, SQL Server, Access, Oracle, Sybase, DB2, and other database systems.

  • http://www.latestonnet.com/ Govind Choudhary

    Great tweak man..i was searching for the tweaks that work on my theme without installing any theme.Thanks a ton for it :)

  • http://www.nonstopcars.com nonStopCars

    I have applied it to my website, thanks again.

  • http://www.techispeaks.com Kathir

    this is what I’m looking for.. thanks

  • http://techarta.com nikhil

    thnks a lot…!! really a good plugin…..

  • Peter

    The best plugin ever, thank you so much.
    I plan on installing it on my blog at http://www.essaysexperts.net