How to simulate 2 timestamp fields in a MySQL table?

I know you can only have 1 timestamp per table开发者_StackOverflow in mysql. But I need 2. One for when an article was created and one for everytime it's updated. The latter is the one that will be changing most of the time so I decided to make that the timestamp field. For the other one, how can I prepare the time in PHP so that it looks just like a timestamp when inserted into the database:

2011-07-29 03:28:20

And if this string is prepared in PHP, will the usual ways of formatting it work, e.g.:

    $time = strtotime($timestamp);
    $date = date('n/d/Y @ g:i a', $time);

You don't need to involve PHP in this. You can use the following pattern defaulting to NULL on the creation timestamp column, and using an ON UPDATE CURRENT_TIMESTAMP for the updated column.

CREATE TABLE test.table (
  `row_inserted` TIMESTAMP NULL,
  `row_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

This is copied from an example on the MySQL TIMESTAMP docs.

You can use a datetime field in order to store insert time (use now() function of mysql). Use instead a timestamp field to store update time.

create table mytb (
id int not null auto_increment primary key,
article varchar(50),
ins_time datetime,
upd_time timestamp not null default current_timestamp on update current_timestamp
) engine = myisam;

insert into mytb (
article,ins_time) values ('bla bla bla', now());

update mytb set article = 'bla bla' where id = 1

You can use MySQL function NOW() in your INSERT and UPDATE queries.

UPDATE table SET modified = NOW() WHERE id='$id'

You have two options: inside SQL using NOW():

sprintf("UPDATE table SET modified = NOW() WHERE id=%d", $id);

Or using a timestamp created in PHP:

$now = time();
sprintf("UPDATE table SET modified =%d WHERE id=%d", $now, $id);

The first is ideal if you only have one roundtrip to the database: insert all data at once. The second allows you to carry the $now variable around for some time and re-use it. For example if you are inserting several records in a loop: you know for sure that they all have the same timestamp. Another advantage of the latter, is that time-difference between the database-server and PHP do not matter.

NOTE: I used sprintf for a minimum of security against SQL-injection. This is not, however, real and solid security. You will have to deal with cleaning data yourself trough a proper database layer.