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!
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!
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 Jeffrey Way
Join our newsletter!
Create a Tutorial, Get Paid!
Have something to teach the world? Want to earn money doing it? Tutorials, screencasts, and articles published here on Nettuts+ are largely contributed by readers just like you! We'll pay you great money for good content. Find Out More
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
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!
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();
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.
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.
Yes, I prefer using query_posts() than raw SQL query. It’s easy to control, and much safer for beginners.
I think this method is more WordPress Like and better code… :)
Thank you for the SIMPLE tip :P
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.
Thanks a lot. Great tutorial.
Nice one. Simple like that :)
Also agree on.. if you can better code it yourself than use a plugin.
Regards
thank you very much for the turorial mr. way,
i have been looking for custom query for wp, its really very helpful
Thank you for this im just getting into making WP themes and this will help allot.
I love nuts and bolts tips like this, their simple and effective without having to go through a huge tut. Great stuff!
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).
Well pingbacks/trackbacks are also a measure of popularity.
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.
Great way to count how many people are involved in your daily discussions.
very nice post thanks
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.
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.
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.
I used <a href="guid; ?>”> its shorter and easyer to remember.
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.
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.
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………..
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..
Nice one mate! Thanks!
Just what I need.
Thank you so much!
If anyone help me I will appreciate it.
How insert the comment programmatically?
I desperately need to know!
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
Need help!
How to insert post programatically ?
Any idea?
Thanks!
There you go. http://www.wprecipes.com/wordpress-tip-insert-posts-programmatically
Great little post there.
Thanks for the great quick tip. Useful to achieve those things from WP …
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.
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.
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
And if I want to display the popular posts only in one category ? This code work for all categories
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:
Hi! Anyone knows a metod to exclude some post?
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!
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
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.
surprenant dépendance bien pour tout. votre site internet est tout sans façon surprenant et lumineux
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 ????
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.
Great tweak man..i was searching for the tweaks that work on my theme without installing any theme.Thanks a ton for it :)
I have applied it to my website, thanks again.
this is what I’m looking for.. thanks
thnks a lot…!! really a good plugin…..