Updated Timestamp field in MySQL through PHP

Question

All,

I have a database table in mysql with a field that is of "TIMESTAMP" type. I need help writing the SQL query to update the field with the current timestamp.

UPDATE tb_Test set dt_modified = ?????

Thanks,

Answer 1

Use:

UPDATE tb_Test 
   SET dt_modified = CURRENT_TIMESTAMP
 WHERE ? -- if you don't specify, ALL dt_modified values will be updated

You can use NOW() instead of CURRENT_TIMESTAMP, but CURRENT_TIMESTAMP is ANSI standard so the query can be ported to other databases.

Answer 2

ALTER TABLE tb_Test MODIFY COLUMN dt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Now whenever any field is changed the dt_modified will be updated by the special trigger.