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

    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()

      sorry im still not clear.

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

        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

          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..

            try

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

              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.

                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

                  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

                    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.

                      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...

                        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.).

                          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.

                            Write a Reply...