MortenCB.CX : IP to Country :

IP to Country

On my pages I have a script the detects which country you are logged on from base on your IP. It checks your IP in a database with around 56000 entries of ranges that has been dealt out to countries, and determines which country you're in.

I will try to show you here how it works:

First you will have to download the database in CSV format from this website:
ip-to-country.webhosting.info

Then you will have to import it into your MySQL server. Create a new table called the_table (or whatever else) and make the proper 5 fields in it. Here is some SQL-code:

CREATE TABLE `the_database` (
  `IP_FROM` double NOT NULL default '0',
  `IP_TO` double NOT NULL default '0',
  `COUNTRY_CODE2` char(2) NOT NULL default '',
  `COUNTRY_CODE3` char(3) NOT NULL default '',
  `COUNTRY_NAME` varchar(50) NOT NULL default ''
) TYPE=MyISAM;

To import the data into this table, you could run this SQL-code to get it in from your CSV-file:

LOAD DATA 
   INFILE '/tmp/ip.csv' 
   INTO TABLE the_database 
   FIELDS TERMINATED BY ',' 
   ENCLOSED BY '"' 
   LINES TERMINATED BY '\r\n';

This should put all the data in the CSV-file nicely into your table you created above. Of course exchange /tmp/ip-csv with your own path to the file you downloaded.

This could of course be used directly each time, but that would be a little inefficient, so the people over at php.net came up with a smart thingy. To use their way of searching, we need the data to be in file in a format of <IP_FROM><IP_TO><COUNTRY_CODE3> one record pr line (and all IP's should be 10 characters, filled with 0's on the left if too short). Thiswould look something like this:

00339963440033996351GBR
00503316480083886079USA
00945854240094585439SWE
01006632960121195295USA

To do this, we can use the following SQL-code to export it to /tmp/ip-to-country.db:

SELECT CONCAT(
      LPAD(IP_FROM,10,'0'), 
      LPAD(IP_TO,10,'0'), 
      COUNTRY_CODE3) 
   INTO OUTFILE '/tmp/ip-to-country.db' 
   FIELDS TERMINATED BY '' 
   LINES TERMINATED BY '\n' 
   FROM `the_database` 
   WHERE 1;

This file needs to be indexed so we can search in it faster, which is done with the following code taken from php.net. You can find the original code yourself at this URL:
http://cvs.php.net/cvs.php/phpweb/include/ip-to-country.inc.
I have modified it some, and here is the code to make the index: country_app1.phps.

Ok, now we have the db and idx-files. What to do with these? You use the code php.net uses to distribute the traffic to a mirror near you, but change it some to drop the cookie-stuff. This is so quick anyway, no need to save it in a cookie.

The code I use can be seen here: country_app2.phps. To use it, simply include the file and call the function i2c_go(). That will create a global variable called $COUNTRY which will be the 3 character country code (ISO 3166).

Now we know the three-letter country-code, and could use the MySQL server to check the name of the country, or get the two-letter country-code. I want to show the flags, and I have downloaded a collection of flags for all the countries in the world from this website:
http://www.hahn-hotel.com/flags/
These flags are free to use by anyone, and are practically named with the two-letter country codes. To avoid using MySQL at all I have created a file with all the country-codes as three letters as keys and two letters as values. I used this SQL-code to make the file:

CREATE TEMPORARY TABLE temp 
   SELECT DISTINCT(COUNTRY_CODE3) as C2,
   COUNTRY_CODE2 as C3 
   FROM the_database;
SELECT CONCAT('"',C3,'" => "',C2,'" ,') 
   INTO OUTFILE '/tmp/country.db' 
   FIELDS TERMINATED BY '' 
   LINES TERMINATED BY '\n' 
   FROM `temp` 
   WHERE 1;

Then I put the following at the top:


<? $COUNTRIES = array(

and the following at the end of the file:


);?>

This makes a file something like this:

<?
$COUNTRIES = array(
"GB" => "GBR" ,
"US" => "USA" ,
...
"AG" => "ATG" ,
"DM" => "DMA" );
?>

I then made a function to get the two-character countrycode from the three-character one and vice verca:


<?
function convert_country_thingy($country) {
   require("country.db");
   if(strlen($country) == 3) $COUNTRIES = array_flip($COUNTRIES);
   if(isset($COUNTRIES[$country])) return $COUNTRIES[$country];
   else return "NA";
}
?>

I then write out the flag you can see at the bottom like this:


<? 
require("country_app2.phps");
require("country_app3.phps");
i2c_go();
if($COUNTRY != "NA") {
   echo "You are logged on from: ".
      "<A HREF=\"http://mortencb.cx/php/country.php\">".
      "<IMG SRC=\"/pics/flags/world 25x15px/".
      strtolower(convert_country_thingy($COUNTRY)).
      ".png\" ALT=\"$COUNTRY\"></A>";
}
else echo "You are from a country unknown to the IP-database.";
?>


	Last updated: 20:44 17.06.2004 by webmaster@mortencb.cx You are from a country unknown to the IP-database. Comments to this page.

Menu

IP to Country