Intro

The guide will demonstrate how to import IP2Location data (DB26) in CSV form into IBM Db2 and then query the data in a PHP 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. We’ll store ours in C:\myfolder but you can choose any folder you wish.

Important Note

We will not cover installation of Db2 or PHP in this guide. We will assume you have already set up Db2 and PHP on the localhost and are using PHP via Apache (also on the localhost). For this example, we are using a Windows 10 machine.

Importing the CSV data into Db2

In the Db2 Command Line Processor, run the following command to create the database.

CREATE DATABASE ip2loc

After creating the database, you need to connect to it by running the below command. Our username is “db2admin” and password is “12345”. Change it to your own credentials.

CONNECT TO ip2loc USER db2admin USING "12345"

Next, run the following command to create the table.

CREATE TABLE db26(ip_from BIGINT NOT NULL CHECK (ip_from >= 0), ip_to BIGINT NOT NULL CHECK (ip_to >= 0), country_code CHAR(2), country_name VARCHAR(64), region_name VARCHAR(128), city_name VARCHAR(128), latitude DOUBLE, longitude DOUBLE, zip_code VARCHAR(30), time_zone VARCHAR(8), isp VARCHAR(256), domain VARCHAR(128), net_speed VARCHAR(8), idd_code VARCHAR(5), area_code VARCHAR(30), weather_station_code VARCHAR(10), weather_station_name VARCHAR(128), mcc VARCHAR(256), mnc VARCHAR(256), mobile_brand VARCHAR(128), elevation INT, usage_type VARCHAR(11), address_type CHAR(1), category VARCHAR(10), district VARCHAR(128), asn VARCHAR(10), "as" VARCHAR(256), PRIMARY KEY (ip_to))

Now that we have a table, we will commence the import of data from our CSV file into the table.

LOAD FROM "C:\myfolder\IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.CSV" OF DEL INSERT INTO db26

After the import is done, one last command is needed to make the table ready for queries.

SET INTEGRITY FOR db26 IMMEDIATE CHECKED

Querying the data in PHP

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 = '8.8.8.8';

function queryIP2Location($myip) {
  $database = 'ip2loc';
  $user = 'db2admin';
  $password = '12345';
  
  $conn = db2_connect($database, $user, $password);
  
  if ($conn) {
    $myipnum = sprintf('%u', ip2long($myip));
    
    $stmt = db2_prepare($conn, 'SELECT * FROM db26 WHERE ? <= ip_to ORDER BY ip_to LIMIT 1');
    if ($stmt) {
      if ($rs = db2_execute($stmt, [$myipnum])) {
        if ($result = db2_fetch_assoc($stmt)) {
          return $result;
        }
        else {
          die("No record found.<br>\n");
        }
      }
      else {
        die("Error: " . db2_stmt_errormsg() . "<br>\n");
      }
    }
    else {
      die("Error: " . db2_stmt_errormsg() . "<br>\n");
    }
    db2_close($conn);
  }
  else {
    die("Error: Could not connect.<br>\n");
  }
}

$myresult = queryIP2Location($ip);

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"; // this is lowercase due to the name being a reserved keyword
?>