Stored procedure causes "Commands out of sync" on the next query

Question

I am running a query with a mysql stored procedure :

$AddProf_qr = mysql_query("call AddStudent('$d_Pass', '$d_Titl', '$d_Firs', '$d_Midd',  '$d_Last', '$d_Addr', '$d_City', '$d_Stat', '$d_County',  '$d_Zipc', $d_Gend, '$d_Birh', '$d_Phom', '$d_Phoh', '$d_Phoo', '$d_Email', '$d_Webs', '$d_Natn', '$d_Profsn',  '$d_Compny', '$d_Desig', $d_ProfAcc)", $this->c_remote) or die ("first call" . mysql_error($this->c_remote));

I am supposed to get just one result from the call : @@IDENTITY = a number;

$AP_result = mysql_fetch_array($AddProf_qr);
$CurrentSID = $AP_result['@@IDENTITY'];

which works fine. but when i run another mysql update query right after this, it gives an error saying :

Error: 2014 (CR_COMMANDS_OUT_OF_SYNC) Message: Commands out of sync; you can't run this command now

i have tried inserting :

mysql_free_result($AddProf_qr);

but still the same.

The MySQL call executes fine also the rest of the script runs without issues the above is commented out. but they don't run at the same time. My best guess is, the call is doing something that's messing this up.

Answer 1

Your stored procedure is returning multiple resultsets. See this post

Solution?

• Use mysqli_multi_query
• Stop using the ancient mysql library - the i in mysqli stands for "Improved" - with good reason.

Answer 2

@DMin Yes that's would work, but you'll crash the server sooner or later. Just make the math, one resquest to a page that makes 3 * number of procedures to database! Just think about it!

[UPDATE] solution:

$aCategory = array();
$it=0;
$res = $mysqli->multi_query( "call ListCategory();" );
if( $res ) {
  do {
    if ($result = $mysqli->store_result()) { 

        while( $row = $result->fetch_row() ) {
                $aCategory[$it] =$row;
                $it= $it + 1;
        }
        $result->close();
    }
  } while( $mysqli->next_result() );
}

foreach($aCategory as $row){
    echo . $row[0] . " - " . $row[1] . "<br />";
} 

Just wanted to add that you are ready to call the next Routine.

PS: By this way I couldn't use

echo $aCategory['category_id'] ; 
//or 
echo $aCategory->category_id;
//just
echo $aCategory[0]