"Commands out of sync" when call multiple stored procedure - PHP & Mysql

pdidominic
pdidominic used Ask the Experts™
on
Hi,

Everytime a call 2 or more store procedure using mysqli, i got the error "Commands out of sync; you can't run this command now".

Please help me!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
yes, I've test with 2 select query and it works but when i wrap the select query in store procedures. It fails.
is it a bug?
can you post the code ?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Yes, here is my code
I use 2 simple select query:
      SELECT COUNT(*) FROM `tb_key` WHERE `tb_key`.`remainPC` > 0;
and
      SELECT SUM(`tb_key`.`remainPC`) FROM `tb_key`;

and wrap them into procedure:
    CREATE PROCEDURE sproc_countAvailPC ()
    BEGIN
          SELECT SUM(`tb_key`.`remainPC`) as 'availPC' FROM `tb_key`;
    END$$

    CREATE PROCEDURE sproc_countAvailKey ()
    BEGIN
          SELECT COUNT(*) as 'availKey' FROM `tb_key` WHERE `tb_key`.`remainPC` > 0;
    END$$

I attached my php test code. I've switched  to PDO.
$rs = $db->query('call sproc_countAvailPC ();')->fetchAll();	
$db->query('call sproc_countAvailKey();') FROM `tb_key`;');

Open in new window

Author

Commented:
here is my mysqli test code. I use the same example in php manual. The only difference is that i use store procedure instead.
if ($result = $mysqli->query("call sproc_countAvailKey()")) {
    printf("Select returned %d rows.\n", $result->num_rows);
 
    /* free result set */
    $result->close();
}
 
/* If we have to retrieve large amount of data we use MYSQLI_USE_RESULT */
if ($result = $mysqli->query("call sproc_countAvailKey()", MYSQLI_STORE_RESULT)) {
	echo $result->num_rows;
	$result->close();
}

Open in new window

Top Expert 2008
Commented:
Check this user contribution in the PHP manual:

http://php.net/manual/en/mysqli.query.php#65813

Basically, you need to use something like this after each call to a SP:

while($mysqli->more_results()) $mysqli->next_result();

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial