PHP MySQLi \'Commands Out of Sync\': How to Fix the Error?

PHP Commands Out of Sync: Resolving the Dilemma

When working with PHP and MySQLi to retrieve data from a MySQL database, executing multiple prepared statements can sometimes lead to the error, "Commands out of sync, you can't run the command now."

The Cause and Solution

This error occurs when the connection object ($mysqli) has pending results from a previous query. To resolve it, you need to call the next_result() function on the $mysqli object after each prepared statement.

Consider this example:

$stmt = $mysqli->prepare("SELECT id, username, password, firstname, lastname, salt FROM members WHERE email = ? LIMIT 1");
$stmt->bind_param('s', $loweredEmail);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($user_id, $username, $db_password, $firstname, $lastname, $salt);
$stmt->fetch();
$stmt->free_result();
$stmt->close();

while ($mysqli->more_results()) {
    $mysqli->next_result();
}

$stmt1 = $mysqli->prepare("SELECT privileges FROM delegations WHERE id = ? LIMIT 1");
$stmt1->bind_param('s', $user_id);
// This is where the error occurs
$stmt1->execute();

In this code, the error will be thrown when executing the second statement ($stmt1). To fix it, add the following line before executing $stmt1:

$mysqli->next_result();

Additional Tips

• Ensure that you always call free_result() and close() on the $stmt object after each query execution.
• Use separate $stmt objects for each prepared statement if possible.
• If you encounter the error even after trying the above solutions, consider checking for any pending results left in the buffer using mysqli::more_results() and mysqli::next_result().

The above is the detailed content of PHP MySQLi \'Commands Out of Sync\': How to Fix the Error?. For more information, please follow other related articles on the PHP Chinese website!