Thursday, 12 April 2012

How to get rid of "Commands out of sync, you can't run the command now" error in mysql php

I wrote a stored procedure for a table, and after that i executed queries for same table in one php function, But i am getting error :
Error in db : Commands out of sync, you can't run the command now..
I tried mysqli: multi_query also instead of mysqli:query, but i got null output. 
So after some effort, i found the solution.
"Commands out of sync", is caused by unused result sets left over by the db procedure. When you call your first procedure, the result sets are buffered until you use them. 
 In that case, it is required to free the buffered result set as mysql uses unbuffered queries by default.
So use following code to overcome this error :
if (mysqli_multi_query($link, $sql)) {
    do {
        if ($result = mysqli_store_result($link)) {
            while ($row = mysqli_fetch_array($result)) {
    } while (mysqli_next_result($link));
Use "mysqli_multi_query" instead of "mysqli_query".