0

Running an SQL query to pull authors by votes they've received on posts.

@bonger helped me out on my first post and helped me get this working great, but I created this new question since it's a separate issue.

<?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+0 DESC LIMIT 0,5
                " );




                    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 Karma: ', $weekly_vote, '<br>',' Total Karma: ', $total_vote, '<br><br><br>';
                    }

                ?>

After doing some look around I've run across some example code that I'm having trouble integrating with this.

I'd like to have name link to the author URL and to display the author avatar.

Something like ->

<a href="' . get_author_posts_url( $author->ID ) . '">' . get_the_author_meta( 'user_nicename' , $author->ID ) . ' 
            ' . get_avatar( $author->ID , 40 ) . '
            </a> ' Weekly Karma: ', $weekly_vote, '<br>',' Total Karma: ', $total_vote, '<br><br><br>';

1 Answer 1

1

You just need to return the user ID in the main select and use that as the author ID. Also I probably confused the issue previously by using echo with commas (separate arguments) rather than dots (one concatenated argument).

$weekly = $wpdb->get_results ( "
                       SELECT u.ID,
                        ( 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+0 DESC LIMIT 0,5
                    " );
foreach ($weekly as $author) {
    list( $weekly_vote, $total_vote ) = explode( '.', $author->votes ? $author->votes : '0.0' );
    echo '<a href="' . get_author_posts_url( $author->ID ) . '">' . get_the_author_meta( 'user_nicename' , $author->ID ) . ' ' . get_avatar( $author->ID , 40 ) . ' </a> '
        . ' Weekly Karma: ' . $weekly_vote . '<br>' . ' Total Karma: ' . $total_vote . '<br><br><br>';
}
0

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.