1

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>';
}
                    ?>
1
  • Have you seen the date query for WP_Query yet?
    – kaiser
    Jul 30, 2014 at 16:38

1 Answer 1

2

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;
}
12
  • How would I roll both into the query so I could display total in last week AND the total?
    – andy
    Jul 29, 2014 at 18:55
  • Well I suppose you could move the date test into the select clause, eg 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)) AS votes etc.
    – bonger
    Jul 29, 2014 at 19:19
  • Can you update your answer to write that within the context of my code?
    – andy
    Jul 29, 2014 at 19:21
  • Having trouble with the concat, want something like this:- CONCAT_WS('<div>VALUE HERE</div>/ this week',, 'VALUE HERE' being the number of votes. Reading about SQL concat_WS now.
    – andy
    Jul 29, 2014 at 23:41
  • It might be easier/nicer to do that using php, list($weekly_vote, $total_vote) = explode('/', $results[$idx]['vote']); $str = '<div>' . $total_vote . '</div>' etc
    – bonger
    Jul 29, 2014 at 23:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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