Re: getdate - how to format date back from mysql db

Addressed to: Jason <it@onestopent.com.au> php-general@lists.php.net ** Reply to note from Jason <it@onestopent.com.au> Thu, 14 Sep 2000 10:35:53 +1000 > > hi all i have a survey page that is writing a date to a mysql db when the > user fills out the survey....i use $add_date = date("Y-m-d G:i:s"); > > can anyone suggest a fix or another way...i'm sure there must be an > easier way!! > CREATE TABLE or UPDATE TABLE such that your survey time/date field is of type TIMESTAMP, and is the first or only TIMESTAMP field in the table. When you INSERT INTO tablename, don't even refer to the TIMESTAMP field. The database will automagically set it to the current time and date. If you update the table you will have to set the TIMESTAMP to its existing value each time, or it will change to the current time. If you must update the table and dont want to maintain the TIMESTAMP, you have a couple of choices. 1. Have two timestamps, only the first will be changed when the field is updated. You will have to set the second to NOW() when you INSERT the record. I like the two timestamp trick, I call the first Modified and the second Created. Whenever the record is updated the Modified time is set. 2. Change the type to DATETIME and explicitly set it to NOW() when you insert the value. If you are not going to be udpating the records once they are entered, just use the single timestamp and ignore it when you enter data. To retrieve the data do something like: SELECT DATE_FORMAT( somedate, '%D %M %Y at %l.%i%p' ) AS somedate ), ... 2000-09-14 21:05:32 will display as 14th September 2000 at 9.05pm All you have to do in PHP is display what MySQL returns. For more info: http://www.mysql.com/documentation/mysql/commented/manual.php?section=Date_and_time_functions and http://www.mysql.com/documentation/mysql/commented/manual.php?section=Date_and_time_types Rick Widmer Internet Marketing Specialists www.developersdesk.com