How can the issue of "Commands out of sync" be resolved when running multiple MySQL procedures from a PHP script?

Issue of "Commands out of sync" can be resolved by ensuring that each result set from the stored procedures is fully fetched before executing the next query. This can be achieved by using mysqli_free_result() function to free the result set after each query execution. 

// Connect to MySQL database
$mysqli = new mysqli("localhost", "username", "password", "database");

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Execute first stored procedure
if ($mysqli->multi_query("CALL procedure1();")) {
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                // Process results
            }
            $result->free();
        }
    } while ($mysqli->next_result());
}

// Execute second stored procedure
if ($mysqli->multi_query("CALL procedure2();")) {
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                // Process results
            }
            $result->free();
        }
    } while ($mysqli->next_result());
}

// Close connection
$mysqli->close();

Keywords

PHP MySQL Commands out of sync Stored procedures mysqli_next_result

Related Questions