SitePoint Sponsor |
|
User Tag List
Results 1 to 9 of 9
-
Nov 27, 2009, 02:24 #1
- Join Date
- Jul 2006
- Location
- Fionnphort, Isle of Mull, Scotland
- Posts
- 363
- 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
-
Nov 27, 2009, 05:58 #2
- Join Date
- May 2006
- Location
- Amsterdam
- Posts
- 206
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
maybe this will help
http://bugs.mysql.com/bug.php?id=27645#c254993
-
Nov 27, 2009, 06:38 #3
- Join Date
- Jul 2006
- Location
- Fionnphort, Isle of Mull, Scotland
- Posts
- 363
- 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
-
Nov 27, 2009, 06:46 #4
- 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,
-
Nov 27, 2009, 07:02 #5
- Join Date
- Jul 2006
- Location
- Fionnphort, Isle of Mull, Scotland
- Posts
- 363
- 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
-
Nov 27, 2009, 07:04 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Nov 27, 2009, 07:13 #7
- 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;
-
Nov 27, 2009, 07:18 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Nov 27, 2009, 07:29 #9
- Join Date
- Jul 2006
- Location
- Fionnphort, Isle of Mull, Scotland
- Posts
- 363
- 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