The IP address reveals a website's visitor's location, but it requires a bit of programming work to find out which country they're from. Once you know the visitor's country, you can also deliver some country specific content such as banners ads. This article will help you to implement this procedure. To follow the tutorial, you'll need some basic understanding of PHP and SQL.

Webmasters usually try to cover their hosting cost by displaying advertising and affiliate links. One of the most popular affiliate programmes is Amazon Associates. Since I'm using the same one, we'll look at it as an example in this tutorial. There are actually many Amazon Associates programmes, one for each Amazon branch. If you have an English language website, you might be member of the Amazon Associates programmes for the UK, the US and for Canada. So the question arises, which product link do you deliver to which visitor.

The answer ist simple: You decide by their IP address. Compared to delivering four links to every visitor, this location based approach has more than doubled my conversion. Here's an example. The following banner will prompt you to an Amazon site relevant to you based on your IP address.

The implementation how to achieve this is also not too complicated, and if you're bringing some basic understanding of PHP and SQL, you'll manage to build this into your own website.

Create an IP-Country Database

The first step before you do anything else is to set up the database table and fill it with the mappings of IP addresses to countries. Most web hosts have MySQL installed, and an administration tool called phpMyAdmin. Go to your website's backend, create a new MySQL database (say ipcountry), a new database user (say ipcountry as well), and make up a user password. Then enter the phpMyAdmin panel.

Within phpMyAdmin, you then create a database table in the newly created database. Let's name it ipcountry also. The table needs to have a number of fields. Please create the fields according to the following table:

Assign the first field IP_FROM to be the table's primary key.

Populate the Database

We now need to get the information which IP addresses belong to which countries, and fill that data into the new database table. You can download an up to date IP to country database from WEB Net 77. Select IPV4 CSV (zip) from the list, download and unpack it. Then open it in Windows WordPad (or any other text editor), and delete the introductory lines, so that only the IP/country records remain (starting from 0).

Now in phpMyAdmin, select table ipcountry and go to import. Select the file type CSV and field separator , and import the downloaded file IpToCountry.csv. So the SQL part of the whole procedure is already finished. You should repeat these steps and replace the database content from time to time so you're always up to date.

Set up IP determination in PHP

Create a new file findcountry.php to include in every page that you want to have some country dependent content on.

<?php

$dbhost = "localhost";
$dbuser = "ipcountry";
$dbpass = "password";  //enter your password here
$dbname = "ipcountry";

$conn = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname);

$iparr = explode(".",$_SERVER["REMOTE_ADDR"]);
$ip = $iparr[3]+256*$iparr[2]+65536*$iparr[1]+16777216*$iparr[0];

$sql = "SELECT ctry FROM ipcountry WHERE ip_from <= $ip and $ip <= ip_to LIMIT 1";
$ip_query = mysql_query($sql);

$ctryarr = mysql_fetch_array($ip_query);
$ctry = $ctryarr[0];

// get browser locale for fallback routine
$acclangarr = explode(";",$_SERVER["HTTP_ACCEPT_LANGUAGE"]);
$acclangarr = explode(",",$acclangarr[0]);
$acclang4 = $acclangarr[0];
$acclang2 = strtolower(substr($acclang4,0,2));
$acccount = strtoupper(substr($acclang4,-2,2));

if (ctry == "ZZ")
  $acccount = $ctry;

//enhance the following routine to determine Amazon branch as needed
if  ($acccount == "CA"):
  $amzn = "CA";
elseif ($acccount == "GB"):
  $amzn = "UK";
elseif ($acccount == "US"):
  $amzn = "US";
endif;

mysql_close($conn);

?>

If you include this routine in your page, you'll have a couple of parameters you can work with:

• $amzn gives you the relevant Amazon branch.
• $acccount gives you the visitor's country iso code.
• $acclang gives you the visitor's language code.

Should your visitor come with a reserved IP address, a fallback logic replaces the respective iso code ZZ with the country iso code submitted by the browser, which is not as accurate as evaluating the IP address, as many users don't maintain their location in their browser settings. You can enhance the overall routine to determine the parameter $amzn according to your own needs, for instance, by adding more Amazon branches (Germany, Japan, China...), or by including more countries (e.g. Ireland, Mexico, Puerto Rico) and assign them to a suitable Amazon scheme.

Now include the new file findcountry.php in your site as follows.

<?php include("findcountry.php"); ?>

<p>some content</p>

<?php if ($amzn == "CA"): ?>
<p>Canadian banner</p>
<?php endif; ?>

<?php if ($amzn == "UK"): ?>
<p>British banner</p>
<?php endif; ?>

<?php if ($amzn == "US"): ?>
<p>American banner</p>
<?php endif; ?>

<p>some more content</p>

You could still improve this script by assigning visitors from other countries to one of the Amazon branches, or to distinguish between different languages (English and French in Canada for example). There are almost infinite possibilities for optimisation here.