Intro

The guide will demonstrate how to import IP2Proxy data (PX11) in CSV form into PostgreSQL and then query the data in a PHP page.

First of all, you will need to download the IP2Proxy PX11 CSV file.

Download commercial version at https://ip2location.com/download?code=PX11

Extract out the IP2PROXY-IP-PROXYTYPE-COUNTRY-REGION-CITY-ISP-DOMAIN-USAGETYPE-ASN-LASTSEEN-THREAT-RESIDENTIAL-PROVIDER.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 ip2proxy WITH ENCODING 'UTF8';

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

\c ip2proxy

Next, run the following command to create the table.

CREATE TABLE ip2proxy_px11( ip_from bigint NOT NULL, ip_to bigint NOT NULL, proxy_type character varying(3) 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, isp character varying(256) NOT NULL, domain character varying(128) NOT NULL, usage_type character varying(11) NOT NULL, asn character varying(6) NOT NULL, "as" character varying(256) NOT NULL, last_seen int NOT NULL, threat character varying(128) NOT NULL, provider character varying(256) NOT NULL, CONSTRAINT ip2proxy_px11_pkey PRIMARY KEY (ip_from, ip_to) );

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

COPY ip2proxy_px11 FROM 'C:\myfolder\IP2PROXY-IP-PROXYTYPE-COUNTRY-REGION-CITY-ISP-DOMAIN-USAGETYPE-ASN-LASTSEEN-THREAT-RESIDENTIAL-PROVIDER.CSV' WITH CSV QUOTE AS '"';

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

COPY ip2proxy_px11 FROM PROGRAM 'cmd /c "type C:\myfolder\IP2PROXY-IP-PROXYTYPE-COUNTRY-REGION-CITY-ISP-DOMAIN-USAGETYPE-ASN-LASTSEEN-THREAT-RESIDENTIAL-PROVIDER.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 queryIP2Proxy($myip) {
  $user = 'postgres';
  $pass = '12345';
  $host = 'localhost';
  $port = 5432;
  $dbname = 'ip2proxy';
  $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 ip2proxy_px11 WHERE $1 >= ip_from AND $1 <= ip_to ORDER BY ip_from, 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 = queryIP2Proxy($ip);

echo 'proxy_type: ' . $myresult["proxy_type"] . "<br>\n";
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 'isp: ' . $myresult["isp"] . "<br>\n";
echo 'domain: ' . $myresult["domain"] . "<br>\n";
echo 'usage_type: ' . $myresult["usage_type"] . "<br>\n";
echo 'asn: ' . $myresult["asn"] . "<br>\n";
echo 'as: ' . $myresult["as"] . "<br>\n";
echo 'last_seen: ' . $myresult["last_seen"] . "<br>\n";
echo 'threat: ' . $myresult["threat"] . "<br>\n";
echo 'provider: ' . $myresult["provider"] . "<br>\n";
?>