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

[sam023]

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

here is my php code

$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..!!!

[sam023]

strange no replies uptil now..!!

is this error occur rarely.?? or no one knows about it.??

[Atli]

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.

[sam023]

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..???

[Atli]

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?

[sam023]

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

i m using XAMPP 1.7.2 for windows..!!!

[Atli]

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.
See this page for details on how to do that.

[Atli]

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

[sam023]

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..!!!

[sam023]

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

thanks to u bro..!!!

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

$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.!! :)

[Atli]

I'm glad you got it working :)

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

That's actually pretty bad. Really bad, to be honest :/
You've got two open connections now, which is just a waste.

I strongly recommend you try to use the function I posted in my first post, or at least use the mysqli functions to do both queries, like:

// Call the first procedure
$result1 = mysqli_query($dbLink, "CALL `TestProc`(1);");

// Do something with the result
// etc...

// Free the results up for the next query
mysqli_free_result($result1);
while(mysqli_more_results($dbLink) && mysqli_next_result($dbLink)) {
    $dummyResult = mysqli_use_result($dbLink);
    if($dummyResult instanceof mysqli_result) {
        mysqli_free_result($dbLink);
    }
}

// Call the next procedure
$result2 = mysqli_query($dbLink, "CALL `TestProc`(2);");
// etc...

This way you can drop the old mysql connection but still do both procedures.

can u give link to basic tutorial of mysqli..?

There is hardly any need for a tutorial like that. The mysqli extension was designed to replace the old mysql extension very easily, and almost everything else you need to know can be found in the manual . Every function is documented there, with loads of examples and user submitted comments.

But to replace the old mysql functions, there are only two things you really need to know:

#1 The way you connect to a database is different:

$mysqli = new mysqli("localhost", "my_user", "my_password", "db_name");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

#2 You need to pass the database object (the $mysqli in the previous example) into the query function as the first parameter:

mysqli_query($mysqli, "SELECT something");

Knowing those two things, you should be able to code almost exactly as you would have using the old mysql extension. The function names are almost identical, except for the added "i" ;-)

There are of course a few new features that you can use, now that you have upgraded.
Like:

• OOP. The mysqli object is in fact an object, which allows for OOP style syntax. Just pick any mysqli function in the manual and check out the OOP examples and see the difference.
• Prepared statements (See the examples). Eliminates the risk of SQL Injection, which is one of the biggest security risks for PHP applications.
• Improved speed! Nuff said.
• Execute multiple queries at once, via the mysqli::multi_query method.
• Transactions. Gives you the ability to execute a series of queries, but only committing the changes to the database when you are ready, or rollback the changes in case of a problem.

There is probably more that I'm not remembering right now :-]

[sam023]

thanks a load for such a useful info.!! :)

u rock.. :P