Getting the date when a database table is last updated

[pradeepjain]

i am using mysql..and i am using php forms ..i need to display the date when the table was last updated for accuracy ..so any idea on this


Thanks,
Pradeep

[nathj]

i am using mysql..and i am using php forms ..i need to display the date when the table was last updated for accuracy ..so any idea on this


Thanks,
Pradeep

Hi Pradeep

The simple solution to this is to have two fields on every table - createDate and editDate. the first is populated when a record is first created. The second is updated every time a record is altered - including first creation.

So when a table update is done the editDate is always set to the current date. Then you simply have to write some basic SQL to find out when a record was last updated or even the last time anything was updated in the table.

If you need some code just give me a shout and I'll draft some basic SQL for you.

Cheers
nathj

[pradeepjain]

as per u i need to add to fields now..ok but for this i need to modify all tables now....is there any other way or should i use the same way

Thanks,
Pradeep

[nathj]

as per u i need to add to fields now..ok but for this i need to modify all tables now....is there any other way or should i use the same way

Thanks,
Pradeep

Pradeep,

I defintely think the extra fields solution is the best way to go. I add these fields to every table I ever build. If you need to do this retreospectivle y it should be possible to work through a list of tables (possibly retreived from the database) and issue an apprpriate ALTER TABLE command on each one to add the two fields.

Cheers
nathj

[ifedi]

Hi Nathj,

Please do provide the example code you promised. I need that kind of thing now.
I know TIMESTAMP fields that reflect INSERT time, but as for reflecting UPDATE and DELETE times, I' not sure I'm now certain about that.
Right now, I can kind of imagine adding timestamp update lines to all update and queries using PHP. But I imagine there's got to be an easier MySQL internal mechanism.
So, can we have it sir?
Thanks in advance.
Ifedi.

[lotus18]

Hi ifedi

If I got your problem, try this

[CODE=php]$today = date("F j, Y g:i a");[/CODE]

This will get the current date : )

Rey Sean

[nathj]

Hi Nathj,

Please do provide the example code you promised. I need that kind of thing now.
I know TIMESTAMP fields that reflect INSERT time, but as for reflecting UPDATE and DELETE times, I' not sure I'm now certain about that.
Right now, I can kind of imagine adding timestamp update lines to all update and queries using PHP. But I imagine there's got to be an easier MySQL internal mechanism.
So, can we have it sir?
Thanks in advance.
Ifedi.

Hi,

To get the the date a particular record was updated the SQL would be:

SELECT editDate FROM table where id=x

The WHERE clause can be anything you like really it just dtermines whcih records are being checked. If you want to select the record most recently updated try something like:

SELECT a.is, max(editDate) as latestEdit FROM table wherex group by a.id, a.editDate

You'll need to play around with this psuedo code to get the where clause and group by clause correct but hopefully this should get you going.

when selecting out dates it is also possible to use date_format in MySQL to get the result in the format you want.

nathj

[pradeepjain]

i finally found the solution to it..


Use SHOW TABLE STATUS and get the value for Update_time column. This method is not applicable on InnoDB tables.

Thanks,
Pradeep

[nathj]

i finally found the solution to it..


Use SHOW TABLE STATUS and get the value for Update_time column. This method is not applicable on InnoDB tables.

Thanks,
Pradeep

Well I've learnt something there and that's always nice so thank you for posting back your final solution.

nathj