Advertisement
WordPress

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

by

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!

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!

Related Posts
  • Code
    WordPress
    Mastering WordPress Meta Data: Querying Posts and Users by Meta DataMetadata
    So far in this series you've learned how to access WordPress meta data, and work with the arrays in which they are returned. We don't just add custom fields to WordPress posts so we can display this information, but it also so we can sort by it. Now that you know how to retrieve and display meta data, it's time to learn how to customize the WordPress loop to return only posts with specific meta values.Read More…
  • Code
    Creative Coding
    Using WordPress for Web Application Development: Custom Database QueriesApplication foundation 400
    Throughout this series, we've been looking at the various facilities that make it possible to treat WordPress as a foundation for web application development. Thus far, we've covered a lot of ground: We've talked about how WordPress is more of a foundation rather than a framework. We've discussed the nature of the the Event-Driven Design Pattern. There's been a discussion of Email, User Management, Saving Data, Retrieving Data ...and more. In the most recent articles, we've been talking a look at how to handle queries against the WordPress database through the use of WP_Query and WP_User_Query.Read More…
  • Code
    Creative Coding
    Using WordPress for Web Application Development: WP_User_QueryApplication foundation 400
    In this series, we've been taking a look at how WordPress can be used to development web applications much like a number of different frameworks and other tools that are available. Starting in the last article, we began looking at the different options that we have as it relates to querying the WordPress data. First, we reviewed WP_Query.Read More…
  • Code
    Creative Coding
    Using WordPress for Web Application Development: Features: Custom Queries with WP_QueryApplication foundation 400
    We've been looking at how WordPress can be used as a foundation for application development, but one of the things that we've yet to cover that most modern frameworks offer is how to query the database to retrieve results for any given view. Specifically, we haven't talked about how to get information out of the database and insert it into our pages.Read More…
  • Code
    Databases
    Mapping Relational Databases and SQL to MongoDBMongodb retina preview
    NoSQL databases have emerged tremendously in the last few years owing to their less constrained structure, scalable schema design, and faster access compared to traditional relational databases (RDBMS/SQL). MongoDB is an open source document-oriented NoSQL database which stores data in the form of JSON-like objects. It has emerged as one of the leading databases due to its dynamic schema, high scalability, optimal query performance, faster indexing and an active user community.Read More…
  • Code
    Plugins
    Using HighCharts in WP-AdminHighcharts 400
    Charts are a great way to present data. They make data more digestible by making it visually appealing. In WordPress, there is no built-in method for getting posts and pages data in a graphical form. Although, there are certain plugins available which integrate Google Analytics with WordPress, but they are overkill if you want to get only a portion of that data. Also, nothing should keep you from learning new techniques and to dive straight into the subject is the best way to learn.Read More…