0

I'm trying to execute a DROP TEMPORARY TABLE statement. I've run the statement using MySQL Workbench and it works there. I've made sure I'm successfully logged in as the same user in both PHP and MySQL Workbench.

I've tried using both mysqli::query() and mysqli::prepare then mysqli::execute() methods. Neither works when I try to execute the DROP TEMPORARY TABLE statement. However, both work when I execute either an INSERT or SELECT statement.

This code doesn't return error, but also doesn't execute in the database:

public function executeSQL()
{       
    // SQL statement using query method
    if (!$this->mySQLi->query("DROP TEMPORARY TABLE IF EXISTS TIME_INTERVAL_DATA") ) 
    {
        $this->writeSQLErrorToLog("Query method returned an error.");
    }
    if(!$this->mySQLi->commit()) 
    {
        // Committing the transaction failed.
        $this->writeSQLErrorToLog("Commit method returned an error.");
    }

    // SQL statement using prepare and execute methods
    $stmt = $this->mySQLi->prepare("DROP TEMPORARY TABLE IF EXISTS TIME_INTERVAL_DATA");
    if($stmt) 
    {
        if(!$stmt->execute())
        {
            // Execute method returned an error.
            $this->writeSQLErrorToLog("Execute method returned an error.");
            $this->throwMySQLErrorAndCloseStatement($stmt);
        }
    } else {
        // Prepare method returned an error.
        $this->writeSQLErrorToLog("Prepare method returned an error.");
        return;
    }
    // ...
}

However, the following code does execute in the database:

$stmt = $this->mySQLi->prepare("INSERT INTO TABLE1 (ID) VALUES (1)");
if(!$stmt->execute()) {
    // Return the MySQL Error message and number
    $this->throwMySQLErrorAndCloseStatement($stmt);
}

I've been reading as many of the MySQL and PHP docs as I can find. I've read and re-read the docs on all of the above mysqli methods. And I've read about and tried variations of using mysqli::escape_string(), but when I use it an error is returned. I've also tried many variations of different SQL statements.

In a nutshell, all of the INSERT and SELECT statements work. But, the DROP TEMPORARY TABLE statement never works and never returns an error. I'm really scratching my head on this one. Any help would be much appreciated!!

Mike
  • 1
  • It does not exist in that session probably. Temporary tables are scoped to your session only, so if you disconnect & reconnect, it's gone anyway. Are you checking with the _same_ connection (not in another request) that the tables exist? And are they in fact, temporary? – Wrikken Aug 14 '14 at 23:38
  • throwMySQLErrorAndCloseStatement(). :O that is quite a function name. – Devon Bessemer Aug 14 '14 at 23:54
  • Thanks Wrikken, you were spot on!! I was actually trying to execute 3 statements. The drop temp table was only the first. I didn't realize temp tables were limited in scope the session. After you posted your comment, I put back in the other two SQL statements and it's working now. Thanks so much!!! If you want to post an answer, I'll mark it as the correct one so you can get the points for it. – Mike Aug 14 '14 at 23:59
  • And yah Devon, I'm an iOS guy, so I got used to verbose function names. I guess it kind of stuck with me. lol :-) – Mike Aug 15 '14 at 00:01

0 Answers0