Why Is MySQLi Giving a "Commands Out of Sync" Error

Why is mysqli giving a Commands out of sync error?

The MySQL client does not allow you to execute a new query where there are still rows to be fetched from an in-progress query. See Commands out of sync in the MySQL doc on common errors.

You can use mysqli_store_result() to pre-fetch all the rows from the outer query. That will buffer them in the MySQL client, so from the server's point of view your app has fetched the full result set. Then you can execute more queries even in a loop of fetching rows from the now-buffered outer result set.

Or you mysqli_result::fetch_all() which returns the full result set as a PHP array, and then you can loop over that array.

Calling stored procedures is a special case, because a stored procedure has the potential for returning multiple result sets, each of which may have its own set of rows. That's why the answer from @a1ex07 mentions using mysqli_multi_query() and looping until mysqli_next_result() has no more result sets. This is necessary to satisfy the MySQL protocol, even if in your case your stored procedure has a single result set.

---

PS: By the way, I see you are doing the nested queries because you have data representing a hierarchy. You might want to consider storing the data differently, so you can query it more easily. I did a presentation about this titled Models for Hierarchical Data with SQL and PHP. I also cover this topic in a chapter of my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

---

Here is how to implement mysqli_next_result() in CodeIgnitor 3.0.3:

On line 262 of system/database/drivers/mysqli/mysqli_driver.php change

protected function _execute($sql)
{
    return $this->conn_id->query($this->_prep_query($sql));
}

to this

protected function _execute($sql)
{
    $results = $this->conn_id->query($this->_prep_query($sql));
    @mysqli_next_result($this->conn_id); // Fix 'command out of sync' error
    return $results;
}

This has been an issue since 2.x. I just updated to 3.x and had to copy this hack over to the new version.

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

If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.

From here:
http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html

Update

If you make the a variable for the query and paste the variable directly into something like MySQL Workbench you can check the syntax prior to execution.

<?php
            function myConnection(){
              $myConnection = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');
              return $myConnection;
            }   


    function register_user($register_data) { 
        array_walk($register_data, 'array_sanitize'); 
        //Make the array readable and seperate the fields from data 
        $fields = '`' . implode('`, `', array_keys($register_data)) . '`'; 
        $data = "'" . implode("', '", $register_data) . "'"; 
        //Insert the data and email an activation email to the user 
        $query = "INSERT INTO `members` ($fields) VALUES ($data)";
                    $myNewConnection = myConnection();          

                    if($result = mysqli_query($myNewConnection, $query)){ 
        email($register_data['mem_email'], 'Activate your account', "Hello " . $register_data['mem_first_name'] . ",\n\nThank you for creating an account with H Fencing. Please use the link below to activate your account so we can confirm you identity:\n\nhttp://blah.blah.co.uk/activate.php?mem_email=" . $register_data['mem_email'] . "&email_code=" . $register_data['email_code'] . "\n\n - David & Jay "); 
        mysqli_free_result($result);
         return ("Success");
        } else {
            echo $query;
            die(mysqli_error($myNewConnection));
        } 

    }

?>  

Why I am getting the Error Commands out of sync; you can't run this command now

There are result set pending from the query:

mysqli_multi_query($connection,$query);

You need to use/store result before you can proceed with next query after:
Since you look like you don't really care about the first result set, do this after the multi query..

do
{
    $result = mysqli_store_result($connection);
    mysqli_free_result($result);
}while(mysqli_next_result());

Another alternative is to close the connection and starts it again..

mysqli_close($connection);
$connection = mysqli_connect("localhost","username","password","tbl_msgs");

It all depends on your requirements.

MYSQLi - Commands out of sync error

Thank You to @andrewsi for his help - it turns out that having the MYSQLI_USE_RESULT inside SELECT * FROM stats WHERE collated = 0", MYSQLI_USE_RESULT was giving me the error. Removing that allowed me to do my code normally.

Hopefully this helps others that may have the same problem. =)

Why is MySQLi multi_query showing a Commands out of sync; you can't run this command now error

You must use mysqli_store_result() or mysqli::use_result() before you can retrieve the results of the query, or move on to the next result. I believe you are looking for something like this:

public function multiQuery($query)
{
    if (@parent::multi_query($query))
    {
        do
        {
            if ($result = @parent::use_result())
            {
                return $result;
                // this will end the function on the first result encountered.
                // If that is not what you desire, you could create an array of results,
                // and return it after the do-while loop finishes.
            }
            else
            {
                printf("MySQLi error:<br><b>%s</b><br>%s <br>", $this->error, $query);
                return NULL;  // or you could return the error encountered
            }
        } while (@parent::next_result()); 
    }
}

Hope that helps :)

In PHP, MySQL throws Commands out of sync error, while trying to use '$mysqli->multi_query'

You have to fetch all results - for example:

// here: first multi query

// fetch all results
while( mysqli_more_results($link) ){
    $result = mysqli_store_result($link);
    mysqli_next_result($link);
}

// here: second multi query

Some SQL systems in some languages are "lazy". They send query only when you ask for results (for example LINQ in C#). Maybe PHP do the same. It blocks connection waiting for your result fetching.

Mysqli returning error Commands out of sync when trying to do multiple prepared statements

EDIT 2

The reason why you're getting "Commands out of sync error" is because the mysql client doesn't allow you to a new query / sub query when there are still rows being fetched from a previous in-progress query. Commands out of sync

Using example provided. (Might have to change it to your liking)

$i=1;
$champion = array();
$noofspellschamp = array();

$Patch_No = trim($_GET['Patch_No']);

// query 1
$champions = $conn->prepare("SELECT champion FROM champions where Patch_No = ?");
$champions->bind_param('s', $Patch_No);
$champions->execute();
$result = $champions->get_result();


while($data = $result->fetch_assoc()){

    // use data variable as an associative array

    //query 2
    $noofspellschamps = $conn->prepare(
    "SELECT Passive, Q, W, E, R,
            ((Passive != '') + (Q != '') + (W != '') + (E != '') + (R != '')
            ) as NumNotNull
    FROM champions
    WHERE Patch_No = ? AND Champion = ?");

    $noofspellschamps->bind_param('ss', $Patch_No, $data["champion"]);
    $noofspellschamps->execute();
    $result2 = $champions->get_result();



    while($data2 = $result2->fetch_assoc()){
        // other stuff with data2 variable
    }

    $i++;
}

As of PHP 5.3 mysqli_stmt::get_result which returns a result-set object. You can use mysqli_result::fetch_array() or mysqli_result::fetch_assoc(). However, note that this only available with the native MySQL driver though.

In essence:

$stmt = $con->prepare("SELECT id FROM table1"); // first prepared statement
$stmt->execute(); 
$result = $stmt->get_result(); // Gets a result set from a prepared statement
while($data = $result->fetch_assoc()) {  
    // do stuff with first set of data

    $stmt2 = $con->prepare("SELECT * from table2 WHERE id = ?"); // second prepared statement
    $stmt2->bind_param('s', $data["id"]);
    $stmt2->execute(); // execute second statement
    $result2 = $stmt2->get_result();
    while($data2 = $result2->fetch_assoc()) { 

        // do stuff with second set of data
    }

}

---

Related Topics