The aim of this guide is to demonstrate how to import IP2Location data (DB26) in CSV form into SimpleDB and then query the data in a PHP web 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.
Also remember to download the AWS PHP SDK into the same folder as your web page.
Important Note
We will not cover installation of PHP in this guide. We will assume you have already setup PHP on the localhost and are using PHP via Apache (also on the localhost). For this example, we are using an Amazon EC2 instance running Debian Linux.
More info can be found at the following URLs if you need assistance with installations:
PHP: http://php.net/manual/en/install.unix.debian.php
AWS PHP SDK: http://docs.aws.amazon.com/aws-sdk-php/guide/latest/installation.html
We will be using the AWS PHP SDK to do mass import of CSV data into SimpleDB and then querying the data via PHP using the same SDK.
Importing the CSV data into SimpleDB
Create a new PHP file called import.php and paste the following code into it:
<?php
require('./aws.phar');
use Aws\SimpleDb\SimpleDbClient;
$db = 'DB26';
$filename = 'IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.CSV';
$itemsperbatch = 25; // limit of SimpleDB bulk import API
$padzero = 10; // need to pad the ip numbers because SimpleDB is comparing as strings, not numbers
$aws_key_id = 'YOUR_KEY_ID';
$aws_secret_key = 'YOUR_SECRET_KEY';
$aws_region = 'YOUR_REGION';
$autharr = array('key' => $aws_key_id, 'secret' => $aws_secret_key, 'region' => $aws_region);
$client = SimpleDbClient::factory($autharr);
$client->deleteDomain(array('DomainName' => $db)); // clear old DB26
$client->createDomain(array('DomainName' => $db));
$handle = fopen($filename, "r");
$contents = '';
$dataarr = array();
$counter = 0;
while (!feof($handle)) {
$line = fgets($handle, 8192);
$dataarr[] = $line;
if (count($dataarr) == $itemsperbatch) {
echo "Importing row " . $counter . "\n";
doImport($dataarr);
$dataarr = array(); //reset
}
}
fclose($handle);
if (count($dataarr) > 0) {
doImport($dataarr);
$dataarr = array(); //reset
}
function doImport($dataarr) {
global $client;
global $db;
global $counter;
global $padzero;
$mainarr = array();
foreach ($dataarr as $data) {
$data = rtrim($data); // clear EOL
if (preg_match('/^"[^"]+","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)"$/', $data, $matches) == 1) {
$itemarr = array();
$attributearr = array();
$counter++;
$ipto = $matches[1];
$countrycode = $matches[2];
$countryname = $matches[3];
$regionname = $matches[4];
$cityname = $matches[5];
$latitude = $matches[6];
$longitude = $matches[7];
$zipcode = $matches[8];
$timezone = $matches[9];
$isp = $matches[10];
$domain = $matches[11];
$netspeed = $matches[12];
$iddcode = $matches[13];
$areacode = $matches[14];
$weatherstationcode = $matches[15];
$weatherstationname = $matches[16];
$mcc = $matches[17];
$mnc = $matches[18];
$mobilebrand = $matches[19];
$elevation = $matches[20];
$usagetype = $matches[21];
$addresstype = $matches[22];
$category = $matches[23];
$district = $matches[24];
$asn = $matches[25];
$as = $matches[26];
$attributearr[] = array("Name" => "IP_TO", "Value" => str_pad($ipto, $padzero, '0', STR_PAD_LEFT), "Replace" => false);
$attributearr[] = array("Name" => "COUNTRY_CODE", "Value" => $countrycode, "Replace" => false);
$attributearr[] = array("Name" => "COUNTRY_NAME", "Value" => $countryname, "Replace" => false);
$attributearr[] = array("Name" => "REGION_NAME", "Value" => $regionname, "Replace" => false);
$attributearr[] = array("Name" => "CITY_NAME", "Value" => $cityname, "Replace" => false);
$attributearr[] = array("Name" => "LATITUDE", "Value" => $latitude, "Replace" => false);
$attributearr[] = array("Name" => "LONGITUDE", "Value" => $longitude, "Replace" => false);
$attributearr[] = array("Name" => "ZIP_CODE", "Value" => $zipcode, "Replace" => false);
$attributearr[] = array("Name" => "TIME_ZONE", "Value" => $timezone, "Replace" => false);
$attributearr[] = array("Name" => "ISP", "Value" => $isp, "Replace" => false);
$attributearr[] = array("Name" => "DOMAIN", "Value" => $domain, "Replace" => false);
$attributearr[] = array("Name" => "NET_SPEED", "Value" => $netspeed, "Replace" => false);
$attributearr[] = array("Name" => "IDD_CODE", "Value" => $iddcode, "Replace" => false);
$attributearr[] = array("Name" => "AREA_CODE", "Value" => $areacode, "Replace" => false);
$attributearr[] = array("Name" => "WEATHER_STATION_CODE", "Value" => $weatherstationcode, "Replace" => false);
$attributearr[] = array("Name" => "WEATHER_STATION_NAME", "Value" => $weatherstationname, "Replace" => false);
$attributearr[] = array("Name" => "MCC", "Value" => $mcc, "Replace" => false);
$attributearr[] = array("Name" => "MNC", "Value" => $mnc, "Replace" => false);
$attributearr[] = array("Name" => "MOBILE_BRAND", "Value" => $mobilebrand, "Replace" => false);
$attributearr[] = array("Name" => "ELEVATION", "Value" => $elevation, "Replace" => false);
$attributearr[] = array("Name" => "USAGE_TYPE", "Value" => $usagetype, "Replace" => false);
$attributearr[] = array("Name" => "ADDRESS_TYPE", "Value" => $addresstype, "Replace" => false);
$attributearr[] = array("Name" => "CATEGORY", "Value" => $category, "Replace" => false);
$attributearr[] = array("Name" => "DISTRICT", "Value" => $district, "Replace" => false);
$attributearr[] = array("Name" => "ASN", "Value" => $asn, "Replace" => false);
$attributearr[] = array("Name" => "AS", "Value" => $as, "Replace" => false);
$itemarr = array("Name" => "Row" . $counter, "Attributes" => $attributearr);
$mainarr[] = $itemarr;
}
}
$finalarr = array('DomainName' => $db, 'Items' => $mainarr);
$result = $client->batchPutAttributes($finalarr);
}
?>
Run the PHP script by calling the below command in command prompt:
php import.php
Querying the IP2Location data from a PHP web page
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
require('./aws.phar');
use Aws\SimpleDb\SimpleDbClient;
$db = 'DB26';
// ip address to test
$ip = "8.8.8.8";
$aws_key_id = 'YOUR_KEY_ID';
$aws_secret_key = 'YOUR_SECRET_KEY';
$aws_region = 'YOUR_REGION';
$autharr = array('key' => $aws_key_id, 'secret' => $aws_secret_key, 'region' => $aws_region);
$client = SimpleDbClient::factory($autharr);
function queryIP2Location($myip) {
global $db;
global $client;
$padzero = 10; // need to pad the ip numbers because SimpleDB is comparing as strings, not numbers
// convert IP address to IP number
$ipnum = sprintf("%u", ip2long($myip));
$ipnum = str_pad($ipnum, $padzero, '0', STR_PAD_LEFT);
$result = $client->select(array('SelectExpression' => "select * from " . $db . " where IP_TO >= '" . $ipnum . "' order by IP_TO asc limit 1", 'ConsistentRead' => true));
$resultarr = $result["Items"][0]["Attributes"];
$finalarr = array();
foreach ($resultarr as $data) {
$finalarr[$data["Name"]] = $data["Value"];
}
return $finalarr;
}
$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";
?>