Keeping track of record changes

[amy.damnit]

Hi all!

Okay, I am new to PHP and MySQL, and here is an embarrassingly simple question since I am very rusty on database stuff...

I want to keep track of when a record was created and updated.

I thought I read somewhere that if you use a certain Data-Type that MySQL will automatically put a TimeStamp in a row anytime something changes.

Is this true?

What is the best way to set things up in PHP (i.e. code) and MySQL (i.e. Data-Types and SQL) to keep track of Row Created and Row Changed info??

Thanks,


Amy

[r937]

the first TIMESTAMP column in the table will be updated automatically for any change to the row

for created, you would use DATETIME and set that yourself

[amy.damnit]

What is the difference between TIMESTAMP and DATETIME??


Amy

[r937]

that's a good question

have you read up on them in da manual?

[amy.damnit]

that's a good question

have you read up on them in da manual?

I did and that is why I am asking!

When I read up on them in the past I wasn't sure what the distinction was.


Amy

[amy.damnit]

I would really like to find an easy way to put a timestamp in both "CreatedOn" and "UpdatedOn" fields in my tables.

Apparently you can only have one TIMESTAMP field set per table that updates automatically, however I found an article that talks about a "hack" to trick MySQL into performing as most people expect.

Here is the article.

Any experts out there have an opinion on using this approach??

It looks like it will work, but some MySQL gurus' opinions are welcome!!

Thanks,


Amy

[r937]

It looks like it will work, but some MySQL gurus' opinions are welcome!!

in the last paragraph, the author of that article says "I would consider it an undocumented hack" and "Use at your own risk."

[amy.damnit]

in the last paragraph, the author of that article says "I would consider it an undocumented hack" and "Use at your own risk."

Right, but that doesn't mean it won't work or isn't a sound approach...

Based on your experience, what do you think of his approach?

Can you think of a better approach to get the same end effect?


Amy

[r937]

Can you think of a better approach to get the same end effect?

i would use a DATETIME column for the created datetime, set it with CURRENT_TIMESTAMP when inserting the row, and then never touch it again

i would use a TIMESTAMP column for the updated datetime, such that it is the first TIMESTAMP column in the table, and then never touch it again

[amy.damnit]

I tried this be=ut get a SQL error...

CREATE TABLE atimestamp_2 (
id INT PRIMARY KEY AUTO_INCREMENT, 
justnow DATETIME, 
created_on DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
updated_on TIMESTAMP);

Amy