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

Quote:

Originally Posted by 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

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

Quote:

Originally Posted by 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

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

Expand|Select|Wrap|Line Numbers

$today = date("F j, Y g:i a");

This will get the current date : )

Rey Sean

nathj

Quote:

Originally Posted by 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.

Hi,

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

Expand|Select|Wrap|Line Numbers

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:

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by 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

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

nathj

Getting the date when a database table is last updated

re: Getting the date when a database table is last updated

re: Getting the date when a database table is last updated

re: Getting the date when a database table is last updated

re: Getting the date when a database table is last updated

re: Getting the date when a database table is last updated

re: Getting the date when a database table is last updated

re: Getting the date when a database table is last updated

re: Getting the date when a database table is last updated

Similar PHP bytes

/bytes/development

/bytes/it

/bytes/about