<

IP Address to Country in PHP

Published on
27,646 Points
7,446 Views
12 Endorsements
Last Modified:
Fore-Foreword
Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API.  A paid subscription may be required for your application.  You can consider this article to offer some guidelines, but not a direct solution.

Foreword
This article was written many years ago, before the PHP GeoIP Extension was ready for prime time.  Today (2014) you might want to check the PHP GeoIP Extension documentation for a more up-to-date view of the issues.

Introduction
An Internet Protocol address (IP address) is a unique numeric value assigned to each device on the Internet.  While we commonly know web sites by a domain name, the internet routing machinery translates the domain name into the numerical IP addresses to send message packets.  When a client visits your web site, you can detect the client's IP address.  Because of the way IP addresses are allocated in contiguous blocks of numbers, you can learn interesting information about the client once you know the IP address.  This article teaches how to get the client's country information by looking up the client's IP address.

The IP address can usually be found in $_SERVER["REMOTE_ADDR"].  In most PHP configurations this is reliable, but please see the man page notes about relying solely on the values in $_SERVER.
http://php.net/manual/en/reserved.variables.server.php

This reference may be useful, too.
http://php.net/manual/en/function.gethostbyname.php

Acquire the IP-to-Country Data
Our first step is to acquire a data base of IP address ranges and country identifications.  You can download a CSV file with this information from MaxMind here:
http://geolite.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip  
It will look something like this:

12.109.8.112,  12.109.8.119,    208472176,  208472183,  PR,  Puerto Rico
12.109.8.120,  12.129.198.255,  208472184,  209831679,  US,  United States
12.129.199.0,  12.129.199.31,   209831680,  209831711,  DE,  Germany

The first two fields are IP string ranges, also called the IP address.  The next two fields are the corresponding IP numbers.  The last two fields are the country abbreviation and the country name.  In April, 2013 the file had a bit less than 170,000 rows.  The file will be about 12MB after it is unzipped.

Unzip the file and store it on your server in the web directory - let's call it 'GeoIPCountryWhois.csv' for now.  You might attach the current date to the file name, in case you are interested in keeping track of versions.  MaxMind issues a new release of the file every month.

Load the IP-to-Country Data into a Table
The second step is to load the data base using the contents of the CSV file.

Run the "LOAD" script from the same directory as the 'GeoIPCountryWhois.csv' data.  On my server it took about 15 seconds to run.  As you look over the code, you can see that this script does the following things.

1. We set a variable with the path to the file we downloaded from MaxMind - that is our source of input data (line 12).
2. We connect and select our data base (lines 15 - 34).
3. We discard any old version of the "ip2country" table (line 38).
4. We create a new table definition (lines 41 - 64).  Experienced data base users will notice that we have no index definitions in our CREATE TABLE statement.  Indexes improve performance during SELECT queries but are of no value (and may even slow down) INSERT statements, so we create the table without indexes, and we add them later after the data has been loaded.
5. We open and read the CSV file, performing some modest edits, and use INSERT to add each record to the "ip2country" table (lines 67 - 114).
6. Finally, we add the indexes to the table on the ip_number columns (lines 117 - 118).  This will make table lookups very fast.

 
<?php // LOAD
error_reporting(E_ALL);


// DEMONSTRATE HOW TO LOAD THE IP-TO-COUNTRY DATA FROM MAXMIND GEOLITE
// http://geolite.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip
// ON   JULY 1, 2010 THE FILE HAD 129,763 ROWS
// ON APRIL 25, 2013 THE FILE HAD 167,637 ROWS


// PUT THE PATH/NAME OF THE DOWNLOADED CSV FILE HERE
$csv = 'path/to/GeoIPCountryWhois.csv';


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// REMOVE THE OLD DATA BASE TABLE, IF ANY (IGNORE ERRORS)
$mysqli->query('DROP TABLE ip2country');


// CREATE THE NEW TABLE
$sql
=
" CREATE TABLE ip2country
( ip_string_lo VARCHAR(16)  NOT NULL
, ip_string_hi VARCHAR(16)  NOT NULL
, ip_number_lo INT UNSIGNED NOT NULL
, ip_number_hi INT UNSIGNED NOT NULL
, country_code VARCHAR(2)   NOT NULL
, country_name VARCHAR(64)  NOT NULL
)
"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = "QUERY FAIL: $sql "
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}


// OPEN THE CSV FILE AND LOAD THE TABLE
$fp = fopen($csv, 'r');
if (!$fp) trigger_error("FAIL: UNABLE TO OPEN $csv". E_USER_ERROR);

