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

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_Add开发者_如何学编程r', '$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.

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.

@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] 

Check out here: http://us3.php.net/manual/en/function.mysql-query.php In comments, one guy claims that he made it work by setting connection flag to MYSQL_MULTI_RESULTS (131072).

But it would be much better to use mysqli...

mysql_free_result(client->res);

while (mysql_more_results(client->conn))
{
    mysql_next_result(client->conn);
}

This did the charm for me :)

Result sets returned from a stored procedure cannot be fetched correctly using mysqli_query(). The mysqli_query() function combines statement execution and fetching the first result set into a buffered result set, if any. However, there are additional stored procedure result sets hidden from the user which cause mysqli_query() to fail returning the user expected result sets.

Result sets returned from a stored procedure are fetched using mysqli_real_query() or mysqli_multi_query(). Both functions allow fetching any number of result sets returned by a statement, such as CALL.

look at official manual