SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
Thread: Keeping track of record changes
-
Oct 11, 2009, 13:35 #1
Keeping track of record changes
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
-
Oct 11, 2009, 13:56 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Oct 11, 2009, 16:01 #3
What is the difference between TIMESTAMP and DATETIME??
Amy
-
Oct 11, 2009, 16:15 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
that's a good question
have you read up on them in da manual?
-
Oct 11, 2009, 16:28 #5
-
Oct 11, 2009, 22:48 #6
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
-
Oct 12, 2009, 02:36 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Oct 12, 2009, 11:08 #8
-
Oct 12, 2009, 14:28 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Oct 12, 2009, 15:16 #10
I tried this be=ut get a SQL error...
Code:CREATE TABLE atimestamp_2 ( id INT PRIMARY KEY AUTO_INCREMENT, justnow DATETIME, created_on DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMP);
Bookmarks