Custom Query With Join and Date Range

  • Resolved rpophessagr

    (@rpophessagr)


    13 years, 9 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.

Viewing 1 replies (of 1 total)

  • 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
 ";
Viewing 1 replies (of 1 total)
  • The topic ‘Custom Query With Join and Date Range’ is closed to new replies.