Calling two stored procedures in mysqli causes 

问题:

Problem Context

I'm relatively new to most of the languages I'm working in, and I am gradually going back and improving old code. I'm re-writing some old PHP and replacing hardcoded SQL queries (I know) with calls to stored procedures.

The code in my pages is organised in the following order -

$RESULT_one = $connection->query( ... my first query ... ); 
$RESULT_two = $connection->query( ... my second query ... );

if(isset($RESULT_one) & isset($RESULT_two))
{
    // Generate a form using the results from the queries 
    // Loop through $RESULT_one to populate a combo box 
    $RESULT_one->free(); 
    // Loop through $RESULT_two to populate a combo box 
    $RESULT_two->free(); 
}
else 
{
    // Display an error to the user rather than displaying the form 
} 

Problem

After replacing my first query and my second query with CALLs to stored procedures, I receive the error #2014 - Commands out of sync; you can't run this command now.

Steps taken to attempt to solve the problem

• This poster on Stack Overflow was having a similar problem and the accepted answer suggests calling next_result() which solves their issue. However, I want to check that both calls to the database were successful before generating my form, I do not want to half-generate it, as it were.

• I see another question where the asker was having the same issue, and it seems that next_result() is unavoidable because "this is how mysqli works" - why does this work with 'raw' SQL but not with stored procedures? What is the difference? Is there an efficient way to check both queries were successful before generating my form? I'd like to understand what is going on and why the change in behavior.

• I stumbled across documentation for mysqli_store_result() which seems like something which may potentially be useful. I've been reading the comments for this documentation but it isn't at all clear.

• It seems the solution is to call next_result() on the mysqli object between calls, as I learned from this Stack Overflow question.

回答1:

When calling multiple stored procedures, the error Commands out of sync; you can't run this command now can occur unless steps are taken to prevent it.

One way is to call next_result() on the mysqli object after each stored procedure call, as described here.