Hi guys,

Total noob here, trying to figure out how to build a database

Problem
I want two fields in my tablet:
1) showing when a record was created &
2) showing when the record was last updated

#1 I can figure out. #2 is a bugger. I've tried TIMESTAMP, and a few
other types, but whenever I update the record, nothing happens :/

Suggestions?
Thanks

ThomasH wrote:
$time=date("Y-m-d H:i:s");

the data type is DATETIME

I suspect there is a Mysql way to do the same like
update..mytable set last_access=NOW();

Cool, I get that But what do you do if your input doesn't come from
PHP? Lets say it's being manually typed into the database?

In SQL I might make a trigger, that notes the date of last modification,
but I'd sure love for the database to keep track of it for me, no matter
when or how an entry is updated.

Thanks though

ThomasH wrote:

I think it may be even simpler.

On Tue, 20 Sep 2011 20:01:21 +0200, ThomasH wrote:
Section 10.3.1.1 of the manual. The abbreviated answer is that there are
two controls for automatically handling TIMESTAMP columns. 1) you've
figured out ( my_ts_col TIMESTAMP DEFAULT CURRENT_TIMESTAMP ). 2) auto
updating whenever the record changes uses the following format:

my_ts_col TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

turns off auto initialization, but turns on updates on UPDATE. (If
you're using traditional sql, this will also get you an error because a
zero timestamp is an error, and there's another couple of settings that
can cause problems with this as well, but they're rare alone.)

There's some additional quirks that these are *automatically* both turned
on for the first timestamp column in a record layout. If you let the
first column do that, you'll get the behavior of having both turned
on. If you explicitly turn on the DEFAULT version, that turns off the
autoupdate for that column. If you set the column to nullable you'll
ruin the default automatic settings and I think the ON UPDATE as well.

--
I wish there was a knob on the TV to turn up the intelligence. There's a
knob called "brightness", but it doesn't work.
-- Gallagher

On 20-09-2011 23:32, Peter H. Coffin wrote:
Wow, that's great - I think I have something to go on now... I predict
some studying in my future.

Thanks to both repliers

ThomasH wrote:
I thought there was probably a better way...

Hi again,

So I took a look at
http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

and added a column to my db using:

ALTER TABLE MyDB
ADD lastmodified TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;

Still - if I edit the contents of another field in the row,
"lastmodified" remains unchanged.

Have tried updating with both the default 000-00-00 value and one set to
now, to get the ball rolling - no dice :/

Any further advice?
Thomas

On 09/21/2011 02:18 PM, ThomasH wrote:
I think only the first TIMESTAMP field created can get auto
updated... not sure, but itś just as easy to manually trigger it like so:


UPDATE table SET blah = something, blah2 = something2, lastmodified = null

--
Norman
Registered Linux user #461062
-Have you been to www.mysql.com yet?-

On 9/21/2011 2:18 PM, ThomasH wrote:
As others have indicated, it is only the first TIMESTAMP which can
autoupdate like this. However, you can easily create an UPDATE trigger
to do what you want. Add an INSERT trigger and you have everything. It
can even be a separate TIMESTAMP so you can see when it was created and
when it was last updated.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================