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:
// connect to MySQL server
throw new Exception ('Error connecting to the server.');
// select database
throw new Exception ('Error selecting database.');
// set timeout limit (handy when working with slower machines)
// open 'iptocountry.csv' file for further reading
throw new Exception ('Error reading source file.');
// read file and populate 'iptocountry' MySQL table with each line of file
// check to see if current line is not a comment
// explode values
// build query
$sql="INSERT INTO iptocountry VALUES ('".implode("','",$values)."')";
// run query against MySQL table
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.
blog comments powered by Disqus