How to display last modified date of a table? 1

[sundance1980]

Hello I am trying to display using php last modified date of a table in mysql. I posted the question to the mysql list however received numberous responses, so now I am really confused and tried a few things that have not worked.

Some say use timestamp
Some say use SHOW TABLE STATUS

In phpmyadmin there is a section that says row statics "Last modified".Is there anyway I can output that to the screen.

Any help would be great.

Take care.

 

[sleipnir214]

The SHOW TABLE STATUS LIKE 'tablename' will give you the information you need in the column "update_time". The query returns data that can be fetched from the server and displayed just as you would any SELECT query.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sundance1980]

Your the best. thanks for you help.

Dan

 

[TonyGroves]

Just to clarify Sleipnir's advice, the update_time field is only updated when the table is physically written to. So, before you do your SHOW TABLE STATUS query, you should do a FLUSH TABLES or FLUSH TABLES tbl1,tbl2 command, in order to write any cached updates to the tables.

-----
ALTER world DROP injustice, ADD peace;

 

[sundance1980]

I guess I dont know what I am doing. Should I do a fetch array then display 'update_time'?

<?php
include "config.php";
$query= "SHOW TABLE STATUS LIKE 'stats'";
$result=mysql_query($query);
	while($rst=mysql_fetch_array($result)) 
	{
	   echo "$rst[update_time]";
	
	}
?>

Should I add another query using Select?

SELECT update_time FROM 'stats' ?

Thanks for you help guys.

 

[sleipnir214]

You should fetch the array and then display 'update_time'. But put code in there to make sure something was retrieved. Also, if your table name is exact, there's no point in your use of a while loop -- only one row will be returned.

Something like:

<?php
mysql_connect ('localhost', 'test', 'test');
mysql_select_db ('test');
$query= "SHOW TABLE STATUS LIKE 'foo'";
$result=mysql_query($query);
if (mysql_num_rows($result) > 0)
{
	$rst=mysql_fetch_array($result);

	print $rst['Update_time'];
}
else
{
	print 'nothing to display';
}
?>

works on my machine.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sundance1980]

I'm so dumb. I had it right I wrote update_time instead of Update_time I did not know it was case sensative.

Thanks sleip