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!
