How to compile WordPress author stats from a database
March 25, 2010 3:43 AM   Subscribe

Where to find WordPress post view stats.

I'm trying to write my first WordPress plugin. It's supposed to rank every blog author by how many hits they've received on their posts.

The working plan is to build on another plugin like "WordPress Popular Posts" that collects all this data for individual posts. The trouble is, I'm new to this and can't figure out where the stats are kept. How would I go about asking a database for this information, to work with?
posted by thelastenglishmajor to Computers & Internet (6 answers total)
 
Just taking a flying stab here, but I think this should get you close:

$query = "SELECT *, SUM(pageviews) FROM wp_posts, wp_popularpostsdata WHERE ID=postid AND post_status='publish' AND post_type='post' GROUP BY post_author";

$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo "author: ". $row['display_name']. " total views = ". $row['SUM(pageviews)'];
echo "
";
}

I'm sure that someone will soon point out what a SQL dud I am, but that should get you started.

The information you're looking for is in three tables: wp_posts, wp_users and one that the plugin adds called wp_popularpostsdata.

I'm sure there's a far more elegant way of achieving the result you're looking for. But I'm a self taught hack with no quality control gene what so ever.
posted by elroyel1327 at 4:29 AM on March 25, 2010


sorry, ignore my first try - use this:

$query = "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";
posted by elroyel1327 at 4:35 AM on March 25, 2010


Thanks! So it adds a new column, "wp_popularpostsdata," to the regular WordPress database. Fascinating.
posted by thelastenglishmajor at 8:29 AM on March 25, 2010


It adds two new tables to the database, one called wp_popularpostsdata and wp_popularpostsdatacache - or at least they are the only two that are immediately apparent as new.
posted by elroyel1327 at 9:12 AM on March 25, 2010


Great stuff, thanks.
posted by thelastenglishmajor at 9:55 AM on March 25, 2010


I finally figured it out.
posted by thelastenglishmajor at 11:14 PM on March 31, 2010


« Older I have a friend (living in the...   |  I just watched JFK. It IS a mo... Newer »
This thread is closed to new comments.