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.
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!
Any questions? Thanks for viewing or reading!