In this post, I’ll illustrate how to use the IP-to-Country database available from http://ip-to-country.webhosting.info/ to identify the real-world geographic location of visitors to a web page (geolocate) based on their IP addresses. Once you know where a visitor is physically located, you can do all sorts of nifty things, such as send them location-aware content (think language, currency, etc.).

The IP-to-Country Database

There are a number of databases that associate IP address ranges with countries. I’ll be using the one at http://ip-to-country.webhosting.info/. It is available as a CSV file, so the first step will be getting the contents migrated into a MySQL database.

The MySQL Part

First, create a MySQL database. I like phpMyAdmin, but use whatever method you are comfortable with. Name the database ip2country.

Once the database has been created, we need to create a table. In phpMyAdmin, click on the SQL tab and enter the following lines:

CREATE TABLE `ip2country` (
  `ipFrom` int(15) NOT NULL default '0',
  `ipTo` int(15) NOT NULL default '0',
  `country2` char(2) NOT NULL default '',
  `country3` char(3) NOT NULL default '',
  `country` varchar(25) NOT NULL default ''
);

This creates a table, also called ip2country, and five fields (ipFrom, ipTo, country2, country3, and country) to hold the data from the CSV.

The next step is to get the contents of the CSV file into the MySQL database, and there are two ways to do this.

If you are using MySQL version 5.0 or greater, the fastest way is to use the LOAD DATA INFILE statement. The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. (For details, visit http://dev.mysql.com/doc/refman/5.0/en/load-data.html.) On my computer, it takes less than half a second to get all 79,000+ rows into the MySQL database. To use this method, click on the SQL tab and enter the following lines, changing the path to “ip-to-country.csv” to the actual path to the file on your local computer.

LOAD DATA INFILE 'D:/PATH/TO/FILE/ip-to-country.csv' INTO TABLE ip2country
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY """"
LINES TERMINATED BY "n";	

The other way to populate the MySQL database is by running a PHP script that writes the records from the CSV one line at a time. To use this method, upload the CSV to a directory on your web site, then create a PHP file with the following lines, editing the script to use your database address, username, and password:

<?php

	// Adjust for the database address, username, password 
	$link = mysql_connect('localhost', 'root', '');
	if (!$link) {
		die('Could not connect: ' . mysql_error());
	}

	$db = mysql_select_db("ip2country") or die(mysql_error());

	// Set the variable $row to zero to begin counting entries
	$row = 0;

	// The ip-to-country.csv must be in the same directory as this php file
	$handle = fopen("ip-to-country.csv", "r");

	// Required to prevent timeout
	set_time_limit(300);

	// While rows exist, write each into the database
	while ($data = fgetcsv($handle, 1000, ",")) {
		$query = "INSERT INTO ip2country(`ipFrom`, `ipTo`, `country2`, `country3`, `country`) VALUES('".$data[0]."', '".$data[1]."', '".$data[2]."', '".$data[3]."', '". addslashes($data[4]) ."')";
		$result = mysql_query($query) or die("Invalid query: " . mysql_error().__LINE__.__FILE__);
		$row++;
	}

	// Close the database connection
	fclose ($handle);

	// Print a confirmation
	echo "All done! " . $row . " rows added to database.";
	
?>

Upload the PHP script to the same directory as the CSV file and then browse to it. It will migrate the contents of the CSV file into the MySQL database and then give a little confirmation of how many rows were added when it is done.

If your script times out, you may need to either increase the value of set_time_limit(300); or move that part of the script inside the while loop.

The PHP Part

Now that the database is in place, it’s time to do something with it. The following PHP script will get the visitor’s IP address and print out the corresponding country.

<?php

	// Figure out the visitor's IP address
	$ip = $_SERVER['REMOTE_ADDR'];

	// Establish a database connection (adjust address, username, and password)
	$dbh = mysql_connect("localhost", "root", "") or die("Could not connect: " . mysql_error());
	mysql_select_db("ip2country");

	// Create a query string
	$country_query = "SELECT country2, country FROM ip2country WHERE ipFrom<=INET_ATON('" . $ip . "') AND ipTo>=INET_ATON('" . $ip . "')";
		  
	// Execute the query
	$country_exec = mysql_query($country_query);

	// Fetch the record set into an array
	$ccode_array = mysql_fetch_array($country_exec);

	// Close the database connection
	mysql_close($dbh);

	// Get the country code from the array and save it as a variable
	$country_code = $ccode_array['country2'];

	// Get the country name from the array and save it as a variable
	$country_name = $ccode_array['country'];
	
	// If the database contains a match, print out the country name and country code, otherwise print the IP address
	if ($country_code != "") { 
	echo '<p>The IP-to-Country database contains a match for your ip address: ' . $ip . '</p>';
	echo '<p>You are located in ' . $country_name . ', and the country code is ' . $country_code . '</p>';
	}else{
	echo '<p>Sorry. The IP-to-Country database does not contain a match for your ip address: ' . $ip . '</p>';
	}

?>

Summary

That’s it. You now have a way to determine each visitor’s physical location. Use geolocation carefully, and always provide a fall-back in the event the database does not contain a given IP. IP addresses are constantly being assigned and revoked, so keeping your database up-to-date is critical.