Importing IP2Location data into Oracle Database and querying with PHP (IPv4)

The aim of this guide is to demonstrate how to import IP2Location data (DB11) in CSV form into Oracle Database 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 https://lite.ip2location.com/database-ip-country-region-city-latitude-longitude-zipcode-timezone
Download commercial version at https://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 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_db11(
  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),
  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_db11(ip_from, ip_to, country_code, country_name, region_name, city_name, latitude, longitude, zip_code, time_zone)
SELECT ip_from2, ip_to2, country_code2, country_name2, region_name2, city_name2, latitude2, longitude2, zip_code2, time_zone2
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))
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.CSV') REJECT LIMIT 0) db11_external;

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_db11 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"] . "<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";
?>

 

Was this article helpful?

Related Articles