// READ EACH ROW OF THE IP AND COUNTRY INFORMATION
$kount = 0;
while (!feof($fp))
{
    $row = fgetcsv($fp);

    // IGNORE ERRORS OR EMPTY RECORDS
    if (!$row) continue;

    // COUNT VALID RECORDS
    $kount++;

    // IF COUNTRY IS LIKE Korea, Republic of - RECONSTRUCT THE NAME
    if (strpos($row[5], ','))
    {
        $x = explode(',', $row[5]);
        $row[5] = trim($x[1]) . ' ' . $x[0];
    }

    // ESCAPE THE DATA FOR SAFE USE IN THE QUERY
    foreach ($row as $key => $val) { $row[$key] = $mysqli->real_escape_string($val); }

    // CONSTRUCT AND RUN A QUERY TO INSERT THE DATA
    $sql
    =
    "
    INSERT INTO ip2country
    ( ip_string_lo, ip_string_hi, ip_number_lo, ip_number_hi, country_code, country_name ) VALUES
    ( '$row[0]',    '$row[1]',    '$row[2]',    '$row[3]',    '$row[4]',    '$row[5]'    )
    "
    ;
    if (!$res = $mysqli->query($sql))
	{
	    $err
	    = "QUERY FAIL: $sql "
	    . ' ERRNO: '
	    . $mysqli->errno
	    . ' ERROR: '
	    . $mysqli->error
	    ;
	    trigger_error($err, E_USER_ERROR);
	}
}
echo "IP-TO-COUNTRY TABLE LOADED WITH " . number_format($kount) . " RECORDS<br/>";

// ALTER THE TABLE TO ADD INDEXES
$mysqli->query("ALTER TABLE `ip2country` ADD INDEX ( `ip_number_lo` )" );
$mysqli->query("ALTER TABLE `ip2country` ADD INDEX ( `ip_number_hi` )" );

// ALL DONE.
die("INDEXING COMPLETE");

Open in new window


Create a "RESTful" Web Service
Now that our table is loaded and indexed for fast performance, we can look up IP addresses and return the country data.  We will demonstrate this ability by creating a RESTful web service.  REST services use the GET string (URL) to communicate with the service.  The input argument is the IP address and the response is a short CSV string with the country code and country name.  We could modify this service to return XML or JSON, but simplicity can be a virtue.  

Our REST service connects to the data base so it can use the table we created in the earlier step.  You may notice that the error messages are very terse - since this is a service that is designed to be simple to use, we only return one string of data.  If the string starts with the word, "ERROR" it indicates a failure.  Otherwise the string will have two fields separated by a comma: The country code and the country name.  For example, a visitor from "down under" would be identified with a CSV string that said "AU,Australia"

You can integrate this service into your web applications with a single line of code that sends the IP address to the URL of the REST script.  Here are some sample calls to the service:

// OUTPUT THE CLIENT'S COUNTRY
echo file_get_contents("path/to/service.php");

// OUTPUT THE COUNTRY THAT HAS IP#123.45.67.89
echo file_get_contents("path/to/service.php?ip=123.45.67.89");

// OUTPUT AN ERROR MESSAGE
echo file_get_contents("path/to/service.php?ip=BOGUS");
 
<?php // REST
error_reporting(E_ALL);


// DEMONSTRATE HOW TO USE THE IP-TO-COUNTRY DATA FROM MAXMIND GEOLITE
// A RESTful SERVICE TO LOOK UP THE COUNTRY INFORMATION FOR A GIVEN IP ADDRESS
// http://www.maxmind.com/app/geoip_country
// ON   JULY 1, 2010 THE FILE HAD 129,763 ROWS
// ON APRIL 25, 2013 THE FILE HAD 167,637 ROWS


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// IF NO IP ADDRESS IS GIVEN, USE THE REMOTE ADDRESS OF THE CLIENT
$ip = empty($_GET["ip"]) ? $_SERVER["REMOTE_ADDR"] : $_GET["ip"];

// CONVERT TO BINARY - SEE http://php.net/manual/en/function.ip2long.php
$x = ip2long($ip);
if ($x === FALSE) die('ERROR: INVALID IP ADDRESS');

// IP ADDRESSES ARE UNSIGNED AND MAY RETURN NEGATIVE VALUES
$ip_number = sprintf('%u', $x);

// QUERY FOR THE BINARY VALUE
$sql
=
"
SELECT  country_code, country_name
FROM    ip2country
WHERE  $ip_number
BETWEEN ip_number_lo
AND     ip_number_hi
LIMIT 1
"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = "QUERY FAIL: $sql "
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// DID WE FIND ANYTHING?
$num = $res->num_rows;
if (!$num) die("ERROR: NO DATA FOUND");

// YES, WE FOUND THE COUNTRY
$row = $res->fetch_object();
die("$row->country_code,$row->country_name");

Open in new window


