Printing out 25 most recently added tables

Question

Hello,

In the code below, I allow users to add tables to a MySQL database. Is there a way to print out the most recent 25 tables added?

Thanks in advance,

John

$table = mysql_real_escape_string($_POST['name']);

$query = "CREATE TABLE `$table` (id INT(11) NOT NULL auto_increment, site VARCHAR(350) NOT NULL, cat1 BIGINT(9) NOT NULL, cat2 BIGINT(9) NOT NULL, PRIMARY KEY(id), UNIQUE (site))";
$result = mysql_query($query) or die(mysql_error());

Answer 1

You can but I'd advise creating a history or audit table of all the table creations (and other relevant activity). For example:

$now = time();
$user = $_SESSION['user'];
$sql = <<<END
INSERT INTO table_history
(table, user, action, action_time)
VALUES
('$table', '$user', 'CREATE', $now)
END;
mysql_query($sql) or die($sql . ': ' . mysql_error());

Also, I'd probably suggest not creating a separate table for each instance you need to use it unless they're going to be really large. Creating another column to identify the user, organization or whatever is often (but not always) a better approach.

Answer 2

select TABLE_NAME
  from INFORMATION_SCHEMA.TABLES
 where TABLE_SCHEMA='your_db_name`
 order by CREATE_TIME desc
 limit 25

This is for MySQL version 5 and above. It will list tables created by your users and you, though.