Jan

21

Where are your users from?

January 21, 2010 | 1 Comment

If you ever wonder where your readers are from, if you ever need to adjust your application to the origins of its users; you can determine the geographic location of a connection by using an IP to Country table.

I should start by saying that I do not always like the way websites use this feature. As much as I appreciate, let’s say, Yamaha, to redirect me from www.yamaha.com to fr.yamaha.com based on my location ; I can not stand for Google or Wikipedia to assume that I either speak French or intend to search French speaking contents.

Another thing I absolutely can not stand is of Skype (for example) to prompt me to download the French version of its application just because I am browsing the Web from France. I can not tolerate any French speaking applications to be installed on my computer. Actually, Mac OS X is such a treat on that point because I can have a French bought CD of Snow Leopard and still install my system in English. All the OEM Windows systems I purchased in my country of residence were bound to remain in (poorly-translated) French. I actually would always keep my license number handy but use a pirated English (but otherwise equivalent) version just so that my Start button would actually spell ‘Start’.

Anyway, the IP to Country determination can still come in handy and here’s how I made it work for me (using PHP/MySQL). I am aware that it is possible to do the www.yamaha.com to fr.yamaha.com redirection simply using geolocated aliases. Hosting providers such as OVH usually provide this functionality.

  1. Download the IP to Country.csv database (and update it there after) from http://ip-to-country.webhosting.info/
  2. Create a table to hold the data
  3. Import the data from the csv file into that table
1
2
mysql> CREATE TABLE iptocountry (ip_from int(4), ip_to int(4), country_code2 char(2), country_code3 char(3), country_name varchar(50));
mysql> LOAD DATA INFILE 'ip-to-country.csv' INTO TABLE iptocountry FIELDS terminated BY ',' ENCLOSED BY '"' LINES terminated BY '\n';

Then, from your PHP code, you can get the country code for example, using the code below

1
2
3
4
5
6
7
8
9
10
11
12
13
14
function _ipAddressToIpValue($ip) {
    if ($ip == "") {
        return 0;
    }
    $ips = split("\.", $ip);
    return $ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256;
}

$ipValue = $this->_ipAddressToIpValue($_SERVER["REMOTE_ADDR"]);
$queryResults = $this->db->query("SELECT country_name FROM iptocountry WHERE ".$ipValue." BETWEEN ip_from AND ip_to");
$res = $queryResults->result_array();
if (count($res)) {
    $countryCode = $res[0]["country_name"];
}

The code above is using Code Igniter to query the database. I suppose it is self explanatory enough to be transposed to any other DAO you might be using.


Comments

1 Comment so far

  1. samantha says:
    January 21, 2010 at 10:28 pm

    New blog post: Where are your users from?: If you ever wonder where your readers are from, if you ever need to adj… http://bit.ly/6cj7jq

    Reply

Name (required)

Email (required)

Website

Leave a Reply