Home arrow PHP arrow Page 3 - Building an IP-to-Country Mapping Application with PHP

Transferring the data of the IP-to-country database to the MySQL table - 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.

TABLE OF CONTENTS:
  1. Building an IP-to-Country Mapping Application with PHP
  2. Start building an IP-to-country PHP application
  3. Transferring the data of the IP-to-country database to the MySQL table
  4. Importing records of the IP-to-country database to the MySQL table
By: Alejandro Gervasio
Rating: starstarstarstarstar / 2
February 03, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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. 



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: