WP-ShortStat SQL queries

• April 4, 2006
• James Skemp
• software

The wp-shortstat plugin, modified for WordPress 2.x use by Markus Kaemmerer, is a great little plugin. I've been using it since March 17, and since Markus has been doing a deal of updating (additional functionality, as well as bug fixes), I've been checking it on a regular basis (and have looked through the code more than once).

However, since I started out by using 1.3, I've got a problem with old records skewing my stats. To remedy that, I've written a couple of SQL queries, for use in phpMyAdmin, for example. Before I release them to the wild, I wanted to see if I can get a smaller audience to try them out. I've tried them out on my own stats, but ...

If someone wants to create a PHP page that does these, certainly go for it - just share the souce code. There's also a way to update more than one row at a time, but, I'd much rather do things one row at a time, just in case.

If you run these queries, please backup your database, or this table, before you run these queries. Note that the 'standard' table name is wp_ss_stats. If your's is named differently, you may have to change these queries accordingly.

UPDATE 'wp_ss_stats' SET version = '2.1' WHERE user_agent = 'Googlebot/2.1 (+http://www.google.com/bot.html)' AND version = 'Indeterminable'

UPDATE 'wp_ss_stats' SET version = '2.1' WHERE user_agent = 'Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)' AND version = 'Indeterminable'

UPDATE 'wp_ss_stats' SET version = '0.9' WHERE user_agent = 'msnbot/0.9 (+http://search.msn.com/msnbot.htm)' AND version = 'Indeterminable'

UPDATE 'wp_ss_stats' SET browser = 'msnbot' WHERE user_agent = 'msnbot/0.9 (+http://search.msn.com/msnbot.htm)' AND browser = 'Crawler/Search Engine'

UPDATE 'wp_ss_stats' SET version = '1.0' WHERE user_agent = 'msnbot/1.0 (+http://search.msn.com/msnbot.htm)' AND version = 'Indeterminable'

UPDATE 'wp_ss_stats' SET browser = 'msnbot' WHERE user_agent = 'msnbot/1.0 (+http://search.msn.com/msnbot.htm)' AND browser = 'Crawler/Search Engine'

UPDATE 'wp_ss_stats' SET version = '2.1' WHERE user_agent = 'Mediapartners-Google/2.1' AND version = 'Indeterminable'

UPDATE 'wp_ss_stats' SET browser = 'Feedfetcher-Google' WHERE user_agent = 'Feedfetcher-Google; (+http://www.google.com/feedfetcher.html)' AND browser = 'Indeterminable'

UPDATE 'wp_ss_stats' SET browser = 'Feedfetcher-Google' WHERE user_agent = 'Feedfetcher-Google; (+http://www.google.com/feedfetcher.html)' AND browser = 'Crawler/Search Engine'

UPDATE wp_ss_stats SET browser = 'Yahoo-Blogs' WHERE version = '5.5' AND user_agent = 'Yahoo-Blogs/v3.9 (compatible; Mozilla 4.0; MSIE 5.5; http://help.yahoo.com/help/us/ysearch/crawling/crawling-02.html )'

UPDATE wp_ss_stats SET version = '3.9' WHERE version = '5.5' AND user_agent = 'Yahoo-Blogs/v3.9 (compatible; Mozilla 4.0; MSIE 5.5; http://help.yahoo.com/help/us/ysearch/crawling/crawling-02.html )'

Want more added? Let me know. I'd like to do Googlebot and Yahoo! Slurp, among others, but I'm waiting for Markus to support them in his plugin. I figure once he thinks I'm done pestering him, I'll send him the code and a request ;)

Remember, running a SELECT query first is your best bet if you're unsure of what you may be updating.

SELECT * FROM wp_ss_stats WHERE version = '2.1' AND browser = 'Crawler/Search Engine'

• wordpress
• mysql