PHP Commands Out of Sync Error

When running multiple prepared statements in PHP/MySQLi, it is possible to encounter the "Commands out of sync, you can't run the command now" error. This occurs when subsequent statements attempt to execute while prior result data remains unprocessed in the buffer.

To rectify this issue, it is imperative to use the mysqli_free_result() function to explicitly release the stored result data before executing the next statement. Additionally, it is crucial to call the next_result() function on the mysqli object after each statement execution to advance the internal pointer to the next result set.

In your specific code example, the error arises during the execution of $stmt1 because the result from $stmt is not cleared. By explicitly calling mysqli_free_result($stmt) and subsequently calling $mysqli->next_result(), you can ensure that the buffer is emptied and the pointer is reset for the next statement.

Modified Code Example:

    $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);
    $stmt1->execute();
    $stmt1->store_result();
    $stmt1->bind_result($privileges);
    $stmt1->fetch();

    $stmt1->free_result();
    $stmt1->close();

By implementing these fixes, you will eliminate the "Commands out of sync" error and ensure that subsequent prepared statements can execute seamlessly.