Redirect Web Visitors By Country Using PHP and MySQL

There are times when it is useful to redirect a visitor to different default web page based on the visitor's country of origin. One practical usage is to redirect visitor to web page with the language recognized by the visitor. This article shows you how to build up such a system using PHP (server side scripting language) and MYSQL (IP address to country lookup database).

Let us take a simple case study. Company XYZ is multi-national company with major customers from United States and Japan. The company official website is developed in both English and Japanese languages. The default page is in English language and visitor can switch to Japanese by changing the default language option. There exists a potential risk when a Japanese visitor does not understand English and he could not navigate the web site.

In this tutorial, we use the IP2Location™ IP-Country database to lookup country of origin from the visitor's IP address. Instead of loading the full database with 50000+ records, we could simplify this tutorial with assumption only two different IP address ranges in the world. IP addresses 0.0.0.0 - 126.255.255.255 originate from United States. Meanwhile, IP addresses 127.0.0.0 - 255.255.255.255 originate from Japan. Here we are creating a database "ip2location" with table "ip_contry" that consists of two IP address range records.

Below are the steps to set up the database for both IPv4 and IPv6 data and the sample codes

Step 1: Create and connect to 'ip2location' database

CREATE DATABASE ip2location;
USE ip2location;

Step 2: Create 'ip_country' table

CREATE TABLE ip_country(
	ip_from DOUBLE NOT NULL,
	ip_to DOUBLE NOT NULL,
	country_code VARCHAR(2) NOT NULL,
	country_name VARCHAR(100) NOT NULL,
	PRIMARY KEY(ip_from, ip_to)
); 

Step 3. Insert dummy records to the database.

INSERT INTO ip_country VALUES (0, 2130706431,'US','UNITED STATES');
INSERT INTO ip_country VALUES (2130706432, 4294967295,'JP','JAPAN');

 

The full version of IP-Country database is available for subscription at $49/year from http://www.ip2location.com. If you have the full version of IP2Location?IP-Country database, the import process is easier by using the LOAD DATA feature available in MYSQL.

LOAD DATA INFILE "IPCountry.csv" INTO TABLE ip_country FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Sample Code:

<?php
// Replace this MYSQL server variables with actual configuration
$mysql_server = "mysql_server.com";
$mysql_user_name = "UserName";
$mysql_user_pass = "Password";

// Retrieve visitor IP address from server variable REMOTE_ADDR
$ipaddress = getenv(REMOTE_ADDR);

// Convert IP address to IP number for querying database
$ipno = Dot2LongIP($ipaddress);

// Connect to the database server
$link = mysql_connect($mysql_server, $mysql_user_name, $mysql_user_pass) or die("Could not connect to MySQL database");

// Connect to the IP2Location database
mysql_select_db("ip2location") or die("Could not select database");

// SQL query string to match the recordset that the IP number fall between the valid range
$query = "SELECT * FROM ip_country WHERE $ipno <= ip_to LIMIT 1";

// Execute SQL query
$result = mysql_query($query) or die("IP2Location Query Failed");

// Retrieve the recordset (only one)
$row = mysql_fetch_object($result);

// Keep the country information into two different variables
$country_code = $row->country_code;
$country_name = $row->country_name;

// Free recordset and close database connection
mysql_free_result($result); mysql_close($link);

// If the visitors are from JP, redirect them to JP site
if ($country_code == "JP")
{
  Header("Location: http://www.google.co.jp");
} else {
// Otherwise, redirect them to US site
  Header("Location: http://www.google.com");
}
exit;

// Function to convert IP address (xxx.xxx.xxx.xxx) to IP number (0 to 256^4-1)
function Dot2LongIP ($IPaddr) {
 if ($IPaddr == "")
 {
   return 0;
 } else {
   $ips = split ("\.", "$IPaddr");
   return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256);
 }
}
?>

 

Step 1: Create and connect to 'ip2location' database

CREATE DATABASE ip2location;
USE ip2location;

Step 2: Create 'ip_country' table

CREATE TABLE `ip_country`(
	`ip_from` DECIMAL(39,0) UNSIGNED,
	`ip_to` DECIMAL(39,0) UNSIGNED,
	`country_code` CHAR(2),
	`country_name` VARCHAR(64),
	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;

Step 3. Insert dummy records to the database.

INSERT INTO ip_country VALUES (0, 170141183460469231731687303715884105727,'US','UNITED STATES');
INSERT INTO ip_country VALUES (170141183460469231731687303715884105728, 340282366920938463463374607431768211455,'JP','JAPAN'); 

 

The full version of IP-Country database is available for subscription at $49/year from http://www.ip2location.com. If you have the full version of IP2Location?IP-Country database, the import process is easier by using the LOAD DATA feature available in MYSQL.

LOAD DATA INFILE "IPCountry.csv" INTO TABLE ip_country FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Sample Code:

<?php
// Replace this MYSQL server variables with actual configuration
$mysql_server = "mysql_server.com";
$mysql_user_name = "UserName";
$mysql_user_pass = "Password";

// Retrieve visitor IP address from server variable REMOTE_ADDR
$ipaddress = getenv(REMOTE_ADDR);

// Convert IP address to IP number for querying database
$ipno = Dot2LongIPv6($ipaddress);

// Connect to the database server
$link = mysql_connect($mysql_server, $mysql_user_name, $mysql_user_pass) or die("Could not connect to MySQL database");

// Connect to the IP2Location database
mysql_select_db("ip2location") or die("Could not select database");

// SQL query string to match the recordset that the IP number fall between the valid range
$query = "SELECT * FROM ip_country WHERE $ipno <= ip_to LIMIT 1";

// Execute SQL query
$result = mysql_query($query) or die("IP2Location Query Failed");

// Retrieve the recordset (only one)
$row = mysql_fetch_object($result);

// Keep the country information into two different variables
$country_code = $row->country_code;
$country_name = $row->country_name;

// Free recordset and close database connection
mysql_free_result($result); mysql_close($link);

// If the visitors are from JP, redirect them to JP site
if ($country_code == "JP")
{
  Header("Location: http://www.google.co.jp");
} else {
// Otherwise, redirect them to US site
  Header("Location: http://www.google.com");
}
exit;

// Function to convert IP address to IP number (IPv6)
function Dot2LongIPv6 ($IPaddr) {
    $int = inet_pton($IPaddr);
    $bits = 15;
    $ipv6long = 0;
    while($bits >= 0){
        $bin = sprintf("%08b", (ord($int[$bits])));
        if($ipv6long){
            $ipv6long = $bin . $ipv6long;
        }
        else{
            $ipv6long = $bin;
        }
        $bits--;
    }
    $ipv6long = gmp_strval(gmp_init($ipv6long, 2), 10);
    return $ipv6long;
}
?>

 


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.