Timestamp?

lukeurtnowski

I have a guestbook on my site. It uses a mysql database and holds names, email and a message. I'm trying to modify it so that it will also hold the date/time that reecord was submitted. So I want and created a date field of type TIMESTAMP to hold these values, and adjusted my php script to add another field. Now, when the guestbook is filled out, in my new field, all I get is a bunch of 0s. Why is this?

toplay

Well, don't forget to include the new column when you insert the row. Example:

INSERT INTO table_name (name, email, message, ts_column) VALUES ('value1', 'value2', 'value3', NOW())

hth.

mrhappiness

the new column is the first column of type TIMESTAMP?

or is it maybe DATE, TIME, DATETIME?

which mysql version do you use?

lukeurtnowski

I'm not sure which version of mysql my server in sacramento is using, but the type is TIMESTAMP (screenshot). Is now() the function mysql uses to record the date/time in that field? Also, do I use the DATE_FORMAT() functon to read that field when I display it, and how do I display it like this?
Jane, 13, 2007. 12.24am
Thanks

toplay

Yes, you can use NOW(), CURRENT_TIMESTAMP(), or LOCALTIMESTAMP() (if you have MySQL version 4.0.6+). When you update your row, use it too. Don't rely on MySQL doing it for you.

You can find out what version of MySQL you're using by doing:

SELECT VERSION()

You should not use "date" as a column name since that's a reserved MySQL keyword. I recommend that you change the name of that column to something else to avoid confusion, or you must surround/enclose it in backtick marks. So, to select the date in the format you want, use:

SELECT DATE_FORMAT(date, '%M, %e, %Y. %h.%i%p') AS formatted_date FROM guestbook

It's all in the manual:

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

http://dev.mysql.com/doc/mysql/en/timestamp-pre-4-1.html

http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html

🙂

lukeurtnowski

k, thanks