PHP/MySQL and TIME
ok, ive run into a little delima here. I have a database on my website. on one table, i have a profile for each of my members (thier username,password,email etc). im building an "event" table as well, with a PHP script that will allow members to add an event (with data like where the event occurs, what it entails, and most importantly, the time it will take place).
here is the hard part. most of my users are all in different time zones (PST, CST and EST mostly). i need to have this script automatically translate the time the event takes place to that persons 'Local' time. so if i go and make an event for 8:30 PM (in PST), when one of my east coasters looks at that events details, they will instead see '11:30 PM (in EST). i figure i will have to add a new column to the user profile table so people can set thier local time, but im interested (and confused) at how the event script and the database should talk to each other with regards to translating time.
its a wierd problem, i know, but any help you can give me would be much appreciated.
Here's a suggestion:
Add a timezone field for your members. Make it so that they can set their own timezones during member creation.
If an event is 7:00pm pacific time, a member who chose an eastern time zone would read the pacific time + 3 hours, which is 10:00pm.
Here's how I'd go about it.
In the registration form (where you set the username, password, all that good stuff) make a select field for timezone. Something like this:
Then in your php code, do something like:
$query = mysql_query("select * from userTable where username='bob");
$result = mysql_fetch_array($query));
/* No need to alter time if timezone is 1 (pacific)
if ($result[timezone] == 2) $time + 1;
elseif ($result[timezone] == 3) $time + 2;
/* ... Etc, etc ... */
Please do not plain copy and paste this code for it will not work. This is just a demo.. or to better put it, a "pseudocode" to place you on the right track :)