WordPress.org

1. rpophessagr
   Member
   Posted 11 months ago #

   I'm trying to do a query 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, count( comment_post_id )
   	 FROM $wpdb->posts wposts, $wpdb->comments wcomments
   	 WHERE wposts.ID = wcomments.comment_post_ID
   	 AND wcomments.comment_date  > ".date( 'Y-m-d', strtotime('-7 days')."
   	 ORDER BY comment_count DESC
   	 LIMIT 0 ,  10
    ";
   
    $pageposts = $wpdb->get_results($querystr);

   Thanks in advance.

2. jackreichert
   Member
   Posted 11 months ago #

   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
    ";

Reply

You must log in to post.