I'm a professional game developer from Wakefield, England, working for TickTock Games.
I'm a married father of five and I am also the director and lead programmer of Retroburn Ltd.
Martin 'Bytrix' Caine
Father. C++ Games Programmer. Cyclist. Guitarist.
emailfacebooktwittermessengersteamxboxliveretroburn
Tags
2013 3d alphalabs amazon apple archivirtual asynchronous battlefield bad company 2 ben 10 bepu beta blackmagic design blog blue marble bootcamp borderlands bsp calibration charity charvel childsplay comments competition content tracker counter-strike crash csgo css3 cycling dear esther deferred deus ex develop conference direct x discipline documentation doom 3 bfg dpi dr bott eidos elite force email deliverability eurogamer expo facebook focus fresnel game development game horizon game republic gamedev games gaming geoip girls make games global offensive grid guitar half-life 2 hawken hd7 hobbyist htc humble indie bundle imac indie indie trials indietrials intensity pro ip-countryside iron man 3 jamulus rift jquery kids kinect launch conference left 4 dead live lost mac mac osx manchester manhacks mass effect 2 matrox maya minecraft mirrors edge montreal morrowind movies museum of the microstar music mxo2 mini mysql nausea network networking nokia normal mapping obj oculus rift omnitrix ouya pedal for pounds php physics playstation suite port25 portal portal 2 positron posters powermta project aedra project euler promotion properties proton pulse ps vita ps4 psn racer reddit rendering retroburn game studios reviews rift racer riftracer roadkill roller coaster sdl2 shadow racers sharks shoct skyrifters snds space cadet spam trap star trek steam stencyl storage super stock sd1 fr superhot team fortress 2 tesselating tesselation texture editor thunderbird thunderclap ticktock games tiga track builder track bulder trials tv twitter uk ultimatrix usergroup vequencer video vireio visual assist visual studio vorpx voucher vr vr cinema war thunder warren web willow windows 8 windows 8.1 windows phone 7 workbench wp7 wp7dev xbla xblig xblig network xbox xbox live indie games xna xnaukug xperia play zombies on the holodeck
Archive
Links
Web
XNA
Games
Email Deliverability
Thursday, September 29th 2011 / Web

IP to Country Tracking with ip-countryside

I've recently been looking into options of adding relevant advertisements to my site and ran a test of some Google AdSense ads, which supports displaying ads relevant to the visitor's country.

Unfortunately, AdSense did not do a terribly great job of optimizing the displayed ads for my site and so I've decided to take matters into my own hands and start adding Amazon ads to certain sections. By doing this I'm able to pre-select a range of products to be displayed when viewing particular pages of my websites (some code and a back end database went into this but it was a few hours work).

Unfortunately however, each of the international arms of Amazon has their own affiliate scheme, and so you can't use the same code to target US and UK visitors. Instead, you have to sign up with each one of these affiliate schemes and display the relevant ad code depending on the country the visitor is from (if you want to maximise clicks and potential sales).

To do this on a website, the usual method is to take the visiting IP address and track it back to the country of origin. In the past I have used MaxMind's GeoIP for this, but I Googled 'IP to Country' and the top link took me to a project on Google Code named ip-countryside. I clicked through and had a look and I like the streamlined method it uses to have decided to run with it. ip-countryside uses a database of IP ranges and the country associated with them from their RIR records (the master datbases which show you who owns certain IP addresses).

I downloaded ip-countryside and was pleased to see a demo PHP file in the distribution but was a little dismayed at the method by which it was performing lookups. The database comes as a 2.9MB text file and the PHP script literally reads the entire file into memory and then scan it to find a range into which the requested IP fits. This is highly inefficient and I decided I would import the flat file database into a MySQL database and write my own little lookup script:

 
CREATE TABLE  `db`.`ip-countryside` (
`start` INT UNSIGNED NOT NULL,
`end` INT UNSIGNED NOT NULL,
`country` VARCHAR( 2 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY ( `start` ),
INDEX ( `country` ),
UNIQUE ( `end` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

LOAD DATA LOCAL INFILE '/tmp/ip2country.db' 
INTO TABLE `db`.`ip-countryside` 
FIELDS TERMINATED BY ' ' 
LINES TERMINATED BY '\n';
 

I then wrote a very small PHP script which I can use in my site to detect the IP of the visitor and use the returned country code to help show the correct ads.

Using a MySQL database rather than a flat file sped up the lookups from an average of 0.5 seconds to 0.025 seconds per lookup. That's 20 times faster!

Here's the PHP file I use to do the lookup. Note that I store the identified country in a session variable, and on successive loads I just used the stored variable rather than repeatedly hitting the database:


<?
// lookup.php | ip-countryside lookup script (using MySQL)
// Written by Martin Caine, 2011-09-29 (martin@martincaine.com)
// ------------------------------------------------------------------ //

 // default if we can not identify the country
$ip_countryside 'US';

//check to see if we already have the country stored
if( isset( $_SESSION['country'] ) )
{
  
$ip_countryside $_SESSION['country'];
}
else
{
  
// check IP and convert it to an integer
  
$ip $_SERVER["REMOTE_ADDR"];
  list(
$a$b$c$d) = split("\.",$ip);
  if( ( 
is_numeric($a) && is_numeric($b) && is_numeric($c) && is_numeric($d) ) && 
      ( (
$a >= && $a <= 255) && 
        (
$b >= && $b <= 255) &&
        (
$c >= && $c <= 255) &&
        (
$d >= && $d <= 255) ) )
  {
    
$ipvalue = (int)$a*256*256*256 + (int)$b*256*256 + (int)$c*256 + (int)$d;

    
$sql 'SELECT `country` FROM `db`.`ip-countryside` 
            WHERE '
.$ipvalue.' BETWEEN `start` AND `end`';
    
$q mysql_query$sql );
    if( 
mysql_num_rows$q ) )
    {
      
$ip_countryside mysql_result$q);
    }
  }
}
$_SESSION['country']=$ip_countryside;

?>


I've also implemented a basic service using a slightly modified script which others can use (though I will monitor it and if it gets abused I reserve the right to remove the service). To perform a country lookup for a particular IP you simply have to pass the IP to the following URL and record the returned 2 letter country code:

http://martincaine.com/tools/ip-countryside/?ip=94.102.149.34

If for whatever reason the script can not identify the country from the given IP address the script will default to return the 2 letter code US. You can optionally pass a second argument to the script to specify the default which will be retuned if the IP address can not be found:

http://martincaine.com/tools/ip-countryside/?ip=244.102.149.34&default=DEFAULTVALUE

I hope this is of use to someone, I'm aware there are many such services on the internet but this was something I needed to implement for my own site anyway so thought I'd share it with the rest of the world :).


If you found this post helpful please leave a comment below:
 
Comments
Tags:   geoip   ip-countryside   php   web
0