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.

Filed under: Computing, Internet, PHP

Tagged with: , ,

Liked this page?

Tweet it

Subscribe to the RSS feed or sign up for the newsletter now.

Posts related to Where are your users from?

1 Comment to "Where are your users from?"

  1. Trackback on samantha on 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

Name (required)

Email (required)

Website

Leave a Reply

  • About Me


    photo of Samantha Halfon Samantha Halfon
    Software Engineer
    blueKiwi software
    Paris, France
    I enjoy playing with my computer(s), listening to Bob Dylan (and related artists) and watching movies (especially if they were directed by Martin Scorsese or John Cassavetes). Sometimes, I play a little guitar... If not doing any of the above, I am either riding a small red bike around Paris, or, making videos. About my videomaking please check out World Wide Angle and its blog.