Rarst asked: “How to get most commented posts of the week?”

Rarst, a reader who blogs at www.rarst.net, asked me “How to get something like list of X posts with most comments in last Y days?”
After some research, I’m happy to provide a code to answer Rarst question.

To achieve this hack, we have to make a custom SQL query by using the $wpdb object. Let's start by creating 3 php variables: The first one is the number of days between today and X days ago, the second is today's date and the last one is today's date - X days.

<?php
$days = 7; //To fetch posts published during the last 7 days
$today = date("Y-m-d H:i:s"); //Today's date
$daysago = date("Y-m-d H:i:s",strtotime(date('Y-m-j H:i:s')) - (7 * 24 * 60 * 60));  //Today - $days

$result = $wpdb->get_results("SELECT comment_count,ID,post_title, post_date FROM $wpdb->posts WHERE post_date BETWEEN $daysago AND $today ORDER BY comment_count DESC LIMIT 0 , 10");

foreach ($result as $topten) {
    $postid = $topten->ID;
    $title = $topten->post_title;
    $commentcount = $topten->comment_count;
    if ($commentcount != 0) {
    ?>
         <li><a href="<?php echo get_permalink($postid); ?>"><?php echo $title ?></a></li>
    <?php }
}
?>

You can paste this code wherever you want, to get the 10 most commented posts from the last 7 days.

How to: Display the most commented posts of 2008

21 Responses

Rarst

Oct 19 2008 20:11

Thanks for answer and link. I really have to read bit on PHP. Moving pieces of code around is easy enough but if I need something custom for my blog I am helpless.

jbj

Oct 19 2008 20:32

You’re welcome! That was a really interesting question and I enjoyed developing this hack!

EmmaB

Oct 20 2008 02:26

Hey JBJ,

Could you post a little snippet on creating simple horizontal menus in Wordpress?
Thanks

Armen Shirvanian

Oct 20 2008 09:50

This is high-quality material that you are providing. I had not come across any sites like this that provide direct code that people can use, with information about it and its usage. This is the type of site that one will be glad to check back onto. A person wanting to show the most commented posts of the week gets the answer instantly.

Nihar

Oct 22 2008 08:35

Thanks for the tip. for this functionality i downloaded most commentted post plugin. I guess the plugin will have the same kind of code.

Bingu

Oct 22 2008 13:24

That is what I’m looking for!
Thank you.

byme

Nov 03 2008 03:06

can you tell me
1. how to display total viewing single post like this

title post (1011 views)

2. how to make nav page like this

page 1 of 10, 1 2 3 4 5 …..[last]
byme

Bobby

Dec 09 2008 00:27

Hi,

Do you know if this trick works with WP 2.7 RC1?

I’d like to display the 5 most commented post of the last 30 days. I copied/paste your code and nothing appeared!

I don’t even have an error message!

Any idea or suggestion on how to fix that?

Jean-Baptiste Jung

Dec 09 2008 00:38

@Bobby: I think it will work but I haven’t tested it. I’ll do when i’ll have some time, and let you know.

Bobby

Dec 09 2008 00:39

Ok, thanks. There’s no hurry.

Monika

Dec 13 2008 23:44

This code creates error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘21:29:30 AND 2008-12-13 21:29:30 ORDER BY comment_count DESC LIMIT 0 , 10′ at line 1 f\xc3\xbcr the query SELECT comment_count,ID,post_title, post_date FROM wp_posts WHERE post_date BETWEEN 2008-12-06 21:29:30 AND 2008-12-13 21:29:30 ORDER BY comment_count DESC LIMIT 0 , 10 in require, require_once, include, get_sidebar, locate_template, load_template, require_once, referer:

regards
Monika

Jean-Baptiste Jung

Dec 13 2008 23:54

Hi Monika,
I have re-tested the code, and it works. What WP version are you using?

Monika

Dec 14 2008 00:24

WP 2.7 the new release

I’ve updated since two hours ago – from WP 2.6.5

I know there are comments the last 7 days – but what if not?

PHP5 and MySQL 5….

Monika

Jean-Baptiste Jung

Dec 14 2008 00:56

This code has been written before WP 2.7 release, so I can’t be sure it works with the latest release. I’ll check it out with WP 2.7, an eventually make a new recipe if needed

Monika

Dec 14 2008 01:00

I love recipes

and I can wait (tocktock ..tock.tock)

…
.