Commands out of sync; you can't run this command now

Juggling multiple result sets

Whether you're dealing with stored procedures or mysqli::multi_query functions, multiple result sets can pop up. Consuming them with a loop and mysqli_next_result() until it returns non-zero is essential. Failed to do this can throw our dreaded error.

do {
    if ($result = $mysqli->store_result()) {
        while($row = $result->fetch_assoc()) {
            // *gulp* all the data
        }
        $result->free(); // Freeing Willy, I mean freeing the result set 😉
    }
} while ($mysqli->more_results() && $mysqli->next_result());


Buffered vs. Unbuffered queries: The Showdown!

Buffered queries lock and load all data into PHP memory, allowing you to roam back-and-forth through the result set. Unbuffered queries, on the other hand, stream data direct from MySQL. They're cool but can leave you with dreaded sync errors. Tool up with mysqli_stmt_store_result() to switch to buffered queries.

Check yourself with SQL Syntax and Parameter Binding

Errors in SQL syntax or parameter bindings, particularly within the LIKE clause, can gift you the dreaded sync error. Always bind your parameters correctly and double-check your SQL syntax. Keep SQL Injection at bay too!

$stmt = $mysqli->prepare("SELECT * FROM table WHERE column LIKE ?");
$searchString = '%'.$searchString.'%'; // we love a well-formatted LIKE clause
$stmt->bind_param('s', $searchString);


Cleaning up after yourself

If you had some fun with functions

Once done with your result set, remember to invoke mysqli_stmt_free_result(). This cleans up all the cruft and preps the statement for another run.

If you have leftovers

Invoke $mysqli->next_result() until there's nothing more in the pipeline. This ensures you haven't left any previous results behind.

while($mysqli->more_results() && $mysqli->next_result()) {
    if ($result = $mysqli->use_result()) {
        // Handle it
        $result->free(); // Not today, memory waste!
    }
}


If your connection handler is a hermit

Recycle your connection handler whenever practical. Establishing a new database connection for every query takes a toll on your resources.

// Let's assume $dbConnection is your good ol' connection handler
$stmt = $dbConnection->prepare($sql); // It's good to be back!


Practical tips to avoid frequent mishaps

Keep your queries and results in sync

You should execute mysqli_stmt_store_result() right after mysqli_stmt_execute(). This fetches all the data and places your result set ready for another round.

The memory saga

Always release memory with mysqli_stmt_free_result() once you are done. This prevents potential errors due to unattended result sets.

Steer away from unnecessary semicolons

Errant semicolons in queries can cause out of sync errors. Mysqli interprets them as separate commands and can get confused.

Error handling: your last line of defense

Your error handling mechanism should be smart enough to catch and throw mysqli errors. Reporting the right error at the right time solves half the job.