Importing IP2Location data into DynamoDB and querying with PHP

The aim of this guide is to demonstrate how to import IP2Location data (DB11) in csv form into DynamoDB 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 http://lite.ip2location.com/database-ip-country-region-city-latitude-longitude-zipcode-timezone
Download commercial version at http://ip2location.com/download?code=DB11CSV

Extract out the IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.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 DynamoDB and then querying the data via PHP using the same SDK.

Importing the csv data into DynamoDB

Create a new PHP file called config.php and paste the following code into it:

<?php
return array(
	'includes' => array('_aws'),
	'services' => array(
		'default_settings' => array(
			'params' => array(
				'key'    => 'YOUR_KEY_ID',
				'secret' => 'YOUR_SECRET_KEY',
				'region' => 'YOUR_REGION'
			)
		)
	)
);
?>

Next, create a new PHP file called import.php and paste the following code into it:

<?php
require('./aws.phar');
use Aws\DynamoDb\DynamoDbClient;
use Aws\Common\Enum\Region;
use Aws\DynamoDb\Enum\KeyType;
use Aws\DynamoDb\Enum\Type;

$db = 'DB11';
$filename = 'IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.CSV';

$itemsperbatch = 25; // limit of bulk import API

// Instantiate the client with your AWS credentials
$aws = Aws\Common\Aws::factory('./config.php');
$client = $aws->get("dynamodb");

do {
	$response = $client->listTables(array('Limit' => 2, 'ExclusiveStartTableName' => isset($response) ? $response['LastEvaluatedTableName'] : null)); 
	
	foreach ($response['TableNames'] as $key => $value) {
		if ($value == $db) // DB11 already exists so must drop first
		{
			// drop all DB11
			$result = $client->deleteTable(array("TableName" => $db));
			$client->waitUntilTableNotExists(array("TableName" => $db));
		}
	}
}
while ($response['LastEvaluatedTableName']);

$attributes = array();
$keys = array();

$attributes[] = array("AttributeName" => "MY_ID", "AttributeType" => Type::STRING);
$attributes[] = array("AttributeName" => "IP_TO", "AttributeType" => Type::NUMBER);
$keys[] = array("AttributeName" => "MY_ID", "KeyType" => KeyType::HASH);
$keys[] = array("AttributeName" => "IP_TO", "KeyType" => KeyType::RANGE);

// create new DB11
$tablearr = array("TableName" => $db, "AttributeDefinitions" => $attributes, "KeySchema" => $keys, "ProvisionedThroughput" => array("ReadCapacityUnits" => 5, "WriteCapacityUnits" => 25));

$result = $client->createTable($tablearr);
$client->waitUntilTableExists(array("TableName" => $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;
	
	$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];
			$attributearr["MY_ID"] = array(Type::STRING => $db); // DynamoDB needs 2 different field names for hash key and range key
			$attributearr["IP_TO"] = array(Type::NUMBER => $ipto);
			$countrycode = $matches[2];
			$attributearr["COUNTRY_CODE"] = array(Type::STRING => $countrycode);
			$countryname = $matches[3];
			$attributearr["COUNTRY_NAME"] = array(Type::STRING => $countryname);
			$regionname = $matches[4];
			$attributearr["REGION_NAME"] = array(Type::STRING => $regionname);
			$cityname = $matches[5];
			$attributearr["CITY_NAME"] = array(Type::STRING => $cityname);
			$lat = $matches[6];
			$attributearr["LATITUDE"] = array(Type::STRING => $lat);
			$long = $matches[7];
			$attributearr["LONGITUDE"] = array(Type::STRING => $long);
			$zipcode = $matches[8];
			$attributearr["ZIP_CODE"] = array(Type::STRING => $zipcode);
			$timezone = $matches[9];
			$attributearr["TIME_ZONE"] = array(Type::STRING => $timezone);
			
			$itemarr = array("PutRequest" => array("Item" => $attributearr));
			$mainarr[] = $itemarr;
		}
	}
	
	$finalarr = array('RequestItems' => array($db => $mainarr));
	redo:
	$result = $client->batchWriteItem($finalarr);
	
	// see if there are any items that couldn't be imported then we resubmit for import
	if (!empty($result['UnprocessedItems'])) {
		$finalarr = array('RequestItems' => $result['UnprocessedItems']);
		goto redo;
	}
}
?>

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\DynamoDb\DynamoDbClient;
use Aws\Common\Enum\Region;
use Aws\DynamoDb\Enum\Type;
use Aws\DynamoDb\Enum\ComparisonOperator;

$db = 'DB11';

//test IP
$ip = '8.8.8.8';

// Instantiate the client with your AWS access keys
$aws = Aws\Common\Aws::factory("./config.php");
$client = $aws->get("dynamodb");

function queryIP2Location($myip) {
	global $db;
	global $client;
	
	// convert IP address to IP number
	$ipnum = sprintf("%u", ip2long($myip));
	
	$request = array(
		"TableName" => $db,
		"KeyConditions" => array(
			"MY_ID" => array(
				"ComparisonOperator" => ComparisonOperator::EQ,
				"AttributeValueList" => array(
					array(Type::STRING => $db)
				)
			),
			"IP_TO" => array(
				"ComparisonOperator" => ComparisonOperator::GE,
				"AttributeValueList" => array(
					array(Type::NUMBER => $ipnum)
				)
			)
		),
		"AttributesToGet" => array("COUNTRY_CODE", "COUNTRY_NAME", "REGION_NAME", "CITY_NAME", "LATITUDE", "LONGITUDE", "ZIP_CODE", "TIME_ZONE"),
		"ConsistentRead" => true,
		"Limit" => 1
	);
	
	$response = $client->query($request);
	
	$result = array();
	
	foreach ($response['Items'] as $key => $value) {
		$result["COUNTRY_CODE"] = $value["COUNTRY_CODE"]["S"];
		$result["COUNTRY_NAME"] = $value["COUNTRY_NAME"]["S"];
		$result["REGION_NAME"] = $value["REGION_NAME"]["S"];
		$result["CITY_NAME"] =  $value["CITY_NAME"]["S"];
		$result["LATITUDE"] =  $value["LATITUDE"]["S"];
		$result["LONGITUDE"] =  $value["LONGITUDE"]["S"];
		$result["ZIP_CODE"] =  $value["ZIP_CODE"]["S"];
		$result["TIME_ZONE"] =  $value["TIME_ZONE"]["S"];
	}
	
	return $result;
}

$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";

?>

Do you like this article? Share it with others by clicking the social media buttons below. We will write more articles related to this topic.