"Commands out of sync; you can't run this command now" - Caused by mysqli::multi_query

Question

I am running multiple deletes through mysqli::multi_query and it is messing up the next query in line. The following error is being thrown.

Error - SQLSTATE HY000. 
Sql error: Commands out of sync; you can't run this command now

Do I need to somehow clear the multi query so it doesn't mess with my next query? What is the cause of this error?

And this is how I am running my multi query:

function deleteSomeTables($args) {
    $sql = 'delete 1;delete another;';
    if ($database->multi_query($sql)) {
        return true;
    } else {
        return false;
    }
}

I am using a recent version of Xampp on windows 7

Answer 1

By using mysqli::multi_query you are firing off queries, but you need to handle the results of those queries before you move on. The documentation page describes the various ways of handling the results. Once handled, you will be able to perform other queries fine.

The error message you are encountering is actually explained a little better on the page for mysqli::query (although bear in mind that mysqli::query would not return a result object in this instance, as you are doing a delete).

You could of course change multi_query to multiple single queries, I don't know what the pros/cons of each approach are.