WordPress Expert Special Offer: Free WordPress Installation, Free WordPress Development Consultation, Budget cut-off for your WordPress customization

Get your own domain name for $10 only! Get the cheapest web hosting for $6 a year, Free WordPress Installation, Free WordPress Development Consultation, Budget cut-off for your WordPress customization, and much more. Get free web hosting to test your website first. Just contact us and we will never charge any cost, and the best part is you can also get free WordPress installation and ready to used for your website. Start today! Get this special offer Now! Don’t waste your time, it’s only until end of this year!

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

Posted on May 13th, 2010 in WordPress with 1 Comment(s). .

You might have noticed that some of WordPress based websites have a section on the home page where 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!


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!

feedsbot

Feedsbot is the search bot software used by website, which collects feeds from the web to build a searchable index for the special topics. Click here to view the original post.

Add Comment

Discussion 1 Comments

  1. Thank for this great post, i like what you

    read.

Add a comment