CODEWITHSUNDEEP
phpmysqltimestamp
Alex
Alex

Reputation: 9265

Update mysql timestamp

I have a table with a field:

'last_modified - timestamp NOT NULL'

I have tried 

$sql = sprintf("UPDATE %s SET timestamp=now(), %s WHERE id='%s'", $table, $implodeArray, $_POST['id']);

and it still doesnt seem to work. How can I make the timestamp update when I update or insert by table from a script?

Also heres a sample of the scripts output:

UPDATE about SET timestamp=now(), page_header = 'Page header goes here', sub_header = 'Sub header goes here', content_short = 'This is where a short description of the about page goes', content = 'This is where the full content description goes' WHERE id='1'

Upvotes: 3

Views: 20265

Answers (4)

Ivan Buttinoni
Ivan Buttinoni

Reputation: 4145

May be I misread,but your column name is last_modified (typed timestamp), so the query should be:

UPDATE about SET last_modified=now(), 
page_header = 'Page header goes here', 
sub_header = 'Sub header goes here', 
content_short = 'This is where a short description of the about page goes', 
content = 'This is where the full content description goes' 
WHERE id='1'

Upvotes: 9

Toms Kovkājevs
Toms Kovkājevs

Reputation: 11

If you have something like Framework that does not allow to "now()" this field - just make field to NOT NULL and set PHP variable (model->field) to null just before saving..

Upvotes: 0

Germann Arlington
Germann Arlington

Reputation: 3353

If you want to update the timestamp every time something happens to the record (i.e. last modified date/time) then it is best to leave it fro the DB. You can not rely on default value in field definition because it will work only on INSERT (when the record/field is created), use SET last_modified=GETDATE() [or GETUTCDATE()] the actual functions may differ depending on your actual SQL server.

And "Ivan Buttinoni" is absolutely correct and nobody, including me, noticed it before - your field name is wrong, but the above comments still apply.

Upvotes: 0

Harald Brinkhof
Harald Brinkhof

Reputation: 4455

if you use a reserved word as your table or columnname then you need to quote it

$sql = sprintf("UPDATE %s SET `timestamp`=now(), %s WHERE id='%s'", $table, $implodeArray, $_POST['id']);

in this case you used timestamp as a columnname so you needed to quote it

To update timestamps automatically you can rely on mysql itself if you only have 1 timestamp column or you can add ON UPDATE CURRENT_TIMESTAMP when defining your timestamp column, for more info see TIMESTAMP initialisation

Upvotes: 1

Related Questions