Hey.
The old MySQL extension was never built to run procedures, even tho it *can* be used to do so. You should be using the
Improved MySQL extension if you are planing to use features like Stored Procedures.
The problem you are facing, "Commands out of sync", is caused by unused result sets left over by your procedure. When you call your first procedure, the result sets are buffered until you use them. However, you only use one set, and you didn't even free it before moving on to the second query. You need to free the buffered result sets before moving on:
It is best to create a function, or a method, to do this. No need to repeat the code over and over.
For example:
<?php
/**
* Calls a Stored Procedure and returns the results as an array of rows.
* @param mysqli $dbLink An open mysqli object.
* @param string $procName The name of the procedure to call.
* @param string $params The parameter string to be used
* @return array An array of rows returned by the call.
*/
function c_mysqli_call(mysqli $dbLink, $procName, $params="")
{
if(!$dbLink) {
throw new Exception("The MySQLi connection is invalid.");
}
else
{
// Execute the SQL command.
// The multy_query method is used here to get the buffered results,
// so they can be freeded later to avoid the out of sync error.
$sql = "CALL {$procName}({$params});";
$sqlSuccess = $dbLink->multi_query($sql);
if($sqlSuccess)
{
if($dbLink->more_results())
{
// Get the first buffered result set, the one with our data.
$result = $dbLink->use_result();
$output = array();
// Put the rows into the outpu array
while($row = $result->fetch_assoc())
{
$output[] = $row;
}
// Free the first result set.
// If you forget this one, you will get the "out of sync" error.
$result->free();
// Go through each remaining buffered result and free them as well.
// This removes all extra result sets returned, clearing the way
// for the next SQL command.
while($dbLink->more_results() && $dbLink->next_result())
{
$extraResult = $dbLink->use_result();
if($extraResult instanceof mysqli_result){
$extraResult->free();
}
}
return $output;
}
else
{
return false;
}
}
else
{
throw new Exception("The call failed: " . $dbLink->error);
}
}
}
?>
Which you could use like:
<?php
header('content-type: text/plain');
$dbLink = new mysqli('localhost', 'usr', 'pwd', 'dbname');
// Execute the first call
echo "\n--- FIRST CALL ---\n";
$result = c_mysqli_call($dbLink, 'TestProc', "2, 'second param'");
if($result) {
echo "Output: \n";
foreach($result as $_row) {
echo " " . $_row['something'] . "\n";
}
}
// Execute the second call
echo "\n--- SECOND CALL ---\n";
$result = c_mysqli_call($dbLink, 'TestProc', "3, 'second param'");
if($result) {
echo "Output: \n";
foreach($result as $_row) {
echo " " . $_row['something'] . "\n";
}
}
?>
Hope that helps.
Offline 
Marked 
