date modified

g7pwx

Hi. this is probably a very newbie question but i did this.

ALTER TABLE my_data ADD date DATE NOT NULL;

looks ok but i want the database to get the date off the server when a record in my_data is added or edited. All this shows is 0000-00-00.

Is there anyway to pull the date from the server into this field?

How is this achieved?

Thanks.

nnichols

Use a TIMESTAMP field instead of DATE. It will automatically update whenever you make changes to the row.

Sxooter

I don't think updates will update a timestamp field unless you set it yourself. And on inserts I think you need a default value of now() or something similar.

In most databases you'd use a trigger to do this.

Sxooter

See my new posting on this board "Postgresql: last modified trigger" for how to do this in a trigger.

csn

The first timestamp field in a table will automatically update itself (MySQL only).

Sxooter

Originally posted by csn
The first timestamp field in a table will automatically update itself (MySQL only).

OK. that's one more thing MySQL has to do to get around not having triggers and such.

g7pwx

Thanks nnicoles that worked great......But..... to be a perfectionist it dispalys it like this wen i call it from the database as $date

20030402110211

can this be split up easily?

so 2003-04-02-11-02-11

Thanks

nnichols

You can either split it in your php code or return different sections of it as separate columns using the various date functions available in MySQL - http://www.mysql.com/doc/en/Date_and_time_functions.html

Check DATE_FORMAT() function

g7pwx

$sql="SELECT company_name, id_num, name, date  FROM company_details order by id_num"

Ok this is how i am pulling it from the database and i get 20030402110214. i understand why that does it that way so i tried below

$sql="SELECT company_name, id_num, name, date('%M %e, %Y')  FROM company_details order by id_num"

Ok obviously wrong but i am sure i am close or near the right track to format it so 2003 04 02.... etc

or am i really to cold on it n no where near ?

Thanks for any advise.

xblue

did you read the DATE_FORMAT section on the manual page nnichols linked to?

syntax:
DATE_FORMAT(date,format)

where date would be the column name, and format the string '%M %e, %Y'

you can alias the value to access it easier lateron:
SELECT DATE_FORMAT(date,'%M %e, %Y') AS fdate

g7pwx

Yes i did follow the link and read and then went to phpmanual.com to look it up from the php side but still couldent make the connection. I knew i was close with the info given.

I got it working now.
Thanks all for the help.