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

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:

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.

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.

Testimonials

I have worked with dozens of developers over the last decade, but very few who work as hard as Bambang. "Bam" uses creative thought, unlike many of the "according to instructions" developers I've worked with from his region. Bam is great at asking questions, finding new and better solutions, and keeping costs low. I expect to work with Bam into 2010 and beyond because of his dependability, talent and accountability.— Steve Wiideman, California, USA

WordPress Solutions

We creates highly-customized blogs and blog-powered websites, taking advantage of the robust WordPress platform and fully optimizing it. We regularly recommend this amazing software and now the majority of our clients happily use WordPress.