I've heard of on update current timestamp feature in mysql, but I don't think that's what I need. I need to current timestamp stored when new info is inserted or old updated in the same row?
If you don't mind it being a MySQL datetime field, just create a field of type 'timestamp'. The database will automatically update that field whenever the record is changed. If you need it to be a unix timestamp, then create a MySQL trigger to populate a field on update.
I know how to make the field update with the current date, but not with unix time. How would I create a mysql trigger? Never heard of that... Maybe it's better if I insert the time manually each time in the database?
mysql> create table example (name varchar(20), update_stamp int); Query OK, 0 rows affected (0.54 sec) mysql> create trigger set_timestamp_on_insert before insert on example for each row set new.update_stamp = unix_timestamp(now()); Query OK, 0 rows affected (0.15 sec) mysql> create trigger set_timestamp_on_update before update on example for each row set new.update_stamp = unix_timestamp(now()); Query OK, 0 rows affected (0.15 sec) mysql> insert into example (name) values ('David'); sQuery OK, 1 row affected (0.05 sec) mysql> select * from example; +-------+--------------+ | name | update_stamp | +-------+--------------+ | David | 1264708968 | +-------+--------------+ 1 row in set (0.00 sec) mysql> update example set name='Susan' where name='David'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from example; +-------+--------------+ | name | update_stamp | +-------+--------------+ | Susan | 1264708994 | +-------+--------------+ 1 row in set (0.00 sec) Code (markup):
Thanks! Learning so much everyday. This method is better than having the user insert the time? Better as consumes less server resources? Because I can imagine tens of cases where I could use this. Also wondering if the clients IP can be recorded on INSERT.
I don't think it's significantly more or less stressful on the server than putting the timestamp in the SQL using PHP. If anything, probably less stressful. The real win is that this way, you can be absolutely sure that the timestamp will always be maintained, even if you have lots of different bits of PHP code all over the place generating updates. Triggers are very useful for ensuring integrity of database fields, and they make it possible to centralise things easily. Unfortunately MySQL doesn't have access to the IP address of the web user so you'd still need to supply that in your SQL code generated by PHP.