Online advertising is another way to promote company products. It’s very important to show the right advertisements to the right consumers to have an optimum response. A company selling their products in Japan showing their advertisement to visitors from United States is totally ineffective. On the other hand, localized advertisements catch visitor attention and improve sales. With all kind of sources or tools, finding your targeted audience based on IP address is easier than ever.
In this tutorial, we use the IP2Location™ IP-Country database to lookup country of origin from the visitor’s IP address.
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` INT(10) UNSIGNED, `ip_to` INT(10) 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. Import the IP2Location LITE IP-COUNTRY Database into table ‘ip_country’, you can get the Lite database at https://lite.ip2location.com/database/ip-country
LOAD DATA LOCAL INFILE 'IP2LOCATION-LITE-DB1.CSV' INTO TABLE `ip_country` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
The full version of IP-Country database is available for subscription at $49/year from https://www.ip2location.com/databases/db1-ip-country. 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.
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 = $_SERVER['REMOTE_ADDR'];
// Convert IP address to IP number for querying database
$ipno = Dot2LongIP($ipaddress);
// Connect to the database server
$link = mysqli_connect($mysql_server, $mysql_user_name, $mysql_user_pass) or die("Could not connect to MySQL database");
// Connect to the IP2Location database
mysqli_select_db($link,"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 = mysqli_query($link,$query) or die("IP2Location Query Failed");
// Retrieve the recordset (only one)
$row = mysqli_fetch_assoc($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
mysqli_free_result($result);
mysqli_close($link);
// Display ads according to visitor's country,replace this to your own code
echo 'This is the advertisement for' . $country_name .'
';
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 = explode(".", $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. Import the IP2Location LITE IP-COUNTRY IPV6 Database into table ‘ip_country’, you can get the Lite database at https://lite.ip2location.com/database/ip-country
LOAD DATA LOCAL INFILE 'IP2LOCATION-LITE-DB1.IPV6.CSV' INTO TABLE `ip_country` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
The full version of IP-Country database is available for subscription at $49/year from https://www.ip2location.com/databases/db1-ip-country. 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.
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 = $_SERVER['REMOTE_ADDR'];
// Convert IP address to IP number for querying database
$ipno = Dot2LongIPv6($ipaddress);
// Connect to the database server
$link = mysqli_connect($mysql_server, $mysql_user_name, $mysql_user_pass) or die("Could not connect to MySQL database");
// Connect to the database server
mysqli_select_db($link,"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 = mysqli_query($link,$query) or die("IP2Location Query Failed");
// Retrieve the recordset (only one)
$row = mysqli_fetch_assoc($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
mysqli_free_result($result);
mysqli_close($link);
// Display ads according to visitor's country,replace this to your own code
echo 'This is the advertisement for' . $country_name .'
';
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;
}
?>