Importing records of the IP-to-country database to the MySQL table - PHP

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!