Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm trying to do a query on wpdb to get the posts commented on most in the past week... Any ideas what I'm doing wrong?

$querystr = "SELECT comment_count, ID, post_title
        FROM $wpdb->posts wposts, $wpdb->comments wcomments
        WHERE wposts.ID = wcomments.comment_post_ID
        AND wcomments.comment_date >= CURDATE() - 7 
        GROUP BY wposts.ID
        ORDER BY comment_count DESC
        LIMIT 0 ,  10
 ";

 $pageposts = $wpdb->get_results($querystr);

Thanks in advance.

EDIT Query changed to something that sort of works I'm still not getting the date range right... it is returning the posts that are most commented on of all time, and not only the posts that have been commented on in the past week.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

This should do it:

$querystr = "SELECT comment_count, ID, post_title
        FROM $wpdb->posts wposts, $wpdb->comments wcomments
        WHERE wposts.ID = wcomments.comment_post_ID
        AND wposts.post_status='publish'
        AND wcomments.comment_approved='1'
        AND wcomments.comment_date > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 YEAR) 
        GROUP BY wposts.ID
        ORDER BY comment_count DESC
        LIMIT 0 ,  10
 ";
share|improve this answer
add comment

The column comment_count in the comments table is a sum of all comments on the post in question.

To get the comments for a time period, you must COUNT() the comments in each group.

$query="SELECT COUNT(comment_id) as num, ID, post_title
        FROM $wpdb->posts wposts, $wpdb->comments wcomments
        WHERE wposts.ID = wcomments.comment_post_ID
        AND wposts.post_status='publish'
        AND wcomments.comment_approved='1'
        AND wcomments.comment_date > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 WEEK) 
        GROUP BY wposts.ID
        ORDER BY num DESC
        LIMIT 0 ,  10
 ";
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.