Using the IP-to-Country to Deny/Allow Access
You might be publishing information on your site that is appropriate only for certain nationalities.  Perhaps you sell a service that is only useful in English-speaking countries, or something like that.  You can use this technology to restrict access to the value-limited pages.  We can do this with an array of country codes (lines 35 - 40).
 
<?php // DENY/ALLOW
error_reporting(E_ALL);


// DEMONSTRATE HOW TO USE THE IP-TO-COUNTRY DATA FROM MAXMIND GEOLITE
// DENY/ALLOW WEB PAGE ACCESS BASED ON THE COUNTRY INFORMATION FOR A GIVEN IP ADDRESS
// http://www.maxmind.com/app/geoip_country
// ON   JULY 1, 2010 THE FILE HAD 129,763 ROWS
// ON APRIL 25, 2013 THE FILE HAD 167,637 ROWS


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// AN ARRAY OF COUNTRY CODES
$countries = array
( 'US'
, 'CA'
, 'AU'
, 'NZ'
, 'GB'
)
;

// CONVERT IP TO BINARY - SEE http://php.net/manual/en/function.ip2long.php
$x = ip2long($_SERVER["REMOTE_ADDR"]);
if ($x === FALSE) die('ERROR: INVALID IP ADDRESS');

// IP ADDRESSES ARE UNSIGNED AND MAY RETURN NEGATIVE VALUES
$ip_number = sprintf('%u', $x);


// QUERY FOR THE BINARY VALUE
$sql
=
"
SELECT  country_code
FROM    ip2country
WHERE  $ip_number
BETWEEN ip_number_lo
AND     ip_number_hi
LIMIT 1
"
;
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}


// DID WE FIND ANYTHING?
$num = $res->num_rows;
if ($num)
{
    // YES, WE FOUND THE COUNTRY
    $row = $res->fetch_object();
    if (in_array($row->country_code, $countries))
    {
        echo "YOU ARE FROM <b>$row->country_code</b> SO YOU CAN SEE THIS PAGE";
    }
    else
    {
        die("YOU CANNOT SEE THIS PAGE");
    }
}
else
{
    die("YOU CANNOT SEE THIS PAGE");
}

Open in new window


Putting the Application to Work
Now that you know how to find the client's country, you can do a number of interesting things with the information.

You might keep a record of where your site visitors come from.  Perhaps you could create a map of the world showing the countries that have visitors to your site.  (Google does this with its Analytics service).  

You could consider localization options, such as adding a national flag to the page banner, or suggesting a second language for the web site text.

You might be able to access specialized RSS feeds for weather or news based on the name or country code of your visitor's country.

You could provide links to Wikipedia articles in the appropriate language, for example (in Germany):
http://de.wikipedia.org/wiki/

You could even provide a link to the CIA fact book for that country!

Please give us your feedback!
If you found this article helpful, please click the "thumb's up" button below. Doing so lets the E-E community know what is valuable for E-E members and helps provide direction for future articles.  If you have questions or comments, please add them.  Thanks!
 
12
Comment
Author:Ray Paseur
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 12

Expert Comment

by:Mohamed Abowarda
Great article! very helpful, Thanks!
0
 
LVL 23

Expert Comment

by:Saqib Khan
This is great article.... but you cannot rely on GeoLite Database.... it is really outdated. i tried it with many tests and it was returning WRONG Country/Region. Best solution is to buy GeoCountry Database, pay them monthly fee for updates and use their COM object to intrtect with their .DAT database to return correct Geo Data.
0
 
LVL 110

Author Comment

by:Ray Paseur
@adilkhan: To quote http://www.maxmind.com/app/geoip_country, "GeoLite Country is similar to the GeoIP Country database, but is slightly less accurate. Should you require greater accuracy, GeoIP Country is a drop-in replacement for GeoLite Country."

And to quote http://www.maxmind.com/app/country, "$50 site license fee and $12 per month of updates."

I have never seen GeoLite return the wrong country, but if greater accuracy is an important goal, then the paid version is probably a good value.  As is true with most things, you only get what you pay for!

Since this article was written in mid-2010, I have found another service that I have used with satisfactory results: http://www.geoio.com/
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 23

Expert Comment

by:Saqib Khan
Geoio looks like an API solution? it seems like they promise to provide 99.9% uptime? how is your experience with them so far?

Thanks
0
 
LVL 110

Author Comment

by:Ray Paseur
So far so good with geoio.  My use case is very light - a private school baseball program with a world-wide audience of perhaps 200 ;-)
0
 
LVL 7

Expert Comment

by:Vimal DM
That's fantastic,let use this when i am in need of.
0
 

Expert Comment

by:hung3r4more
Great article, everything is explained perfectly. It really helped me a lot.
Thank you.
0

Featured Post

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Join & Write a Comment

The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month