However one looks at it, the internet is an amazing piece of technology. It allows exposure to your products and services from people all over the world. However, if you're getting international visitors and you're not serving them with the correct content then you're potentially losing-out on a large number of sales. We've all seen the seamless way that large sites such as Google seem to 'know' which territory a surfer is coming from and then delivers content appropriately. The truth, however, is that even though there is some cleverness behind the approach there's no magical all-knowing code lying in the background. With some fairly simple perl code and some FTP data downloads you too can replicate this functionality for your own website. Don't believe me? Well, in this article I'll show you exactly what you need to do to gain this functionality for your own web pages. The basic process involved is to glean your users' IP address (addresses of the form 127.1.1.0) which tells you what server they're using to access the internet. The example given on the left represents a dot-decimal notation which comprises four octets in decimal separated by periods. Everyone is familiar with this notation, however it's not very useful if you need to find a number within an IP range (the repositories provide us with IP ranges rather than a list of numbers). To convert from decimal-coded octets to a single decimal number the following process is used (eg. for an IP 1.2.3.4) 1.2.3.4 = 4 + (3 * 256) + (2 * 256 * 256) + (1 * 256 * 256 * 256). The numbers are split and reversed then each is multiplied by 256^n with n in the range 0 to 3. This results in a pure decimal number. OK, so you can get your site visitors' IP addresses and convert them to decimal notation (which aids comparisons), but how can you convert these to the country of origin for your visitors? Thankfully for us, the actual allocation of IP addresses so- called Regional Internet Registries (RIRs) which are RIPE NCC (Europe), ARIN (North Americ and part of the Caribbean), LACNIC (Latin America and Caribbean), APNIC (Asia Pacific) and AfrINIC (Africa). Each of these publish a daily list of all the registry data and they make these data available via anonymous FTP. To find them simply navigate to the ARIN home page (which you can find via any search engine) and look for the list of RIRs on the left-hand border. These link you out to the respective FTP sites. Each is a standard repository where the latest version of the data file is available as delegated-*-latest so it's easy to write an automated script that will download the files on a daily basis. Once you've grabbed the files from each datacentre they will all have the format (eg from the European data file): ripencc|GR|ipv4|62.1.0.0|65536|20000216|allocated ripencc|CH|ipv4|62.2.0.0|65536|19981211|allocated ripencc|SA|ipv4|62.3.0.0|8192|20000721|allocated ripencc|SA|ipv4|62.3.32.0|8192|20020109|allocated ripencc|GB|ipv4|62.3.64.0|16384|20010629|allocated ripencc|SE|ipv4|62.3.128.0|8192|20001005|allocated ripencc|PL|ipv4|62.3.160.0|8192|20020114|allocated ripencc|GB|ipv4|62.3.192.0|16384|20030212|allocated ripencc|FR|ipv4|62.4.0.0|8192|19970513|allocated As you can see from the example above the data format is relatively simple and easy to parse as the data is delimited by the pipe "|" character. COLUMNVALUES ----------------------------------------------------------------- REGISTRY:apnic,arin,ripencc,lacnic,iana COUNTRY_CODE:One of 240 unique 2-character country codes or "*" or "ZZ" (unassigned) ADDRESS_TYPE:asn,ipv4,ipv6 ADDRESS:Either the starting IP Address or AS Number or "*" NUMBER:Number of IPs in range or "1" if ADDRESS_TYPE is "asn" DATE:Date IP range or AS Number was added to database or "*" RANGE_TYPE:"allocated" -> borrowed; "assigned" -> owned
Only the ipv4 lines are needed, so the others can be discarded, as can any lines with wildcard '*' characters in them. This reduces the file size to about 2.4 MB and about 750 000 lines. Generally I load this into a database and use the ISO 3166 country code definitions (search for this on the web) to build another database that converts the two-letter codes in the file above to full country names (the only exception to this is GB which is the ipv4 code for the United Kingdom whereas the ISO 3166 code is UK). I fix this by appending 'GB United Kingdom' to the end of the code_to_name database I create. Once all the data has been loaded into a database and the IP numbers have been converted to decimal ranges I can start to do something useful. As a simple example here's an SQL query that fetches some data across the two databases I've created: select cc.country, cc.code, ip.registry, ip.ip_from, ip.ip_to from country_codes cc, ip_maps ip where cc.code = ip.code and ip.code = "GL"; and returns the following data: +-----------+------+----------+------------+------------+ | country | code | registry | ip_from | ip_to | +-----------+------+----------+------------+------------+ | GREENLAND | GL | ripencc | 1481834496 | 1481842687 | | GREENLAND | GL | ripencc | 3266437120 | 3266445311 | +-----------+------+----------+------------+------------+ 2 rows in set (0.05 sec) This is a very simple query, but the code given below shows you how to use PHP to do something useful with the databases: #first the settings to link to the database$user="foo"; $pwd="bar"; $host="my.mysql.host"; $database="ipv4_db"; #now connect to the database mysql_connect($host,$user,$pwd); @mysql_select_db($database) or die( "Unable to select database"); $add; #fetch the incoming IP address and perform some checks to make sure #that this is valid if (getenv("HTTP_CLIENT_IP")) $add = getenv("HTTP_CLIENT_IP"); else if(getenv("HTTP_X_FORWARDED_FOR")) $add = getenv ("HTTP_X_FORWARDED_FOR"); else if(getenv("REMOTE_ADDR")) $add = getenv("REMOTE_ADDR"); else $add = "UNKNOWN"; #now check that the IP address is valid $cmp = strcmp($add,"UNKNOWN"); #if the IP address is valid if ($cmp != 0) { #convert the octets into useful decimal $ip = sprintf("%u", ip2long($add)); #now create the query. Here we're checking to find which IP range the incoming IP #address belongs to $query = "SELECT cc.country as ctry, cc.code as tlc, ip.registry as rgst from country_codes cc, ip_maps ip where cc.code = ip.code and ".$ip." >= ip_from and ".$ip." <= ip_to"; $result = mysql_query($query) or die('Error, query failed'); while($row = mysql_fetch_array($result)) { echo "Your IP address: ".$add." originates in ".$result[ctry].", code: ".$result[tlc]." from registry ". $result[rgst]."
"; } } else { #something's wrong with the IP address it's either not valid or is being spoofed echo "Your stated IP address is invalid... Are you trying to spoof me?"; } The code above fetches the IP address of a visitor (after doing some checking to make sure that it's valid) and then grabs the country representing that IP address. It even warns the user of an invalid IP address that they're probably trying to spoof you. This is basically all you need. Once you know your visitors' country of origin you can then do useful things like display tailored advertisements or automagically direct them to a relevant Amazon link page or an affiliate link page. A full description of how to implement this functionality can be found here: http:// www.celtnet.org.uk/info/IP-to-country-converter.php. This page also gives a demonstration of converting an IP address into a country name and a country map. Basically, once you have the core functionality in place you can amend and improve the code as you see fit. You are limited only by your own imagination (and coding skills). For a further example of how the code is used in practice, have a look at my eBay Misspelling Tool: http://www.celtnet.org.uk/ auctions/synosearchresults.php which uses IP to country conversion to automatically chose the appropriate country of origin for a visitor. Remember, Internationalization is one of the buzzwords of the internet at the moment and the more comfortable you can make a surfer feel with your pages the more likely they are to trust you and to buy from you. |
