The aim of this guide is to demonstrate how to import IP2Location data (DB26) in CSV form into Oracle Database and then query the data in a PHP web page.

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 Oracle or PHP in this guide. We will assume you have already setup Oracle 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 Oracle

Run the following SQL in Oracle to create the table and import the data from the CSV file:

CREATE TABLE ip2location_db26(
  ip_from NUMBER(10) CHECK (ip_from >= 0),
  ip_to NUMBER(10) CHECK (ip_to >= 0),
  country_code CHAR(2),
  country_name VARCHAR2(64),
  region_name VARCHAR2(128),
  city_name VARCHAR2(128),
  latitude BINARY_DOUBLE,
  longitude BINARY_DOUBLE,
  zip_code VARCHAR2(30),
  time_zone VARCHAR2(8),
  isp VARCHAR2(256),
  domain VARCHAR2(128),
  net_speed VARCHAR2(8),
  idd_code VARCHAR2(5),
  area_code VARCHAR2(30),
  weather_station_code VARCHAR2(10),
  weather_station_name VARCHAR2(128),
  mcc VARCHAR2(256),
  mnc VARCHAR2(256),
  mobile_brand VARCHAR2(128),
  elevation NUMBER(10),
  usage_type VARCHAR2(11),
  address_type CHAR(1),
  category VARCHAR2(10),
  district VARCHAR2(128),
  asn VARCHAR2(10),
  "as" VARCHAR2(256),
  CONSTRAINT pk_ip_to PRIMARY KEY (ip_to)
);

CREATE OR REPLACE DIRECTORY ip_tmp AS 'C:\your_CSV_file_folder';
GRANT READ, WRITE ON DIRECTORY ip_tmp TO sys;

INSERT INTO ip2location_db26(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")
SELECT ip_from2, ip_to2, country_code2, country_name2, region_name2, city_name2, latitude2, longitude2, zip_code2, time_zone2, isp2, domain2, net_speed2, idd_code2, area_code2, weather_station_code2, weather_station_name2, mcc2, mnc2, mobile_brand2, elevation2, usage_type2, address_type2, category2, district2, asn2, as2
FROM EXTERNAL (
  (ip_from2 NUMBER(10),
  ip_to2 NUMBER(10),
  country_code2 CHAR(2),
  country_name2 VARCHAR2(64),
  region_name2 VARCHAR2(128),
  city_name2 VARCHAR2(128),
  latitude2 BINARY_DOUBLE,
  longitude2 BINARY_DOUBLE,
  zip_code2 VARCHAR2(30),
  time_zone2 VARCHAR2(8),
  isp2 VARCHAR2(256),
  domain2 VARCHAR2(128),
  net_speed2 VARCHAR2(8),
  idd_code2 VARCHAR2(5),
  area_code2 VARCHAR2(30),
  weather_station_code2 VARCHAR2(10),
  weather_station_name2 VARCHAR2(128),
  mcc2 VARCHAR2(256),
  mnc2 VARCHAR2(256),
  mobile_brand2 VARCHAR2(128),
  elevation2 NUMBER(10),
  usage_type2 VARCHAR2(11),
  address_type2 CHAR(1),
  category2 VARCHAR2(10),
  district2 VARCHAR2(128),
  asn2 VARCHAR2(10),
  as2 VARCHAR2(256))
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ip_tmp
ACCESS PARAMETERS (
  RECORDS DELIMITED BY 0x'0A'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
)
LOCATION ('IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.CSV') REJECT LIMIT 0) db26_external;

EXEC DBMS_STATS.GATHER_TABLE_STATS('sys','ip2location_db26');

 

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) {
  $user = 'sys';
  $pass = '12345';
  $host = 'localhost';
  $port = 1521;
  $service = 'XE';
  $tns = '
  (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ' . $host . ')(PORT = ' . $port . '))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = ' . $service . ')
    )
  )';
  
  if ($conn = oci_connect($user, $pass, $tns, 'AL32UTF8', OCI_SYSDBA)) {
    $myipnum = sprintf('%u', ip2long($myip));
    
    $stid = oci_parse($conn, 'SELECT * FROM ip2location_db26 WHERE :ip_num <= ip_to ORDER BY ip_to FETCH FIRST 1 ROWS ONLY');
    oci_bind_by_name($stid, ':ip_num', $myipnum);
    
    oci_execute($stid);
    $num = oci_fetch_all($stid, $res, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
    
    oci_close($conn);
    
    if ($num > 0) {
      return $res[0];
    }
    else {
      die('No record found.' . "<br>\n");
    }
  }
  else {
    $err = oci_error();
    die('Error: ' . $err['message'] . "<br>\n");
  }
}

$myresult = queryIP2Location($ip);

echo 'COUNTRY_CODE: ' . $myresult["COUNTRY_CODE"] . "\n";
echo 'COUNTRY_NAME: ' . $myresult["COUNTRY_NAME"] . "\n";
echo 'REGION_NAME: ' . $myresult["REGION_NAME"] . "\n";
echo 'CITY_NAME: ' . $myresult["CITY_NAME"] . "\n";
echo 'LATITUDE: ' . $myresult["LATITUDE"] . "\n";
echo 'LONGITUDE: ' . $myresult["LONGITUDE"] . "\n";
echo 'ZIP_CODE: ' . $myresult["ZIP_CODE"] . "\n";
echo 'TIME_ZONE: ' . $myresult["TIME_ZONE"] . "\n";
echo 'ISP: ' . $myresult["ISP"] . "\n";
echo 'DOMAIN: ' . $myresult["DOMAIN"] . "\n";
echo 'NET_SPEED: ' . $myresult["NET_SPEED"] . "\n";
echo 'IDD_CODE: ' . $myresult["IDD_CODE"] . "\n";
echo 'AREA_CODE: ' . $myresult["AREA_CODE"] . "\n";
echo 'WEATHER_STATION_CODE: ' . $myresult["WEATHER_STATION_CODE"] . "\n";
echo 'WEATHER_STATION_NAME: ' . $myresult["WEATHER_STATION_NAME"] . "\n";
echo 'MCC: ' . $myresult["MCC"] . "\n";
echo 'MNC: ' . $myresult["MNC"] . "\n";
echo 'MOBILE_BRAND: ' . $myresult["MOBILE_BRAND"] . "\n";
echo 'ELEVATION: ' . $myresult["ELEVATION"] . "\n";
echo 'USAGE_TYPE: ' . $myresult["USAGE_TYPE"] . "\n";
echo 'ADDRESS_TYPE: ' . $myresult["ADDRESS_TYPE"] . "\n";
echo 'CATEGORY: ' . $myresult["CATEGORY"] . "\n";
echo 'DISTRICT: ' . $myresult["DISTRICT"] . "\n";
echo 'ASN: ' . $myresult["ASN"] . "\n";
echo 'AS: ' . $myresult["AS"] . "\n";
?>