Visit any websites and you’ll most likely find some form of localization being used. The most common is the showing of ads local to you even though the website may be foreign. Using the IP address of the web visitor, the website operator can target relevant ads, content or change the language of the website based on your geolocation country.
We will explore how to implement this geolocation feature into your own website using PHP. Part 1 deals with creating the database & table as well as importing the data. Part 2 will show how to use PHP to query the database and retrieve the results.
Part 1: Importing the geolocation data
Let’s assume your website is running an Apache web server with PHP powering the backend. We’ll assume you are using MySQL as PHP usually operates with MySQL.
The first step is to download the DB11 LITE database file from https://lite.ip2location.com/database/ip-country-region-city-latitude-longitude-zipcode-timezone which is free after sign-up.
Once you’ve downloaded the zipped file, extract the IP2LOCATION-LITE-DB11.CSV data file to a folder called /root/ip2location_data (this is just an example folder name).
In your MySQL, run the following to create the database and table where we’ll store the DB11 data.
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_to` (`ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;Now run the following in MySQL to import the data.
LOAD DATA LOCAL
INFILE '/root/ip2location_data/IP2LOCATION-LITE-DB11.CSV'
INTO TABLE
`ip2location_db11`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 0 LINES;Part 2: Querying the geolocation data in your PHP web page
We will now create the PHP webpage to query the database and output the geolocation data for the page visitor.
Create a page called test.php and paste the following code into it.
<?php
$dbname = 'ip2location'; // database name
$dbhost = 'localhost'; // database server name
$dbuser = 'root'; // database user login
$dbpass = '12345'; // database user password
?>
<!DOCTYPE html>
<head>
<title>Test page</title>
</head>
<body>
<?php
try {
$pdo = new PDO('mysql:host=' . $dbhost . ';dbname=' . $dbname . ';charset=utf8', $dbuser, $dbpass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$ip = $_SERVER['REMOTE_ADDR']; // web visitor's IP address
$st = $pdo->prepare('select * from `ip2location_db11` where inet_aton(:ip) <= ip_to limit 1');
$st->bindParam(':ip', $ip, PDO::PARAM_STR);
$st->execute();
$data = $st->fetchAll(PDO::FETCH_ASSOC);
if ($st->rowCount() > 0) {
echo 'country_code: ' . $data[0]['country_code'] . "<br />\n";
echo 'country_name: ' . $data[0]['country_name'] . "<br />\n";
echo 'region_name: ' . $data[0]['region_name'] . "<br />\n";
echo 'city_name: ' . $data[0]['city_name'] . "<br />\n";
echo 'latitude: ' . $data[0]['latitude'] . "<br />\n";
echo 'longitude: ' . $data[0]['longitude'] . "<br />\n";
echo 'zip_code: ' . $data[0]['zip_code'] . "<br />\n";
echo 'time_zone: ' . $data[0]['time_zone'] . "<br />\n";
}
else {
echo 'No data found.<br />' . "\n";
}
}
catch(PDOException $e) {
echo 'An error has occurred.<br />' . "\n";
}
?>
</body>
</html>Now open that page in your browser. You should see all the geolocation data for your own IP address. Once you can see all the geolocation fields returned by the IP2Location DB11 LITE data, you can use them in many scenarios. You can filter traffic, and block or redirect users based on their locations.
If you have more questions about the implementation, you can find more tutorials in IP2Location Blog.
If you liked this article, then please subscribe to our YouTube Channel for video tutorials.
