Steve T's Semi-Cohreant Ramblings

Just another WordPress site

  • About Steve
  • Blog Roll
  • Contact

Word Press ShortStat Plugin Enhancement

Posted by stevet on December 18, 2007
Posted in: Geek Out.

One of the Word Press plugins I use is wp-shortstat. It is a handy stat tracker that allows me to look at keyword searches and referrers right at the Dashboard level within WP.

One of the drawbacks of this stats system is the country code lookups. For my setup it was not working at all, so I was getting a lot of unknown countries. That does not make it very useful. I did a bunch of research and decided that rolling my own lookup into wp-shortstat and using a free data source was the way to go.

Being the holiday season and being a fairly generous guy (don’t laugh, please), I am going to make the changes available to my reading public. All three of you. I can say that because I now have working stats 🙂

In order to do this update/upgrade you will have to be comfortable with your database, editing PHP files, and some basic UNIX text processing commands. There are no expert tasks here, by any stretch.

First step, download some data. I got my data from Software77. It is a ZIP file that contains a CSV file. Unfortunately, a small amount of conversion needs to be done on the CSV file. The first is to strip off the comment lines. Every comment line they use starts with a “#” so it is pretty easy to do:
[stevet@argon]$ grep -v "^#" IpToCountry.csv > IpToCountry.nohash.csv

Next, I needed to remove all of the quotes from the CSV file. If I tried to import with the quotes, the data in MySQL would not be in the right format- the quotes get imported along with the data I wanted. Easy enough to strip them off:
[stevet@argon]$ sed "s/"//g" IpToCountry.nohash.csv > IpToCountry.clean.csv

Now we have a bunch of data (about 4.5 meg) that is ready for the database.

Next we need to get the data into the database which requires two things: a new table in the wordpress database and running an import command. To create the needed table, connect to you blog’s database. Remember I am using MySQL, you will need to season these commands to taste if you are using Postgres or Oracle or whatever else. Here is the SQL to create table I used. It has the same naming convention as the other WordPress tables:
DROP TABLE IF EXISTS `wp_hostip`;
CREATE TABLE `wp_hostip` (
`ip_from` bigint(20) default NULL,
`ip_to` bigint(20) default NULL,
`registry` varchar(50) default NULL,
`assigned` int(11) default NULL,
`ctry` char(2) default NULL,
`cntry` varchar(10) default NULL,
`country` varchar(256) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Simple enough. Now you need to import the data into that table. Luckily MySQL has a really simple import from CSV command called LOAD. Here is the usage:
LOAD DATA LOCAL INFILE 'IpToCountry.clean.csv'
INTO TABLE wp_hostip
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'n'
(ip_from, ip_to, registry, assigned, ctry, cntry, country);

Now you have all of the data you could possible need (for awhile anyway). The trick is getting wp-shortstat to use it. Time to modify some PHP code. You only need to modify one function in one file.

Find your wp-shortstat.php file, it is in the wp-content/plugins directory. Open that file in your favorite editor and find the determineCountry function. Modify the function to look like the following. You can comment out the current code if you don’t feel comfortable deleting it.
function determineCountry($ip) {
global $wpdb;
list($a,$b,$c,$d) = split ('[.]', $ip);
$calc_ip = $d + ($c * 256) + ($b * 256 * 256) + ($a * 256 * 256 * 256);
$query = "SELECT ctry FROM wp_hostip where $calc_ip >= ip_from AND $calc_ip <= ip_to"; $country = $wpdb->get_var($query);
return $country;
}

That is it. Once the file is saved, the next time a non-admin connects to your blog wp-shortstat will log their arrival with the country code working.

I tested it by firing up the dusty IE on my box and connecting to my blog. I then looked in the wp_ss_stats table to see the newest entries and sure enough the stats are being updated with complete data. Sweet.

That is Part 1. If you got some value out of this let me know. I like to be complimented 🙂

Part 2: Automating the update of the wp_hostip data. The download site claims data should only need be updated every week at the most. I won’t even say it needs to be updated that often. I am going to update it monthly. Part 2 will explain how I go about doing that. Part 2 will also show how to fix the country data in wp_ss_stats that is currently marked as “unknown”.

Happy Holidays Everyone!

Posts navigation

← What Happened to All the Nice Guys?
Fun Christmas Lights →
  • Recent Posts

    • DirecTV 775 Message
    • Configuring BFGMiner on CentOS
    • 1-Wire for Temperature Monitoring – Part 3
    • 1-Wire for Temperature Monitoring – Part 2
    • 1-Wire for Temperature Monitoring
  • Recent Comments

    • stevet on BalanceNG Experts?
    • tgo on BalanceNG Experts?
    • stevet on Stupid NFL Officials
    • RoccoBoxer on Stupid NFL Officials
    • RoccoBoxer on Stupid NFL Officials
  • Archives

    • August 2016
    • November 2013
    • July 2013
    • July 2011
    • June 2011
    • August 2010
    • July 2010
    • May 2010
    • November 2009
    • August 2009
    • July 2009
    • June 2009
    • May 2009
    • April 2009
    • March 2009
    • February 2009
    • January 2009
    • December 2008
    • November 2008
    • October 2008
    • September 2008
    • August 2008
    • July 2008
    • June 2008
    • May 2008
    • April 2008
    • March 2008
    • February 2008
    • January 2008
    • December 2007
    • November 2007
    • October 2007
    • September 2007
    • August 2007
    • July 2007
    • June 2007
    • May 2007
    • April 2007
    • March 2007
    • February 2007
    • January 2007
    • December 2006
    • November 2006
    • October 2006
    • September 2006
    • August 2006
    • July 2006
    • June 2006
    • May 2006
    • April 2006
    • March 2006
    • February 2006
    • January 2006
    • December 2005
  • Categories

    • Geek Out
    • Home Improvement
    • Poker
    • Random Rants
    • Sports
    • Uncategorized
  • Meta

    • Log in
    • Entries feed
    • Comments feed
    • WordPress.org
Proudly powered by WordPress Theme: Parament by Automattic.