WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Custom Query With Join and Date Range (2 posts)

  1. rpophessagr
    Member
    Posted 3 years 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. Jack Reichert
    Member
    Posted 3 years 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
     ";

Topic Closed

This topic has been closed to new replies.

About this Topic