How to determine the region by IP in PHP using the DB

In this article, we’ll look at one way to determine a user’s location based on their IP address — specifically, using an existing database of regions and assigned IP address ranges.

There are many such databases available. I happened to work with a database from ip2location.com. I can’t say anything bad about it — it correctly identified my region and those of some of my clients.

The first thing we need is to download the database file. They provide it in CSV format, and you can get it from this page.

Importing the IP and Region Data File

Follow the instructions in the description to save time. Initially, I tried importing the data via phpMyAdmin using CSV import, and that took significantly longer than a console-based import.

Just in case, here’s the code again. We’ll create a database and a table for locations:

CREATE DATABASE ip2location;
USE ip2location;
CREATE TABLE `ip2location_db11`(
	`ip_from` INT(10) UNSIGNED,
	`ip_to` INT(10) UNSIGNED,
	`country_code` CHAR(2),
	`country_name` VARCHAR(64),
	`region_name` VARCHAR(128),
	`city_name` VARCHAR(128),
	`latitude` DOUBLE,
	`longitude` DOUBLE,
	`zip_code` VARCHAR(30),
	`time_zone` VARCHAR(8),
	INDEX `idx_ip_from` (`ip_from`),
	INDEX `idx_ip_to` (`ip_to`),
	INDEX `idx_ip_from_to` (`ip_from`, `ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Next, import the data (by the way, this can also be done via phpMyAdmin by specifying the full path to the CSV file):

LOAD DATA LOCAL
	INFILE 'IP2LOCATION-LITE-DB11.CSV'
INTO TABLE
	`ip2location_db11`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 0 LINES;

Determining User Location by IP

In the example below, I renamed the table from “ip2location_db11” to “ip2location_v4”. Code listing:

$ip_user = '195.208.131.1'; /* Russia, Novosibirsk */
$ip_user = '5.101.112.0'; /* Estonia */

$sql = 'SELECT *
	FROM `ip2location_v4` 
	WHERE "'.ip2long($ip_user).'" BETWEEN `ip_from` AND `ip_to`
	LIMIT 1';

$Result = mysqli_query($DB, $sql);
$Data = mysqli_fetch_assoc($Result);

echo "<pre dir='ltr'>";print_r($Data); echo "</pre>";

Where:
ip2long() — function that converts a string containing an IPv4 address to an integer.

The sample IPs were found online. Note: the code above does not include a DB connection, so don’t copy and run it blindly without adapting it.