Importing IP2Location data into PostgreSQL and querying with PHP (IPv4)

Intro

The guide will demonstrate how to import IP2Location data (DB11) in CSV form into PostgreSQL and then query the data in a PHP 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. 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_db11( 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, CONSTRAINT ip2location_db11_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_db11 FROM 'C:\myfolder\IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.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=5432 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_db11 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";
?>

 

Was this article helpful?

Related Articles