How to Geolocate Visitors Using an IP-to-Country Database

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.

17 thoughts on “How to Geolocate Visitors Using an IP-to-Country Database

  1. Phil Gill

    A brilliant, well-written, CLEAR article. Thanks for this, just what I’m looking for. Like your website in general, real clear layout. Nice :)

  2. blogdar

    Really basic and clean post. I liked your post styles. You explain all things on article and everybody i guess got it. Keep it bro ;)

  3. Pingback: Ten Tips to Increase Conversion Rates « World of Usability

  4. Pingback: » Benutzer geographisch orten Flusensieb

  5. Nico Moenens

    Thank you very much for this great how-to.
    Very clear and informative!

    If I may suggest one update: it seems that the IPLong-numbers have become so long, that they cannot be contained in int(15)
    anymore, but require bigint(20).
    At least, this turned out to be the solution on my server when much of my IPLongs where stored as the number ‘2147483647’ regardless of what the actual longIP was.

    MySQL Version: FreeBSD port: mysql-server-5.0.67

  6. ardamis Post author

    While I haven’t tried it myself, it looks like the Google API offers a much easier way of geo-locating visitors.

    “When an application makes used of the AJAX API loader, the loader attempts to geo locate the client based on its IP address. If this process succeeds, the client’s location, scoped to the metro level, is made available in the google.loader.ClientLocation property. If the process fails to find a match, this property is set to null.”

    http://code.google.com/apis/ajax/documentation/#ClientLocation

  7. bypasser

    on ssh mysql command this is what I use:

    LOAD DATA LOCAL INFILE 'ip-to-country.csv'
    INTO TABLE ip2country
    FIELDS TERMINATED BY ","
    OPTIONALLY ENCLOSED BY """"
    LINES TERMINATED BY "n";

    ip2country.csv is at root directory.
    thanks for the code :)

  8. bypasser


    mysql>; LOAD DATA LOCAL INFILE 'ip-to-country.csv'
    -> INTO TABLE ip2country
    ->; FIELDS TERMINATED BY ","
    -> OPTIONALLY ENCLOSED BY """"
    -> LINES TERMINATED BY "n";
    Query OK, 87810 rows affected, 65535 warnings (0.18 sec)
    Records: 87810 Deleted: 0 Skipped: 0 Warnings: 116784

    hope it helps.

  9. Lobo

    Great tutorial! Thank you.

    hey I’m trying to use this script to redirect people to specific page if the come from CHINA and I found this script http://www.ip2location.com/articles/article2.htm . I follow your tutorial step by step and I try this is script but it seems that don’t work. Any ideas why? you have a forum/

  10. ardamis Post author

    Is the script giving you an error? I would need to know more about
    what’s happening before I could tell you what’s going wrong.

    But there’s actually a method for using Google’s Ajax API to identify the visitor’s
    location, down to the city level. This method is probably more accurate and useful, and does not require you to update a database.

    Read more at
    http://code.google.com/apis/ajax/documentation/#ClientLocation

    That’s what I would use instead of this PHP/MySQL method.

  11. Pix

    Great article. sorry if it s a lame Q – but do oyu have an example where eg .. different pricing is loaded into the same div according to country.. this would be a great little trick to know

  12. Gil

    so if IP isn’t in list example i go from localhost i get result ip not found etc. but on top of page i get error:

    Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:wampwwwipp.php on line 8

    8 line defines: $ccode_array = mysql_fetch_array($country_exec);

    any help ?! :/

  13. Claude "CodeAngry" Adrian

    Your solution is good but very slow. I think it rouns in tens of milliseconds as I see no indexes and it’s comparing unsigned longs (4 byte numbers) giving the comparison function quite a bit of work. Been there…

    Checkout http://ipcountryphp.com/ for a self-contained PHP script highly optimized for seeks. It won’t be a bottleneck for any website…

    Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>