Importing IP2Location data into MongoDB and querying with PHP

Importing IP2Location data into MongoDB and querying with PHP

Intro

The aim of this guide is to demonstrate how to import IP2Location data (DB26) 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 DB26 CSV file.
Download commercial version at https://ip2location.com/download?code=DB26

Extract out the IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.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. For our example, our MongoDB has a user called mongoAdmin with changeMe as the password. This user has been granted the privilege to drop databases. Please note that this is a requirement since we will drop the existing database and collection during import.

To grant the necessary permission, run the following in the mongo client.

use admin;
db.grantRolesToUser("mongoAdmin", ["root"]);

Pre-requisites

To install the MongoDB PHP driver, Linux users can run the following commands in Bash.

pecl install mongodb
echo "extension=mongodb.so" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`

Windows users can get the driver from https://windows.php.net/downloads/pecl/releases/mongodb/ and then edit the php.ini to load the dll.

To install the MongoDB PHP Library, you will need to have the PHP composer for either Linux or Windows then run the following command.

composer require mongodb/mongodb

NOTE: Make sure you run the above command in the folder where your PHP page is going to be created.

Importing the CSV data into MongoDB

For Linux

At the command prompt, run the following command:

mongoimport -u mongoAdmin -p changeMe --authenticationDatabase admin --drop --db ip2location --collection db26 --type csv --file "/var/www/TestMongoPHP/IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.CSV" --fields ip_from,ip_to,country_code,country_name,region_name,city_name,latitude,longitude,zip_code,time_zone,isp,domain,net_speed,idd_code,area_code,weather_station_code,weather_station_name,mcc,mnc,mobile_brand,elevation,usage_type,address_type,category,district,asn,as

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.

For Windows

At the command prompt, run the following command:

C:\mongodb\bin\mongoimport -u mongoAdmin -p changeMe --authenticationDatabase admin --drop --db ip2location --collection db26 --type csv --file "C:\inetpub\wwwroot\TestMongoPHP\IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.CSV" --fields ip_from,ip_to,country_code,country_name,region_name,city_name,latitude,longitude,zip_code,time_zone,isp,domain,net_speed,idd_code,area_code,weather_station_code,weather_station_name,mcc,mnc,mobile_brand,elevation,usage_type,address_type,category,district,asn,as

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

For Linux

Next, create a text file called buildindexipv4 and paste the following code into it:

use ip2location
db.db26.createIndex({ip_to: 1})
exit

Save the buildindexipv4 and then run the following in command prompt:

mongo -u mongoAdmin -p changeMe --authenticationDatabase admin < buildindexipv4

For Windows

Next, create a text file called buildindexipv4.txt and paste the following code into it:

use ip2location
db.db26.createIndex({ip_to: 1})
exit

Save the buildindexipv4.txt and then run the following in command prompt:

C:\mongodb\bin\mongo.exe -u mongoAdmin -p changeMe --authenticationDatabase admin < buildindexipv4.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

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

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

<?php
require 'vendor/autoload.php';

// ip address to test
$ip = "8.8.8.8";

$user = "mongoAdmin";
$pwd = "changeMe";

$m = new MongoDB\Client("mongodb://${user}:${pwd}@localhost:27017");

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

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

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));
  
  // perform query and return a single result
  $retarr = $collection->findOne($query);
  return ($retarr);
}

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

// output the result
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";
echo 'isp: ' . $myresult["isp"] . "<br>\n";
echo 'domain: ' . $myresult["domain"] . "<br>\n";
echo 'net_speed: ' . $myresult["net_speed"] . "<br>\n";
echo 'idd_code: ' . $myresult["idd_code"] . "<br>\n";
echo 'area_code: ' . $myresult["area_code"] . "<br>\n";
echo 'weather_station_code: ' . $myresult["weather_station_code"] . "<br>\n";
echo 'weather_station_name: ' . $myresult["weather_station_name"] . "<br>\n";
echo 'mcc: ' . $myresult["mcc"] . "<br>\n";
echo 'mnc: ' . $myresult["mnc"] . "<br>\n";
echo 'mobile_brand: ' . $myresult["mobile_brand"] . "<br>\n";
echo 'elevation: ' . $myresult["elevation"] . "<br>\n";
echo 'usage_type: ' . $myresult["usage_type"] . "<br>\n";
echo 'address_type: ' . $myresult["address_type"] . "<br>\n";
echo 'category: ' . $myresult["category"] . "<br>\n";
echo 'district: ' . $myresult["district"] . "<br>\n";
echo 'asn: ' . $myresult["asn"] . "<br>\n";
echo 'as: ' . $myresult["as"] . "<br>\n";
?>

Was this article helpful?

Related Articles