Importing IP2Location data into MongoDB and querying with PHP

The aim of this guide is to demonstrate how to import IP2Location data (DB11) in csv form into MongoDB and then query the data in a PHP web page.

First of all, you will need to download the IP2Location DB11 csv file.
Download Free LITE version at http://lite.ip2location.com/database-ip-country-region-city-latitude-longitude-zipcode-timezone
Download commercial version at http://ip2location.com/download?code=DB11CSV

Extract out the IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.CSV file from the downloaded zipped file.

Important Note

We will not cover installation of either MongoDB or PHP in this guide. We will assume you have already setup MongoDB and are using PHP via Apache on a Linux or Windows platform.

Importing the csv data into MongoDB

Linux
At the command prompt, run the following command:
mongoimport --drop --db ip2location --collection db11 --type csv --file "/var/www/TestMongoPHP/IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.CSV" --fields ip_from,ip_to,country_code,country_name,region_name,city_name,latitude,longitude,zip_code,time_zone

My csv file folder is /var/www/TestMongoPHP while your folder may be different so remember to change to the folder where you have extracted the csv file earlier.

Windows
At the command prompt, run the following command:
C:\mongodb\bin\mongoimport --drop --db ip2location --collection db11 --type csv --file "C:\inetpub\wwwroot\TestMongoPHP\IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.CSV" --fields ip_from,ip_to,country_code,country_name,region_name,city_name,latitude,longitude,zip_code,time_zone

My MongoDB is installed in C:\mongodb but if your installation folder is different, remember to change the example above. Same with the csv file folder; mine is C:\inetpub\wwwroot\TestMongoPHP\ so remember to change to the folder where you have extracted the csv file earlier.

Create index to speed up queries

Linux
Next, create a text file called buildindex and paste the following code into it:
use ip2location
db.db11.ensureIndex({ip_to: 1})
exit

Save the buildindex and then run the following in command prompt:
mongo < buildindex

Windows
Next, create a text file called buildindex.txt and paste the following code into it:
use ip2location
db.db11.ensureIndex({ip_to: 1})
exit

Save the buildindex.txt and then run the following in command prompt:
C:\mongodb\bin\mongo.exe < buildindex.txt

Again, my MongoDB is installed in C:\mongodb but if your installation folder is different, remember to change the example above.

Querying the IP2Location data from a PHP web page

Note: You will need to install the PHP driver for MongoDB. Please see the link below for more info: Go to http://docs.mongodb.org/ecosystem/drivers/php/ for more info.

Now, create a PHP file called test.php in your website.

Paste the following PHP code into it and then run it in the browser:

<?php
// ip address to test
$ip = "8.8.8.8";

// connect to Mongo
$m = new MongoClient();

// select a database
$db = $m->ip2location;

// select a collection (analogous to a relational database's table)
$collection = $db->db11;

function queryIP2Location($myip) {
        global $collection;

        // convert IP address to IP number
        $ipnum = sprintf("%u", ip2long($myip));

        // filter those ip_to which are greater than our IP number
        $query = array('ip_to' => array('$gte' => (float)$ipnum));

        // query the database and limit to only 1 result with sort ip_to in ascending order
        $cursor = $collection->find($query)->sort(array('ip_to' => 1))->limit(1);

        // convert from iterator to array form
        $retarr = iterator_to_array($cursor);
        $retarrfinal = array();

        // getting down to the sub-array which holds the actual data
        foreach ($retarr as $retarr2) {
                $retarrfinal = $retarr2;
        }

        // remove the Mongo auto id field
        unset($retarrfinal['_id']);

        return ($retarrfinal);
}

// call the function to get IP2Location information on the ip address
$myresult = queryIP2Location($ip);

// output the result
echo 'ip_from: ' . $myresult["ip_from"] . "<br>\n";
echo 'ip_to: ' . $myresult["ip_to"] . "<br>\n";
echo 'country_code: ' . $myresult["country_code"] . "<br>\n";
echo 'country_name: ' . $myresult["country_name"] . "<br>\n";
echo 'region_name: ' . $myresult["region_name"] . "<br>\n";
echo 'city_name: ' . $myresult["city_name"] . "<br>\n";
echo 'latitude: ' . $myresult["latitude"] . "<br>\n";
echo 'longitude: ' . $myresult["longitude"] . "<br>\n";
echo 'zip_code: ' . $myresult["zip_code"] . "<br>\n";
echo 'time_zone: ' . $myresult["time_zone"] . "<br>\n";
?>

Do you like this article? Share it with others by clicking the social media buttons below. We will write more articles related to this topic.