Building an IP-to-Country Mapping Application with PHP

If you have content in several different languages, your visitors probably speak several different languages as well. Wouldn’t it be nice to serve them content from your site in their native language, based on the country from which they hail? You can, even if you’re a small company, with an application that tells you a visitor’s country based on their IP address. This four-part article series will show you how to build the application and incorporate it into your web site.

Introduction

Due to the web’s constant evolution, and more specifically, because of the need to get more traffic, it’s now common to see web sites delivering their contents in different languages, depending on the countries from which they’re being accessed.

Google, Yahoo, and Hotmail, to cite a few, are good examples of how big companies implement this feature to offer a greater level of customization to their users all over the world. Even PHP’s official web site gives visitors the choice to read the PHP manual using their native languages, which has contributed to expanding its utilization worldwide.

While it is easy for big web players, like the ones mentioned above, to provide their services through a multi-lingual platform, for small and medium-scaled web sites, implementing this feature can be a more complex and challenging task.

Not all is lost in cases like these. Actually, building a small web site that will offer support for several languages might be easier than you think, thanks to the existence of a few IP-to-country mapping databases, which can be freely used during the development of a particular web project.

By way of a mapping database, it’s possible to build an IP-to-country application very easily. Such an application can be utilized, for instance, for creating a web site that delivers its contents in distinct languages according to the countries from which visitors are coming. 

