PHP - SQL query to get update time from table status

This is my php code (I already have a connection to the db):

$result = mysql_query("SHOW TABLE S开发者_如何学PythonTATUS FROM mydb LIKE 'mytable';");
     while ($array = mysql_fetch_array($result)) {
          $updatetime = $array['Update_time'];
     }

echo $updatetime;

I get:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource.

I am running MySQL 5.0.89 and PHP5.

I do not want to add a new field to the table... I want to use the table status...

Any help?

Thanks!

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource.

You did not supply the right resource to mysql_fetch_array. Also, I believe you have your $array and $result variables mixed up, and note the my_database reference which I'll explain below.

$array = mysql_query("SHOW TABLE STATUS FROM my_database;"); <--here
     while ($array = mysql_fetch_array($result)) { <--$result is undefined.

Should be instead

$result = mysql_query("SHOW TABLE STATUS FROM my_database;");
     while ($array = mysql_fetch_array($result)) {
          $updatetime = $array['Update_time'];
     }

echo $updatetime;

As of now, your MySQL syntax is not correct in regards to SHOW TABLE STATUS you need to reference your database after your FROM clause.

The show table status from should be followed by database name not table name:

SHOW TABLE STATUS FROM <DATABASE NAME>

Also every time you execute a query, you should always check its return value before you go ahead and start using the result object.

$res= mysql_query("SHOW TABLE STATUS FROM DB;");
if(! $res) {
 die('Query failed'.mysql_error());
}

Table update time must be taken from table field only.

Figured it out. Not sure if this is the perfect way, but it works...

$result = mysql_query("SHOW TABLE STATUS FROM mydb LIKE 'mytable';");
foreach ($result as $data) {
    $updatetime = $data->Update_time;
}

echo $updatetime;

You can then parse the raw timestamp with substr.

P.S. As I was doing this for Wordpress, I am including the code specifically for working in the Wordpress DB...

global $wpdb;
define('FOO_TABLE', $table_prefix."footable");

$result = $wpdb->get_results("SHOW TABLE STATUS LIKE '".FOO_TABLE."';");
foreach ($result as $data) {
  $updatetime = $data->Update_time;
}