Review: building an IP-to-country mapping MySQL table with a lookup database file - PHP

In case you haven't had the chance to read the preceding article of this series, in the next few lines I'm going to list for you all of the source files required for building an IP-to-country MySQL table. I will be using the records of the source lookup database, previously downloaded from Webnet77's web site.

That being said, here are the corresponding definitions for each of these files:

(definition of 'create_ip_to_country_table.sql' file)

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 ''

);

(definition of 'populate_table.php' file)

<?php

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();

}

?>

The two files listed above contain all the source code required for creating an IP-to-country mapping MySQL table. In this case, the first SQL file is responsible for creating the structure of the table in question, while the second one is tasked with importing the records of the source lookup database, which was previously downloaded as a CSV file from Webnet77's web site.

Now that you remember how the previous source files do their things, it's time to build a pair of simple PHP applications. These applications will take advantage of the functionality of the "iptocountry" MySQL table created in the previous tutorial to map the IP addresses of different users to their originating countries.

To learn how these sample PHP programs will be developed, please click on the link that appears below and keep reading.