$wpdb select date range of posts

Question

0 down vote favorite Using this SQL query to sort users by how many votes(meta key) their posts have received.

<?php


                        global $wpdb;


                        $results = $wpdb->get_results ( "
                           SELECT u.display_name as name,
                            ( SELECT SUM(pm.meta_value)
                              FROM wp_posts p, wp_postmeta pm
                              WHERE p.post_author = u.ID
                              AND p.post_status LIKE 'publish'
                              AND pm.meta_key = 'epicredvote'
                              AND p.ID = pm.post_id ) as votes
                              FROM wp_users u
                              ORDER BY votes DESC LIMIT 20
                        " );

                        foreach ( $results as $result ) { 


                          echo "{$result->name}: {$result->votes} votes<br>";

                        }
                ?>

Never really worked with a direct SQL query in wordpress before like this, it was always the wp loop or variations thereof.

Anyway, this will display the username + their score (total).

I'd like to do is limit this to a date range of posts, say a week.

I think it'd be possible to use strtotime to do this, but I'm not sure of how.

  $date1 = date('Y-m-d', strtotime('+1 days'));
    $date2 = date('Y-m-d', strtotime('-8 days'));

So to get posts in the last 7 days? Googled around but can't find much.

Any help is appreciated.

EDIT:- Full code that isn't working for some reason.

<?php


                        global $wpdb;

                         $date2 = date('Y-m-d', strtotime('-60 days'));

                         $weekly = $wpdb->get_results ( "
                      SELECT u.display_name as name,
                        ( SELECT
                          CONCAT_WS('/',
                            SUM(CASE WHEN p.post_date < '$date2' THEN pm.meta_value ELSE 0 END),
                            SUM(pm.meta_value))
                          FROM wp_posts p, wp_postmeta pm
                          WHERE p.post_author = u.ID
                          AND p.post_status LIKE 'publish'
                          AND pm.meta_key = 'epicredvote'
                          AND p.ID = pm.post_id ) as votes
                          FROM wp_users u
                          ORDER BY votes DESC LIMIT 20
                    " );


                        foreach ($weekly as $result) {
    $name = $result->name;
    list( $weekly_vote, $total_vote ) = explode( '/', $result->votes ? $result->votes : '0/0' );
    // do stuff with $name, $weekly_vote, $total_vote ...eg
    echo  $name, '<br>', ' Weekly Vote: ', $weekly_vote,'<br>', ' Total Vote: ', $total_vote, '<br>', '<br>';
}
                    ?>

Answer 1

You basically have the answer, just add $date2 to the inner sql where clause:

AND p.post_date > '$date2'

to get last week. To get a particular week, use

AND p.post_date < '$date1' AND p.post_date > '$date2'

where eg

$date1 = date('Y-m-d', strtotime('2014-07-04 +1 days'));
$date2 = date('Y-m-d', strtotime('2014-07-04 -8 days'));

To return weekly/total votes, you could put the test into the select clause:

                    $results = $wpdb->get_results ( "
                       SELECT u.display_name as name,
                        ( SELECT
                          CONCAT_WS('.',
                            SUM(CASE WHEN p.post_date < '$date1' AND p.post_date > '$date2' THEN pm.meta_value ELSE 0 END),
                            SUM(pm.meta_value))
                          FROM wp_posts p, wp_postmeta pm
                          WHERE p.post_author = u.ID
                          AND p.post_status LIKE 'publish'
                          AND pm.meta_key = 'epicredvote'
                          AND p.ID = pm.post_id ) as votes
                          FROM wp_users u
                          ORDER BY votes+0 DESC LIMIT 20
                    " );

And then output results as desired:

foreach ($results as $result) {
    $name = $result->name;
    list( $weekly_vote, $total_vote ) = explode( '.', $result->votes ? $result->votes : '0.0' );
    // do stuff with $name, $weekly_vote, $total_vote ...eg
    echo 'Name: ', $name, ' Weekly Vote: ', $weekly_vote, ' Total Vote: ', $total_vote;
}