Why is mysqli giving a "Commands out of sync" error?

mysqli database
mysqli connect
mysqli_query not working
mysqli example
search php mysqli
new mysqli
mysqli vs mysql
mysqli download

I am trying to run the following.

<?php

$db = mysqli_connect("localhost","user","pw") or die("Database error");
mysqli_select_db($db, "database");

$agtid = $_POST['level'];

$sql = sprintf("call agent_hier(%d)", $agtid);

$result = mysqli_query($db, $sql) or exit(mysqli_error($db));

if ($result) {
    echo "<table border='1'>
        <tr><th>id</th>
        <th>name</th>
        <th>parent_id</th>
        <th>parent_name</th>
        <th>level</th>
        <th>email</th></tr>";

    while ($row = mysqli_fetch_assoc($result)) 
    {
        $aid = $row["id"];
        $sql2 = "SELECT * FROM members WHERE MEMNO = '$aid'";
        $result2 = mysqli_query($db,$sql2) or exit(mysqli_error($db));

            while ($newArray = mysqli_fetch_array($result2)) {
                $fname = $newArray['FNAME'];
                $lname = $newArray['LNAME'];
                $mi = $newArray['MI'];  
                $address = $newArray['ADDRESS'];    
                $city = $newArray['CITY'];  
                $state = $newArray['STATE'];    
                $zip = $newArray['ZIP'];
                            $kdate = $newArray['KDATE'];
                $date = abs(strtotime(date('m/d/Y')) - strtotime(date($kdate))) / (60 * 60 * 24);
            }

        echo sprintf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>",
            $row["id"],$row["name"],
            $row["parent_id"],$row["parent_name"],
            $row["level"],$row["email"]);
    }

    echo "</table>";
}

mysqli_free_result($result);
mysqli_close($db);

?>

If I remove lines from:

  $aid = $row["agent_id"];

to....

  $date = abs(strtotime(date('m/d/Y')) - strtotime(date($kdate))) / (60 * 60 * 24);
  }

everything will work fine. If not, I get the following error:

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

In researching, I think it could be due to multiple MySQLi queries run at the same time, in which using mysqli_multi_query but for all the samples and general data in the guide does not seem to be applicable.

Any ideas?

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, Extension (MySQL Improved) is a relational database driver used in the PHP scripting language to provide an interface with MySQL databases. Stack Overflow Public questions and answers; Teams Private questions and answers for your team; Enterprise Private self-hosted questions and answers for your enterprise; Talent Hire technical talent

Simply, You have to call mysqli_next_result($db) , after mysqli_free_result is called.

mysqli_free_result($result); mysqli_next_result($db) mysqli_close($db);

Deprecated features in PHP 5.5.x - Manual, extension doesn't. Things like prepared statements, multiple statements, and transactions on top of my head. 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.

simply call this function :

$this->free_result();

function free_result() {
        while (mysqli_more_results($this->conn) && mysqli_next_result($this->conn)) {

            $dummyResult = mysqli_use_result($this->conn);

            if ($dummyResult instanceof mysqli_result) {
                mysqli_free_result($this->conn);
            }
        }
    }

Which method is more secure between MySQLi and PDO, The MySQLi Extension (MySQL Improved) is a relational database driver used in the PHP scripting language to provide an interface with MySQL databases. Mysql not connecting Giving HTTP 500 But Mysqli is Connecting. Posted June 1, 2018 915 views. I don;t know why mysql connection or code is not working. So please

You have to close previous connection hold by Stored Procedure. Instead of closing connection each time, you can simply use :

mysqli_next_result($conn);

Difference between mysql & mysqli, Performs a query against the database. For non-DML queries (not INSERT, UPDATE or DELETE), this function is similar to calling mysqli_real_query() followed by  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.

If you are also making a few calls to stored procedures, and facing the aforementioned error, I have a solution for you, Mr. Wayne.

IMHO, Somewhere down the line, the CALL to Stored Procedure actually messes up the connection. So all you have to do is reset the database connection handle.

You could even have a function sitting in your config file doing just that for you, and all you do is call that function once before making any query or CALL to the Stored Procedure

My implementation is (just ignore the $app since I am working on silex framework it is there, YMMV)

function flushhandle() {

    global $app;
    global $db_host;
    global $db_user;
    global $db_pass;
    global $db_name;
    $app['mysqlio']->close();
    $app['mysqlio'] = new mysqli($db_host, $db_user, $db_pass, $db_name);
    return $app['mysqlio'];
}

mysqli::query - Manual, Call to a member function bind_param() , giving you the actual error message from Mysqli supports both procedural and object-oriented syntax, and the  I want to find review group by ratings 1,2 etc and for doing so i write below query but this giving wrong info. This is the query: SELECT CASE ur.rating WHEN ur.rating between 0.5 and

How to connect properly using mysqli, MySQLi is introduced with PHP Version 5.0 and is a replacement for the mysql functions, with object-oriented and procedural versions. It has  What is MySQL. MySQL? What? My is the daughter’s name of the MySQL’s co-founder, Monty Widenius. The name of MySQL is the combination of My and SQL, MySQL. MySQL is a database management system that allows you to manage relational databases. It is open source software backed by Oracle. It means you can use MySQL without paying a dime.

Why should you switch to PDO from MySQL or MySQLi?, MySQLi is a Relational SQL database management system. MySQLi is used inside the PHP programming language to give an interface with MySQL databases. One of MySQL’s hallmarks is exceptional performance & scalability, which is why so many Web-based businesses use MySQL. The world’s most popular open-source database utilizes a number of key strengths to deliver fast performance. They include: InnoDB Default Storage Engine in MySQL 5.5

MySQLi Tutorial, You can insert data into MySQLi table by using mysql> prompt or by using any sc. enter key without giving a semicolon at the end of each line of the command. Whenever I type my password into the mysql command "Enter Password" prompt, I hear a system beep and then mysql closes. I have installed winamp5 1.4.4 and have Windows XP. I would appreciate any

Comments
  • Where is the line $date = abs(strtotime(date('m/d/Y')) - strtotime(date($kdate))) / (60 * 60 * 24); } in your code? You said if you remove up to it, but I can't see it there.
  • Thanks for the great advice. I ended up solving my own question (so far...) in a manner which made more sense on another php page (php.net/manual/en/mysqli.store-result.php). Great presentation by the way - i need to give it some real time but seems valuabe. At first glance, however - may be a bum for me since i'm using MySQL - i'm sure similar theories apply
  • Good answer, helped me to go through a similar problem. Thanks
  • So even if we only have one query using the database object, we should still call mysqli_next_result()?
  • Yes, the active query is not "cleared" unless it confirms there are no more result sets to process.
  • "messes up the connection" How?