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
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.
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
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+256*$iparr+65536*$iparr+16777216*$iparr; $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; // get browser locale for fallback routine $acclangarr = explode(";",$_SERVER["HTTP_ACCEPT_LANGUAGE"]); $acclangarr = explode(",",$acclangarr); $acclang4 = $acclangarr; $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.