Intro

The guide will demonstrate how to import IP2Location data (DB26) in CSV form into PostgreSQL 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 PostgreSQL or PHP in this guide. We will assume you have already setup PostgreSQL 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 PostgreSQL

In the psql shell, run the following command to create the database.

CREATE DATABASE ip2location WITH ENCODING 'UTF8';

After creating the database, you need to select it by running the below command.

\c ip2location

Next, run the following command to create the table.

CREATE TABLE ip2location_db26( ip_from bigint NOT NULL, ip_to bigint NOT NULL, country_code character(2) NOT NULL, country_name character varying(64) NOT NULL, region_name character varying(128) NOT NULL, city_name character varying(128) NOT NULL, latitude real NOT NULL, longitude real NOT NULL, zip_code character varying(30) NOT NULL, time_zone character varying(8) NOT NULL, isp character varying(256) NOT NULL, domain character varying(128) NOT NULL, net_speed character varying(8) NOT NULL, idd_code character varying(5) NOT NULL, area_code character varying(30) NOT NULL, weather_station_code character varying(10) NOT NULL, weather_station_name character varying(128) NOT NULL, mcc character varying(256) NOT NULL, mnc character varying(256) NOT NULL, mobile_brand character varying(128) NOT NULL, elevation integer NOT NULL, usage_type character varying(11) NOT NULL, address_type character(1) NOT NULL, category character varying(10) NOT NULL, district character varying(128) NOT NULL, asn character varying(10) NOT NULL, "as" character varying(256) NOT NULL, CONSTRAINT ip2location_db26_pkey PRIMARY KEY (ip_to) );

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

COPY ip2location_db26 FROM 'C:\myfolder\IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.CSV' WITH CSV QUOTE AS '"';

WARNING: If you encountered a “could not stat file” error, try the below import command instead.

COPY ip2location_db26 FROM PROGRAM 'cmd /c "type C:\myfolder\IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.CSV"' WITH CSV QUOTE AS '"';

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 = 'postgres';
  $pass = '12345';
  $host = 'localhost';
  $port = 5432;
  $dbname = 'ip2location';
  $conn_string = "host=$host port=$port dbname=$dbname user=$user password=$pass";
  
  if ($conn = pg_connect($conn_string)) {
    $myipnum = sprintf('%u', ip2long($myip));
    
    if ($rs = pg_query_params($conn, 'SELECT * FROM ip2location_db26 WHERE $1 <= ip_to ORDER BY ip_to LIMIT 1', [$myipnum])) {
      if ($result = pg_fetch_all($rs)) {
        return $result[0];
      }
      else {
        die('No record found.' . "<br>\n");
      }
    }
    else {
      die('Error: ' . pg_last_error() . "<br>\n");
    }
  }
  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"; 
?>