Optimize Online Advertising Campaign by Country using PHP and MySQL Database

Let’s say you are coming from an English speaking country and you visit a website that shows you advertisements in a foreign language that you don’t understand. You would just ignore those advertisements like any normal person would. To the website owner, this is a lost opportunity to earn some money. That is why it is so vital to be able to match your web visitors with online advertisements that they will find relevant and useful. One way to do this is to show one set of advertisements for visitors from one country and then show another set to visitors from another country.

In this tutorial, we will use the IP2Location™IP-Country database to determine the country of origin for the web visitor just by checking the IP address. Instead of loading the full database with 50000+ records, we could simplify this tutorial with the assumption that only two different IP address ranges in the world. IP addresses of the range 0.0.0.0 - 126.255.255.255 originates from the United States. Meanwhile, IP addresses of the range 127.0.0.0 - 255.255.255.255 originates from Japan. Here we are creating a database called "IP2Location" with a table called "IPCountry" which contains our two IP address range records.


Step 1: Start the mysql command prompt. Run the 2 SQL command below to create the ‘IP2Location’ database and select it for use.

mysql> CREATE DATABASE IP2Location
mysql> CONNECT IP2Location


Step 2: Next, run the SQL statement below to create the ‘IPCountry’ table.

mysql> CREATE TABLE IPCountry
--> (
--> ipFROM DOUBLE NOT NULL,
--> ipTO DOUBLE NOT NULL,
--> countrySHORT VARCHAR(2) NOT NULL,
--> countryLONG VARCHAR(100) NOT NULL,
--> PRIMARY KEY(ipFROM, ipTO)
--> );


Step 3: Run the SQL codes below to insert dummy data into the database.

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


The full version of IP-Country database is available for subscription at $49/year from http://www.ip2location.com/ip-country.aspx.




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.

PC:
mysql> LOAD DATA INFILE "/IPCountry.csv" INTO TABLE IPCountry FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Linux/Unix:
mysql> LOAD DATA INFILE "/IPCountry.csv" INTO TABLE IPCountry FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';


Below is a sample code in PHP which will show you how to use the data to optimize your online advertising campaign by country.

<%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 IPCountry WHERE $ipno <= ipTO AND $ipno>=ipFROM";

// 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
$countrySHORT = $row->countrySHORT;
$countryLONG = $row->countryLONG;

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

if ($countrySHORT == "JP")
{
// If the visitors are from JP, show advertisement from JP
echo "<img src="Japan.jpg" border="0" height="200" width="100">";
}
else {
// Otherwise, show other advertisement
echo "<img src="US.jpg" border="0" height="200" width="100">";
}
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);
}
}
%>

IP2Location also carries a wide variety of databases which are more advanced and has more types of data which could also be used to further narrow down your target audience. If you want to try out the online demo, you can go to http://www.ip2location.com/demo.aspx and check it out.