Two timestamps for update and insert

matt2012

Hi simple one really.

I need to record date updated which a timestamp does, but also the date inserted. Can I do this all from the mysql database, or do I need to have a variable date inserted in the php. The first option appeals to me more.

Matt

mrhappiness

have two timestamp columns and name the first one created and the second last_updated

for inserting don't sepcify any timestamp column

for updating do

SET created = created, last_updated = NOW()

matt2012

sorry im still not clear.

What happens in php what can be set in the mysql end.

mrhappiness

managing the content of your two timestamp columns can be done with mysql, no need for php

you only have to pass the proper sql statements

matt2012

Ok bare in mind im a newbie. Could you spell this out.

If i run this in the sql box of phpMyAdmin I get

SET tbContact.qDate = tbContact.qDate, tbContact.rDate = NOW()

#1193 - Unknown system variable 'tbContact'

If I put Now(), Now, .. in the Default properties for qDate or rDate it simple ignores it.

Defaulting first timestamp to Now and second to 00000..

thanks for your patience..

liquorvicar

try

UPDATE tbContact SET tbContact.qDate = tbContact.qDate, tbContact.rDate = NOW() WHERE etc...

matt2012

This is not a one off operation.

In all the tables I use I want two fields. One that has a record of the date a record was added and never changes, one that stores the date the record was last ammended. I want to do this if possible all in the mysql backend using phpMyAdmin.

please explain step by step

I would be very appreciative.

liquorvicar

when you say you want to do it all via phpMyAdmin, what exactly do you mean? are you not writing your own PHP pages to achieve this?

read mrhappiness' post and MySQL documentation on time/timestamp columns MySQL docs

matt2012

I could do this with php
I could do this as a one off operation in phpMyAdmin
surely in mysql you should be able to set a update timestamp and an insert timestamp on a table row without resorting to client side php code

Sxooter

The best way to do this is with triggers.. That way, no matter what PHP tries to do in the future, it will be the right thing happening in the database.

You could download and start testing MySQL 5.0.3 right now, it's got triggers, or use PostgreSQL, which has had them for ages.

Barring that, you could write a standard access function in PHP that always gets called to do the insert / update etc... and use it religiously. But if it gets bypassed your timestamp might be anything.

csn

Or, write your triggers in PHP (using PostgreSQL of course)!

Sxooter

yeah, that is quite impressive. There's been a long drawn out discussion on -general and -hackers as to whether or not plphp (and other pls) should be in the core...

csn

Do you know what version plPHP is (mainly, is it out of beta - I don't see version info on the site)? Do -general and -hackers feel it's ready for prime-time? I haven't tried it yet, but I'm probably going to try plPHP, plPerl, or something else that offers more functionality than plpgsql (like the ability to send emails, interact with files, etc.).

Sxooter

It's been considered stable for quite some time now. As far as I know there aren't any major features left to add to it.

It's considered quite stable on the -hackers list, most objections to it being in the core are 1: Nothing needs to be in core but pl/pgsql and 2: It has circular BUILD requirements (not rpm installation) that make it hard to maintain.