SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    356
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Date-stamping a change on a specific column ?

    My database table has a timestamp column which will show when a record has been changed. Is it possible to have a timestamp linked to a specific column ?

    One of the columns in my table defines whether a record should be displayed at all (i.e. included in any search). I'm looking to record a date when that value has been changed. I'm likely to make that change in phpMyAdmin rather than by using a query (at least initially). I could enter a date in another column manually, but I'm likely to forget.
    Tim Dawson
    Isle of Mull, Scotland

  2. #2
    SitePoint Addict
    Join Date
    May 2006
    Location
    Amsterdam
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  3. #3
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    356
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks. It's interesting, and it would lead me to think the answer to my question is no. However, that thread's more about the merits/demerits of timestamp, responding to any update (of any column). I wanted something specific to one column that wouldn't change if other columns were updated. I'd be quite happy with a timestamp.

    There's not really a problem if I update with a query, as I can insert the current date as a string in any column I want. It's just while using phpMyAdmin that I'll have to remember to do it.
    Tim Dawson
    Isle of Mull, Scotland

  4. #4
    SitePoint Addict
    Join Date
    May 2006
    Location
    Amsterdam
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wouldn't this take care of what you're looking for?

    Code:
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  5. #5
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    356
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks. Yes, if I was doing it with a query I could add a clause like that whenever I changed the 'display' column, and insert the date in another column. But not when using 'phpMyAdmin', as I can't put a function in a column (as per the link you gave in your first reply).
    Tim Dawson
    Isle of Mull, Scotland

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ramasaig View Post
    Is it possible to have a timestamp linked to a specific column ?
    no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    May 2006
    Location
    Amsterdam
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, maybe I'm not fully understanding your question. This is how I currently understand it:

    1. You want a column that will update with the current date when any field in that row has been updated?

    Here's a quick example that works for me via a query or change in phpMyAdmin:

    Code:
    CREATE TABLE IF NOT EXISTS `emails` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(100) default NULL,
      `email` varchar(100) default NULL,
      `updated_at` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by danNL View Post
    Hmm, maybe I'm not fully understanding your question. This is how I currently understand it:

    1. You want a column that will update with the current date when any field in that row has been updated?
    no, only when a specific column has been updated
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    356
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys. I think we've arrived at a definitive answer, which I suspected all along. The documentation rarely tells you what you CAN'T do, so it might have taken ages to reach this conclusion without the forum.
    Tim Dawson
    Isle of Mull, Scotland


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
  •