truncate and optimize via php no go bro

[vbthanks]

Hi all,
I am doing a loop out of a temp table, moving some of that data to another table, then deleting the row from the temp table - works a treat. When the temp table is empty, I want to clean it up.

After the temp table is empty and has no rows, I would like to 'truncate' it to make sure cardinality or next autoindex returns to 1. Here lays the weird-dom...
I can truncate this table and it works.
THEN I try to do the same to another table and nothing happens. My applicable code...

// ----------- CLEAN-UP THE TABLES IF EMPTY
$cleanup = ("SELECT batchID from queue");
$result3 = mysql_query($cleanup);
if(mysql_num_rows($result3) == 0) {
mysql_query("TRUNCATE queue");
 }  // <-- This WORKS GOOD!!!!
 
$cleanup2 = ("SELECT batchID from batch");
$clnp = mysql_query($cleanup2);
if(mysql_num_rows($clnp) == 0) {
mysql_query("TRUNCATE batch");
 } <-- THIS BAD 

Nothing happens to the batch table - it still has overhead and i dislike dreds.
Cheers,
Mick

[PHP John]

Try "TRUNCATE TABLE batch" and try changing the first one to the same syntax, "TRUNCATE TABLE queue".

TRUNCATE Syntax

TRUNCATE TABLE table_name
In 3.23 TRUNCATE TABLE is mapped to COMMIT ; DELETE FROM table_name. See DELETE.
TRUNCATE TABLE differs from DELETE FROM ... in the following ways:

· Truncate operations drop and re-create the table, which is much faster than deleting rows one by one.
· Not transaction-safe; you will get an error if you have an active transaction or an active table lock.
· Doesn't return the number of deleted rows.
· As long as the table definition file table_name.frm is valid, the table can be re-created this way, even if the data or index files have become corrupted.
TRUNCATE is an Oracle SQL extension.
MySQL Reference Manual (C) 2002 MySQL AB