PHP - SQL query to get update time from table status

Question

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

$result = mysql_query("SHOW TABLE STATUS 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!

Answer 1

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.

Answer 2

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());
}

Answer 3

Table update time must be taken from table field only.

Answer 4

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;
}