7

This simple code calls two MySQL procedures, but after the first which returns values, it returns an error on the second query.

NOTE: Running the first or the second on their own will return correctly for each one. So the queries work, just not together.

The full error is: Invalid query: Commands out of sync; you can't run this command now

Any ideas please.

<?php

require_once ('connection.php');
//First Query and Output

$result = mysql_query("CALL C01_Client_Summary_ByAccount(1, '2012-02-27', '2013-03-29');");
if (!$result) { 
die('Invalid query: ' . mysql_error());
}
while($row=mysql_fetch_array($result))
{
echo $row['CommisionPercentage'];
}

mysql_free_result($result); 
//END First Query and Output

//Second Query and Output
$new2 = mysql_query("CALL C01_Client_Summary_ByBetType(1, '2012-02-27', '2013-03-29');");
if (!$new2) { 
die('Invalid query: ' . mysql_error());
}
while($row=mysql_fetch_array($new2))
{
echo $row['Turnover'];
}
//END Second Query and Output

?>
user2162372
  • 79
  • 1
  • 5
  • 1
    Does your first `CALL()` cause 2 resultsets? – Wrikken Mar 12 '13 at 22:45
  • I found a solution to this I needed to change the connection to include ('connection.php'); and then after the first query closed the connection mysql_close($con); and then reopen the connection before the second query include ('connection.php'); – user2162372 Mar 12 '13 at 23:17
  • See http://stackoverflow.com/q/614671/632951 – Pacerier Apr 20 '15 at 11:23

1 Answers1

5

The old MySQL extension for PHP is not working properly with stored procedures. Unfortunately there seams to be no way to execute multiple stored procedures with it. The problem is that the first procedure leaves some buffered result set which cause the second one to fail. You can however use mysqli extension. Here is a nice example on how to do this:

http://www.daniweb.com/web-development/php/threads/234868/error-commands-out-of-sync-you-cant-run-this-command-now

Bojan Dević
  • 1,875
  • 14
  • 24
  • thanks - i found that also - my solution is probably a cheat – user2162372 Mar 12 '13 at 23:21
  • 1
    the solution is to switch to the `mysqli` or `PDO` extensions. Bear in mind that the old `mysql_xxX()` functions are deprecated and not supported; one day you will need to upgrade your PHP version, and if you haven't switched, you'll find your code simply doesn't work; it will be a real pain to fix it when that happens. Switch now while it's easy to do. – Spudley Mar 12 '13 at 23:37