Calling two stored procedures in mysqli causes 'Commands out of sync' error

Question

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.

Answer 1

I was struggling with this error in both phpmyadmin and running queries within php and simply calling next_result() as suggested didn't work for me. Coming from MS-SQL to MariaDB, I needed to alter my stored procedures to avoid using straight "SELECT" queries to populate variables and instead use "SELECT ... INTO" queries. Normal SELECT statements echo the selected data to the caller in a separate result set. I believe you then need to call "next_result" once for each SELECT statement that was executed in your stored procedure before you can call a new procedure. "SELECT ... INTO" doesn't echo any data which greatly improved speed and avoided this error.

Answer 2

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.