SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is the difference between TIMESTAMP and DATETIME??


    Amy

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that's a good question

    have you read up on them in da manual?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  6. #6
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by amy.damnit View Post
    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."
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by amy.damnit View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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);
    Amy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •