GeoNames Forum

Hi,

I'm currently trying to write some php scripts to utilise the "update" files available in the download section to keep a mysql copy of the geonames dataset in synch.

So far I've got one that pretty much works on the "modifications" file - code herewith (currently doing an delete and insert instead of update until I'm ready to load the full file):

Code:

 <?php
   date_default_timezone_set("UTC");
 
   $mysqlConnection = mysql_connect("server","db","pwd");
 
   if (!$mysqlConnection)
     {
       die("Could not connect: " . mysql_error());
     }
 
   mysql_set_charset("utf8");
 
   mysql_select_db("db");
 
 mysql_query("DELETE FROM geonames");
 
   // Get a file into an array.
   $lines = file("http://download.geonames.org/export/dump/modifications-" . date("Y-m-d", time() - 86400) . ".txt", FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
 
   // Loop through our array.
   foreach ($lines as $line_num => $line)
     {
       $lineData = explode("\t", $line);
 print 'INSERT INTO geonames VALUES ("' . $lineData[0] . '", "' . $lineData[1] . '", "' . $lineData[2] . '", "' . $lineData[3] . '", "' . $lineData[4] . '", "' . $lineData[5] . '", "' . $lineData[6] . '", "' . $lineData[7] . '", "' . $lineData[8] . '", "' . $lineData[9] . '", "' . $lineData[10] . '", "' . $lineData[11] . '", "' . $lineData[12] . '", "' . $lineData[13] . '", "' . $lineData[14] . '", "' . $lineData[15] . '", "' . $lineData[16] . '", "' . $lineData[17] . '", "' . $lineData[18] . '")' . "<br />";
       mysql_query('INSERT INTO geonames VALUES ("' . $lineData[0] . '", "' . $lineData[1] . '", "' . $lineData[2] . '", "' . $lineData[3] . '", "' . $lineData[4] . '", "' . $lineData[5] . '", "' . $lineData[6] . '", "' . $lineData[7] . '", "' . $lineData[8] . '", "' . $lineData[9] . '", "' . $lineData[10] . '", "' . $lineData[11] . '", "' . $lineData[12] . '", "' . $lineData[13] . '", "' . $lineData[14] . '", "' . $lineData[15] . '", "' . $lineData[16] . '", "' . $lineData[17] . '", "' . $lineData[18] . '")');
     }
 
   mysql_close($mysqlConnection);
 ?>

I'm actually quite happy with this effort so far since its the first php script I've ever written!

Before I start replicating this for the other "update" files can you please advise how this should be improved?

For instance I'm sure there should be more error checking in there (e.g. for when the update file isn't there and the geonames server returns a 302) or when the db connection doesn't happen etc but I finding it hard to work my way through the php manual (missing the mysql_set_charset had me scratching my noggin for quite some time I can tell you

) and aren't really sure what I should be looking at. Also I'd like to add some sort of auto email function if it fails.

As you can probably guess I want to make it as "bullet proof" as is reasonably possible - can anyone offer my some advice?

Thanks,

Adam M.

Ok... so digging a bit further it looks like I can incorporate the email() function to do the email bit. I would appreciate some advice on dealing with the 302 and additional error checking though.

Thanks.