Currently, there are a few good IP-to-country lookup databases available on the web that can be accessed and used for free, but in this particular tutorial I’m going to pick the one provided by Webnet77 (http://software77.net/cgi-bin/ip-country/geo-ip.pl). It contains more than 87,600 records, which permit you to map over 3,200,000,000 IP addresses to their corresponding countries. That’s a lot of information and hard work!

In summary, in this group of articles, I’ll be demonstrating how to use the aforementioned database to build an IP-to-country lookup application with PHP, which can be incorporated into any existing web site with minor hassles.

So, want to learn how to spice up you own web site with neat geo-IP location capabilities? Then start reading now!

{mospagebreak title=Start building an IP-to-country PHP application}

Before you start learning how to build an IP-to-country application with PHP, you should download the source lookup database from the Webnet77’s web site. Then, point your browser to http://software77.net/cgi-bin/ip-country/geo-ip.pl and proceed to download the ZIP file that contains a CSV version of the database to your machine (make sure to read the corresponding term and conditions before doing this).

Got that file uncompressed on your computer? Good. Now, it’s time to use it to transfer all the records contained in the source database to a new MySQL table, so they can be accessed with PHP. To do so, you’ll need to rename the downloaded file to “iptocountry.csv,” and then create the table in question in the following way:


CREATE TABLE ‘iptocountry’ (

‘lower_bound’ INT(11) UNSIGNED NOT NULL default ’0′,
’upper_bound’ INT(11) UNSIGNED NOT NULL default ’0′,
’owner’ VARCHAR(20) NOT NULL,
’last_updated’ INT(11) DEFAULT NULL,
’two_chars_country’ CHAR(2) NOT NULL default ”,
’three_chars_country’ CHAR(3) NOT NULL default ”,
’country’ VARCHAR(100) NOT NULL default ”

);


As shown by the above SQL code, the structure of the previous MySQL “iptocountry” table is comprised of seven fields, whose names speak for themselves about the type of data that they’ll house. In this case, the two first fields will store a lower IP boundary and an upper IP boundary respectively, represented as numbers (not as dot-separated octets). Then, there are a couple of additional fields, called “owner” and “last_updated,” where the first one represents the registry that owns that range of IP addresses, and the second field, not surprisingly, is the last time the range was updated.

In addition, the last three fields of the table will be used for storing the name of the country that corresponds to that range of IP addresses in the form of two-character and three-character strings respectively, as well as the country’s full name. Not too hard to grasp, right?

So far, so good. At this point, you’ve created a simple MySQL table, which will store all of the data required for building an IP-to-country mapping program with PHP. It’s quite possible, however, that at this moment you’re wondering how this data will be imported to this table.

Well, since the source lookup database is actually structured as a CSV file (comma-separated values), it’s necessary to build a simple PHP script that imports the data from that file to the “iptocountry” MySQL table.

Thus, if you’re interested in learning the full details of how this script will be set up, please click on the link that appears below and read the section to come.

{mospagebreak title=Transferring the data of the IP-to-country database to the MySQL table}

As you may have guessed, there are many ways to create a PHP script that transfers the contents of the CSV lookup database to the MySQL table defined in the previous segment. But in this situation, since the number of records to be imported is fairly huge, I’m going to build a small file reading program, based on the script developed by Matthew Pennell here.

In short, the PHP script that reads the content of the lookup database and imports it to the previous MySQL table looks like this:


try{

// connect to MySQL server

if(!$db=mysql_connect(‘host’,'user’,'password’)){

throw new Exception (‘Error connecting to the server.’);

}

// select database

if(!mysql_select_db(‘database’,$db)){

throw new Exception (‘Error selecting database.’);

}

// set timeout limit (handy when working with slower machines)

set_time_limit(360);

// open ‘iptocountry.csv’ file for further reading

if(!$fp=fopen(‘iptocountry.csv’,'r’)){

throw new Exception (‘Error reading source file.’);

}

// read file and populate ‘iptocountry’ MySQL table with each line of file

while(!feof($fp)){

$line=str_replace(‘"’,”,fgets($fp));

// check to see if current line is not a comment

if(substr($line,0,1)!=’#'){

// explode values

$values=explode(",",$line);

if(count($values)!=1){

// build query

$sql="INSERT INTO iptocountry VALUES (‘".implode("’,'",$values)."’)";

// run query against MySQL table

mysql_query($sql);

}

}

}

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


Although it may seen a bit complex at first, the logic that drives the above script is quite simple to grab. As shown previously, the script starts by connecting to MySQL and selecting a database (naturally, you should change these illustrative parameters and use the ones that correspond to your MySQL settings). It then  opens the lookup database file, and reads one line at a time.

Finally, if the file line being read isn’t a comment, it’s used to build an “INSERT” query, which imports the lookup data into the “iptocountry” MySQL table. It’s that simple, really.

In addition, there’s an important detail worth stressing here with reference to the way that the previous script works. As you may have noticed, it extends the timeout default value of PHP to 360 seconds via the “set_time_limit()” function. Here’s the reason: since the number of records to be imported is large (over 82,000 rows), this prevents the PHP engine from triggering a time out error when performing this process in a slow machine. Of course, if you’re running the script in a turbo-charged computer, you won’t suffer this problem.

All in all, if everything worked as expected, then by this time the “iptocountry” MySQL table should have been populated with all of the records imported from the lookup database file. The following screen capture shows how the table looks when viewed with the MySQL Query Browser:



Now things are getting really interesting, aren’t they? At the moment, the lookup MySQL table contains all the data required to build an IP-to-country mapping application. As I said before, my purpose here is to build this application specifically with PHP, but undoubtedly you can use another programming language, as long as it offers support for MySQL.

In the following section, I’m going to code a slightly different version of the prior PHP script, which will read all of the lines of the database file at once, implying that it should only be utilized with computers with a huge amount of RAM.

To see how this modified script will be developed, please jump forward and read the segment to come. 

{mospagebreak title=Importing records of the IP-to-country database to the MySQL table}

In the previous segment, you learned how to create a PHP script which was tasked with importing the contents of the lookup database file into the “iptocountry” MySQL table. Considering that this script reads one line of the file at a time, it’s feasible to create one for illustrative purposes that fetches all of the lines in a single step, by means of the “file()” PHP function.

Here’s how this sample script looks:


try{

// connect to MySQL server

if(!$db=mysql_connect(‘host’,'user’,'password’)){

throw new Exception (‘Error connecting to the server.’);

}

// select database

if(!mysql_select_db(‘database’,$db)){

throw new Exception (‘Error selecting database.’);

}

// read source file and populate ‘iptocountry’ MySQL table with each line of file

$lines=file(‘iptocountry.csv’);

foreach($lines as $line){

$line=str_replace(‘"’,”,$line);

// check to see if current line is not a comment

if(substr($line,0,1)!=’#'){

$values=explode(",",$line);

// explode values

if(count($values)!=1){

// build query

$sql="INSERT INTO iptocountry VALUES (‘".implode("’,'",$values)."’)";

// run query against MySQL table

mysql_query($sql);

}

}

}

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


Obviously, this modified version of the script built in the previous section doesn’t differ too much from the original, since it utilizes the “file()” PHP native function to read all the lines of the lookup database file at once. Even though this approach can be faster, it requires a web server equipped with more RAM, so if you’re running this script in a slower computer, then I suggest that you use the method discussed in the previous section.

Regardless of the PHP script that you may want to use for importing the records of the lookup database file into the “ipcountry” MySQL table, at this point the table in question should be filled in with all of the data required for building an IP-to-country mapping program.

However, this topic will be discussed in depth in the upcoming article of this series. In the meantime, feel free to tweak all the code samples shown in this tutorial, and don’t forget to take a look at the contents of the recently created “iptocountry” MySQL table. You’ll be amazed at how many IP addresses have been mapped to their corresponding countries.

Final thoughts

In this initial chapter of the series, I provided you with a quick overview on how to create an IP-to-country mapping MySQL table with PHP. Logically, the data stored on this table is pretty useless if it’s not linked to a concrete application that turns it into meaningful information.

Therefore, in the forthcoming article I’ll be demonstrating how to build an application like this. Now that you’ve been warned, you don’t have any excuses to miss the next tutorial!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan