MySQL and PHP DROP TABLE based on value in table

Question

I have a database where entries are inserted into tables with a timestamp (an int from PHP's time() not an SQL timestamp). I want to make a query to drop all tables where the greatest timestamp is less than the expiry time (some time from the time the query is executed). Right now I have

$q = mysqli_query(..., "SHOW TABLES LIKE 'prefix%'");
if($q===FALSE) die(mysqli_error(...));
for($row in mysqli_fetch_array($q)){
    $slice = array_slice($row, 0, 1);
    $tbl = array_shift($slice);
    mysqli_query(..., "DROP TABLE `$tbl` WHERE ((SELECT GREATEST (SELECT `time` FROM `$tbl`)) <= $expiry_time)");
}

This gives an SQL syntax error.

What is the correct way to achieve this? Also, is it possible to eliminate the PHP logic and just loop through each table and drop it if necessary all in SQL?

There is no WHERE clause on a DROP TABLE command. Are you sure you want to drop the table and not delete the rows? — AgRizzo, Mar 24 '14 at 20:42
i wager this could be done in a stored procedure using prepared statments in a loop. http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html — captainroxors, Mar 24 '14 at 20:44
I should have looked up the syntax for drop table before posting this. My fault. You are right, it has no where clause. So how would what I want be achieved? — DankMemes, Mar 24 '14 at 20:44

score 1 · Accepted Answer · answered Mar 24 '14 at 20:46

The approach you've tried will not work - you're mixing data definition statements with data manipulation statements.

You could probably do it in a stored procedure in MySQL, but I haven't tried it.

So you're faced with a choice: do it in MySQL via a stored procedure (which, to my mind, kind of hides the implementation) or do it in PHP (where the code will probably be more accessible and understandable).

Note that, in either event, you'll probably need to lock the tables to prevent the table from disappearing between the time when you query it (to find the timestamp value) and the time when you drop it.

Although, on second thought, a "DROP TABLE IF EXISTS..." should be ok, since you want it deleted anyway. — Kryten, Mar 24 '14 at 20:50

Alexander · Answer 2 · 2014-03-25T08:09:41.593

0

I don't think the answer you accepted is correct one. I added in your code some logic, that will give you what you want.

$q = mysqli_query(..., "SHOW TABLES LIKE 'prefix%'");
if($q===FALSE) die(mysqli_error(...));
for($row in mysqli_fetch_array($q)){
    $slice = array_slice($row, 0, 1);
    $tbl = array_shift($slice);
$r = mysqli_query(..., "SELECT MAX(`time`) FROM $tbl HAVING MAX(`time`) <= $expiry_time");
    if ($r) { 
     mysqli_query(..., "DROP TABLE $tbl");
    }
}

edited Mar 25 '14 at 08:09

answered Mar 25 '14 at 06:50

Alexander

3,129
2
19
33

I actually implemented something similar to this yesterday after posting this question. Sorry for not accepting your answer; although both answers are right, I accepted the first one because it was the only one at the time. (This whole question is unnecessary because I could have simply looked up the MySQL doc for DROP, so I'm sorry for wasting your time) – DankMemes Mar 25 '14 at 19:08

MySQL and PHP DROP TABLE based on value in table

2 Answers2