Error: Commands out of sync; you can't run this command now

Q:

i m getting this bug.. and i still cant understand why this happening..??

here is my php code

<?php
$query1 = "call new_user('$cardDigits','$cardNo','$amount','$traiff','','','','','$creator',@_lastname,'$customer','$firstName','$email','0','0')";

$result1 = mysql_query($query1) or die('query_error'.''.mysql_error());

$lastname = mysql_fetch_row($result1);

// Generate New User

$query2 = "genrate_user('$lastname[0] ','$creator')";

echo $query2; 

$result2 = mysql_query($query2) or die('query_error1'.''.mysql_error());

 Procedures are working fine..!!! 

1st Procedure generate the $lastname
which is the input parameter of second Procedure..!!!!!

when i print or echo the 2nd procedure.. its run fine at mysql.. but through php its throughing error Commands out of sync; you can't run this command now

Help me guys..!!!

 

 

Posts:

1.

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.

 

 

Q:

If it only matters to free the buffered result set..!!!!

Can i use mysql_free_result() or is there any other way to do this thing without using mysqli..??? 

2:

As far as I know, there is no way for the old MySQL extension to execute multiple procedures. Not without using multiple connections, which is a horrific idea.
You would have to be able to fetch multiple result sets, and I don't see a way to do that with the mysql_ functions. It would need to implement the mysql_next_result() function of the MySQL API.

Like I say, the mysql extension was not built to be used with procedures. It was built for MySQL 3, but stored procedures weren't introduced until MySQL 5. The Improved MySQL extension was created to address these sort of incompatibilities.

Any specific reason you do not want to switch over?

Q:

i dont want to switch to mysqli because it is most creepiest thing in php according to me..!!! i wasted hours to install it.. but was unable to that..!!!

i uncommented the extension=php_mysqli.dll in php.ini

also check mysqli dll files in ext folder..

but still i m unable to install mysqli...

every time i got this error Class 'mysqli' not found

3:

Well, I don't know about XAMPP as such, but the process of installing PHP extensions is fairly simple:

1. Put the DLL into the ext/ directory.
2. Add/Uncomment the "extension=xxx.dll" line in the config.
3. Restart Apache.
4. ... and your done.

If that fails, the most common causes are:

• You are editing the incorrect php.ini file.
• Windows can not find the ext/ directory. Or, more accurately, it doesn't know it is supposed to look in the PHP directory.
• Prerequisites for the extension are missing. (Shouldn't be the case for you, seeing as the other MySQL extension is working.)
• You are using Windows! (Joking... kind of ;-)

For the first two, the solution is usually to put the PHP directory into the PATH variable.

 Q:

That's odd... I just installed XAMPP 1.7.2 on my Windows test box and the mysqli extension was enabled by default.

4:

Yeah it is default uncommented
stupid of me..!! actually i m using Nusphere at my php IDE..!!!
i dont know tht Nushpere also have php.ini.. in it is commented mysqli.dll

i un comment that and now its working..!!

i have a question regarding mysqli...!!!
if i made my config file in mysql...!! than can i use mysqli functions..!!!
i mean is it necessary to make connection with mysqli extension to use mysqli function..!!!

Q:

Yuppy..!!! i got the output finally..!!!

thanks to u bro..!!!

but in coding i made two config file.. one with mysql other with msqli..!!!

<?php
$query1 = "call new_user('$cardDigits','$cardNo','$amount','$traiff','','','','','$creator',@_lastname,'$customer','$firstName','$email','0','0')";
$result1 = mysqli_query(,$mysqli,$query1) // here i made change
$lastname = mysqli_fetch_row($result1); // here i made change
 
// Generate New User
$query2 = "genrate_user('$lastname[0] ','$creator')";
echo $query2; 
$result2 = mysql_query($query2) or die('query_error1'.''.mysql_error());

 thats it..!! 

i know its a bad pratice..!! but i will improve it..!!
can u give link to basic tutorial of mysqli..?

i will appreciate that.!!

 

 

 

<!-- google_ad_section_end -->