How To Make An IP-To-Country Tool With PHP and MySQL

Jun 16 2008

When it comes to creating web applications, there is no guarantee where your audience will hail from. You might be in sunny California, but in today’s global community it is almost certain that an English-language application will attract visitors from Canada, the UK, Australia, and probably many other countries as well.

With that in mind, it would be nice to offer your users location-sensitive content. This is true whether your app is built around country-specific features (a shopping site, for example) or simply for pre-filling the country and timezone dropdowns on your registration form.

In this tutorial, we will create a database that allows us to map an IP address to a country, and use that information to serve up location-specific content.

Creating the database

Your database structure will depend, in part, on the data you intend to use to populate it. I am using the free IP to Country database from Webnet77 so my structure is based on that file, but most (if not all) ip-to-country datasets contain the following fields:

In your chosen MySQL database, create the table, mapping fields to data columns in the order in which they appear:

  1. CREATE TABLE `iptocountry` (
  2. `lower_bound` int(11) unsigned NOT NULL default '0',
  3. `upper_bound` int(11) unsigned NOT NULL default '0',
  4. `owner` varchar(20) NOT NULL default '',
  5. `last_updated` int(11) default NULL,
  6. `two_char_code` char(2) NOT NULL default '',
  7. `three_char_code` char(3) NOT NULL default '',
  8. `country` varchar(100) NOT NULL default ''
  9. );

Importing the data

The raw data is in the form of a 6MB .csv file containing over 80,000 lines of data. What we will do is upload it to our site and create a PHP script to read the file into the database, one line at a time.

With such a large dataset, it’s a good idea to spit out regular status messages to make sure our script is behaving—it’s better than waiting for some sort of response and not being sure whether it’s worked or not. Normally, PHP will only write content to the page when the entire script has finished, but by using the flush() function we can force the output buffer to print any output line-by-line. I use a handy wrapper function to accomplish this:

  1. function status($str)
  2. {
  3. echo $str . '<br>';
  4. flush();
  5. }

Onto the script. We’ll upload it to the same folder as the .csv data file. First we need to connect to our MySQL database:

  1. $db = mysql_connect('localhost', 'root', 'root') or die('Could not connect to database');
  2. mysql_select_db('iptocountry', $db) or die('Could not select database');
  3. status('Connected to database successfully');

Next we open the data file for reading, using PHP’s fopen() function:

  1. $fh = fopen('./iptocountry.csv', 'r');
  2. status('Opened csv file for reading');

We need to loop through the data file one line at a time (ignoring any commented lines). Each line needs to be stripped of the quotes that wrap each field; split into its component parts (separated by commas); and then inserted into the table. We’ll print the MySQL query used, and also any error messages thrown.

  1. while (!feof($fh))
  2. {
  3. $line = str_replace('"', '', fgets($fh));
  4. if (substr($line, 0, 1) != '#') // Ignore commented lines
  5. {
  6. $arr = explode(",", $line);
  7. $sql = "INSERT INTO iptocountry VALUES ('" . implode("','", $arr) . "')";
  8. mysql_query($sql);
  9. status($sql);
  10. if(mysql_error())
  11. {
  12. status(mysql_error());
  13. }
  14. }
  15. }

The handy fgets() function reads through the file returning a single line each iteration. If the line doesn’t start with a #, we explode() it around the separating commas, and insert it into the database using implode() to recreate the query. (Note that the query assumes that the data appears in the same order as our fields—if you are using a different data source than me, make sure your data and table structure match up.)

Finally (and because we’re responsible developers) we close the two connections in use:

  1. fclose($fh);
  2. mysql_close($db);

Running the script should generate a huge page of status messages as each record is inserted into the database.

Using the data—where am I

So now we have a database that can translate an IP address into a country—how do we use it?

Your site visitor’s IP address can be retrieved from the $_SERVER collection:

  1. $ip = $_SERVER['REMOTE_ADDR'];

To translate the four-part IP address into a single numeric value, you need to convert the a.b.c.d format into d + (256 * c) + (256 * 256 * b) + (256 * 256 * 256 * a). We can do this by splitting the IP address on the period:

  1. $parts = explode('.', $ip);
  2. $numeric_ip = $parts[3] + (256 * $parts[2]) + (256 * 256 * $parts[1]) + (256 * 256 * 256 * $parts[0]);

Now all that remains is to query the database for the row where $numeric_ip is between the lower and upper bounds:

  1. $db = mysql_connect('localhost', 'root', 'root') or die('Could not connect to database');
  2. mysql_select_db('iptocountry', $db) or die('Could not select database');
  3. $sql = "SELECT two_char_code FROM iptocountry WHERE lower_bound <= $numeric_ip AND upper_bound >= $numeric_ip LIMIT 1";
  4. $result = mysql_query($sql);
  5. $row = mysql_result($result, 0);

The variable $row now contains the two-character country code of your user.

Potential applications

There are plenty of ways that this information can now be used. Apart from the aforementioned pre-populating of country dropdowns, you could also: