Hi,
I made two mysql tables in one db, where one is the countrycodes (iso3166) and one is for timezones.
You can then run a natural join on country and timezones.
if > 1 result, show availible timezones in that country
else set timezone to users country
You could define a standard-timezone, but also let your users choose theire country upon registration.
You would then run the join on three tables:
user, iso3166 and tbl_timezone
SELECT TZ FROM user, iso3166, tbl_timezone WHERE user.country = iso_3166_country.iso3166_country AND tbl_timezone.country_code = iso3166_country.iso3166_a2;
ps. I do it like this:
Upon login (when you ahve to query the user anyways, I check if the field TZ in the users table is set.
If it's not set, I run the query above.. (theire country must be set!).
If the query gets one row, I set the field TZ to that TZ result. If it gets more, I populate an listbox and let the user choose his/her region in his/her country.
Then, I use the putenv("TZ=...
But, the mysql is not affected by this!
This means that you have to either run addtime, or something in the mysql db, or do it in php.
I think doing it inside mysql is faster, so I went for that option. You can use addtime (I think it was that I used), and if you have 23:00 and add 2hrs, it automagically finds next day at 01:00.
You can also add negative integers!
How do you know what time to add?
You can use the difference between the original system time and the timezone you used in putenv. (simple math, in other words).
It's very easy actually, though I used many hours thinking about it, before I stumbled across the iso3166 and timezones.
They where not in database-format, so I built the databases up and tested.
It works great!
Since it's so easy, it's more work for me to give the php-source, rather than you making it on your own!
It's just some simple queries, and adding of one or two fields to your users table, also one very simple form for user input, if more than one timezone is availible in that country.
Good luck!
Olav Alexander Mjelde
Admin & Webmaster