Today I needed the ability to determine which countries visitors are coming from for one of my websites.
I considered working out the country based on which languages the visitor’s browser is happy to accept, but decided I wanted something more accurate than that.
In the end I decided to use an IP-based solution and so downloaded the CSV file from http://software77.net/geo-ip/ which is a great tool to be able to quickly map countries to IP addresses.
Then I created a table in MySQL to hold this data:
DROP TABLE IF EXISTS `ipLocation`;
CREATE TABLE IF NOT EXISTS `ipLocation` (
`id` int(11) NOT NULL auto_increment,
`ipFrom` int(11) NOT NULL,
`ipTo` int(11) NOT NULL,
`reg` varchar(50) null,
`assigned` int(11) NULL,
`countryCode` char(2) NULL,
`countryAbbr` varchar(5) NULL,
`countryName` varchar(50) NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Then from the command line I ran the following snippet to import the CSV data into the table. Note that I am using user root with no password (yeah naughty I know, but it’s only my dev box). You will also need to change the path location from the one shown:
mysqlimport -uroot bip "/home/ben/ipLocation.csv" --columns=ipFrom,ipTo,reg,assigned,countryCode,countryAbbr,countryName --default-character-set=utf8 --fields-terminated-by=, --fields-enclosed-by=\" --lines-terminated-by="\n" --local --delete
I personally don’t care about some of the columns such as when the IP address was assigned, or the countries’ 3 letter abbrevation codes so I dropped them:
ALTER TABLE `ipLocation`
DROP `assigned`,
DROP `reg`,
DROP `countryAbbr`;
Finally to speed up query access time I indexed the ipFrom and ipTo fields:
ALTER TABLE `ipLocation`
ADD INDEX ( `ipFrom` ),
ADD INDEX ( `ipTo` );
Now your website can grap the REMOTE_ADDR header from the webserver, convert it to a long number with something like this:
$blocks = explode('.', $ip);
if(count($blocks)!=4) return 0;
return ($blocks[0] * 16777216) + ($blocks[1]*65536) + ($blocks[2]*256) + ($blocks[3]);
and then look up the country code with a query like this:
SELECT countryCode FROM ipLocation WHERE ipAddress_as_long BETWEEN ipFrom And ipTo
And now you have a free, quick, easy and fairly reliable way to work out the visitor’s country. For more accuracy you should update the table with fresh data periodically.
