Hey everyone. I’m trying to write a plugin that figures out which blog authors are the most popular contributors. But I am still learning MySQL and would really appreciate some help.
So my basic idea was to start with an existing plugin called WordPress Popular Posts. This plugin collects pageview data and leaves it in the WordPress database under wp_popularpostsdata.
I’ve been trying to compile that information and sort it by author. I’ve been doing that using this query, but there might be a better way to do it:
SELECT *, SUM(pageviews) FROM wp_posts, wp_popularpostsdata, wp_users WHERE wp_posts.ID=wp_popularpostsdata.postid AND post_status=‘publish’ AND post_type=‘post’ GROUP BY post_author
But then when I run the query, I am not sure how to display or parse the results. I’ve been trying different things using the global $wpdb object.
What would the next step be? Any help much appreciated. Or alternative methods.