May 30

PHP ZipCode to TimeZone

I have been building a database of customers to call and found one slight issue, I needed to be able to workout which timezone each of the contacts are in so I only call when its suitable for them!

I started off by using one of the online ZipCode to TimeZone converters and manually adding the data to the existing database, but soon ran into difficulties because the websites limit the number of requests that could be run (Its cookie based so while I could probably have worked round it I needed a better solution). I also looked at using the telephone area code in the database and the various online converters which provide the TimeZone based on it, again after a number of requests the pages stopped working.

Having to cut and paste the Zipcode between the pages was also very painful, I wanted something I could just program and run. There are a number of API’s out there that will allow you to run requests against web pages and receive XML data back. These tended to return data which needed more massaging and required conversion of XML structures to allow the database to be updated.

I wanted to have a database of ZipCodes which had the Timezones included, I found one at the following link. I did find others but this one appeared to have been updated more recently. I started by importing the sql into a new MySQL database called ziptotz. On reviewing the data I found that the TimeZone was in a format such as ‘America/Alaska’. I needed to take this data and work out the time offset.

My solution was to simply add a new field to the end of each row which would hold the offset from GMT, I could have made it simpler by determining the offset of my current timezone from GMT and subtracting it from the result but the data would then be location specific so I decided offset from GMT would work just fine.

This is the script I used to add the new values.


// connect to the server
$con = mysql_connect("my_host","my_user","my_pwd");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
// select the database
mysql_select_db("ziptotz",$con);
// list the timezones supported by PHP for the US only
$timeZones = DateTimeZone::listIdentifiers(DateTimeZone::PER_COUNTRY, 'US');
// loop through the returned array
foreach ($timeZones as $key => $zoneName ) {
// create a timezone object
$tz = new DateTimeZone($zoneName);
// create a new datetime object using the timezone object time is current
$dateTime = new DateTime("now",$tz);
// create the offset char string
$timeoffset = date_format($dateTime, 'P');
// add the offset to the list of zipcodes
$query = "UPDATE timezonebyzipcode SET offset_gmt = '" .$timeoffset ."' WHERE timezone = '" .$zoneName ."'";
$result = mysql_query($query);
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}
}
// close the server connection
mysql_close($con);

Now I have a full list of ZipCodes with the gmt offset stored in a database table. Now I can use the data in this new table to add a new field to the contact database which will show the time offset allowing me to know when I should be able to call without waking them at 5AM in the morning.

Have fun..

Chris..

May 15

Zend moves support for ZendDBi to alternative supplier.


A recent article in IT Jungle just announced that Zend is going to be moving support and future development for Zend DBi off to another supplier. I did a quick review of the website for Percona and the pricing for their support for MySQL and various add-ons they have created and found the following price page. This means you should now expect to pay for support for Zend DBi separate to any support contract you decide to take with Zend.

This is not a bad move for Aura Equipments and their iAMP server solution, with iAMP you get the MySQL engine built in and support via the forums is still going to be free. You can purchase support for iAMP which will cover all of the installed products (Apache, MySQL and PHP) and have that support in one place. Add to that the ability to include the Easycom product as part of that support contract and it be comes very much a better option than the Zend stack which now requires the support with Percona and Zend plus the no fee based support for the open source XMLSERVICE to provide the same solution.

I am not sure why Zend took the move or how the IBMi community will respond. I do know this is not going to be a bad thing for Aura, who can capitalize on the fact that they provide a single point of contact for the total solution and at a price which is far below that of the total package required to run the various elements for Zend, Percona and the Open Source XMLSERVICE.

You can download and install the iAMP server from the Aura Equipments website.

Chris…