MySQL Last Modified

[noox]

Is there a possibility to request from a mySQL database when data in a table was changed (update, insert, delete) the last time?

Thanx
nòóx

[DCE]

You should be able to use
mysql> select LAST_INSERT_ID
and then work from there to where you want to go

[noox]

Hi DCE!

Thanx, but I do not think that that is what I want.
I do not need the last data inserted, but I need the date data in a table was changed the last time.

Until now I had static HTML pages and at the end of each page a SSI which outputs something like: page last modiefied on 05-05-2001.

Now the data is read from the database with PHP. I can add the same with PHP but then I have the date where the PHP-File was altered the last time - pretty useless.

The thing would be easy too if I had a special tool for altering the data in the table. Then I just have to alter a specific field with the actual date when altering any data.

But I can easily alter the data with phpMyAdmin, so I need not program a special tool. Then I must change the last-modified field by hand. And I'm sure I will forget to do this all the time ;-)

So I wanted to know if mySQL had a internal value where it holds the date of the last modification of a table. And if this value is readable by SQL.

I have not looked how the timestamp values in mySQL work, but if they work like in MS SQL Server I could add one to the table and it will be updated automatically on each update or insert. But if that is the only option I would prefer an extra table where I write the last modified dates for each table per hand.

I think it would be cool in general if you could select the date of the last change of a table. Could be usefull for many web pages.

nòóx

[tribby]

im pretty sure (but not entirely sure) that the date modified is not stored. you're probably best off just using a timestamp (http://www.mysql.com/doc/D/A/DATETIME.html)

[noox]

Thank's for comments.

I will probably make an extra table, where I write the last-modified values for the pages per hand.

A timestamp field in the table which holds the data is not very usefull here, because I alter the table per hand (myPHPAdmin). So I would have to alter the timestamp field also per hand...

The other idea would be to directly access the properties of the files in which the tables are stored. At the Win32 version I saw that there are 3 files per tables.

It seems that mySQL uses the file system to access the data, so the last modified dates of the files will be set on every change of the data. (Maybe not only when changing data??)

But I think that this would not be very clean programming. And I don't know if we here have file access to the mySQL database - probably not.

nòóx

[noox]

show table status
show table status like "table%"
show table status like "tablename"

can be queried like select with mysql_query() from PHP.
Fetch "Update_time" from the Result: http://www.mysql.com/doc/S/H/SHOW_TABLE_STATUS.html

nòóx