Geotargeting database |

Source link: http://ekstreme.com/geotargeting/flags-comments.php

Requirements

A database of worldwide IP addresses. (I’ll show you where to get one in a second.)
A database, like MySQL, and a server-side programming language, like PHP. The examples in this tutorial are PHP/MySQL based, but any combination should work.
Optionally, a set of country flags – again, I’ll show where you get them.

Geo IP database: setup

The first thing you need is a database of world wide IP addresses. A freely available one is MaxMind’s GeoLite Country Database. Download the CSV version, not the binary format version.

A note about the database:

It is updated once a month, so you can (and should) update your copy frequently.
It is claimed to be 97% accurate. This is sufficient for most users, but if you want a more accurate version, download the MaxMind GeoIP Country Database version, which is not free.

Once you download the IP database in CSV format, you need to upload it into your MySQL database. Here are the instructions:

Create a new table in your database; call it IPCountries.
In the IPCountries table, create 6 (six) new fields.
The fields are:

Field Type

IP_START VARCHAR, length 50

IP_END VARCHAR, length 50

IP_FROM Double

IP_TO Double

COUNTRY_CODE2 Char, length 2

COUNTRY_NAME VARCHAR, length 50
Import the CSV file into the MySQL table. If you are using phpMyAdmin, at the very bottom of the table view is a link that says ‘Insert data from a text file into the table’. Click that and then make sure the settings are the following:

Setting Value

Fields terminated by , (a comma)

Fields enclosed by ” (a double-quote)

Lines terminated by \n (a linefeed)

Now browse to where you save the CSV file and click ‘Submit’. It’s a large file, so be patient

Field	Type
IP_START	VARCHAR, length 50
IP_END	VARCHAR, length 50
IP_FROM	Double
IP_TO	Double
COUNTRY_CODE2	Char, length 2
COUNTRY_NAME	VARCHAR, length 50

Setting	Value
Fields terminated by	, (a comma)
Fields enclosed by	” (a double-quote)
Lines terminated by	\n (a linefeed)

Now that we have the IP database ready, let’s use it!

IP Database Explanation

With the database ready, let’s take a look at what each field means:

Field	Type
IP_START	The start of the IP range.
IP_END	The end of the IP range.
IP_FROM	The start of the IP range, as a long number.
IP_TO	The end of the IP range, as a long number.
COUNTRY_CODE2	A two-letter country code, such as ‘UK’.
COUNTRY_NAME	The full country name, such as ‘Germany’.

Geotargeting PHP Code

The code is very simple:

We get the visitor’s IP address…
…and look it up in the database…
…and figure out which country the IP address belongs to.

And the code is:

$DatabaseServer = “YOUR_DATABASE_SERVER”;

$Username = “YOUR_DATABASE_USERNAME”;

$Password = “YOUR_DATABASE_PASSWORD”;

$DatabaseName = “YOUR_DATABASE_NAME”;

$link = mysql_connect($DatabaseServer, $Username, $Password) or die(‘Could not connect: ‘ . mysql_error());

mysql_select_db($DatabaseName) or die(‘Could not select database’);

$IP = $_SERVER[“REMOTE_ADDR”]; //Get the IP address

$res = mysql_query(“SELECT country_code2,country_name FROM IPCountries WHERE IP_FROM<=inet_aton(‘$IP’) AND IP_TO>=inet_aton(‘$IP’)”);//look up IP address

$Codes = mysql_fetch_array($res); //get result

$CountryCode = $Codes[‘country_code2’]; //two-letter country code

$CountryName = $Codes[‘country_name’]; //full country name

echo “$CountryCode – $CountryName”; //print it out, just as an example

mysql_close($link); //clean up

And what does it look like? For your IP address, this is its output:

Geographical location of IP address is:

Unknown country

This code is used to display IP information at the PHPCounter reverse DNS and whois tool. (I’ll show you where to get flag icons later.)

In this example, we simply printed out the country code and name. In a real-world example, you could use this information to target content. For example, to output content for IP addresses from France, you could use:

if($CountryCode == “FR”){

//echo contents for France

echo “Bonjour!”

}

else{

//default content

echo “Hello!”

}

Country flags

One cool feature is to get country flags to display. There are two places to get good-quality flags:

The AWStats package is an open source log analyzer. It comes with a full set of country flags. Download it, and the flags are in the \wwwroot\icon\flags directory.
A set of open source flags are published by the Flags of the World project.

Personally, I like the AWStats flags more.

Closing remarks

Just a few comments to close off with:

Performance: If you have a popular website, having geotargeting code can cause serious strain. The IP database contains over 60000 rows, which is not exactly a small database. Looking through the database will stress your server.
Usability: If you geotarget languages, then make sure that there is a way to change the language by the user. An example: if you geotarget French content to France, then an English-speaking tourist in France will get French. Make sure this tourist can get to your English content if he/she wants to!
Accuracy: The database is claimed to be 97% accurate. This is sufficient for most users, but if you want a more accurate version, download the MaxMind GeoIP Country Database version, which is not free.

In short, just think through your geotargeting implementation, as you would for everything else!

Galhano //127.0.0.1