Why is mysqli giving a “Commands out of sync” error?

by

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.

More Related Contents:

  1. Can I mix MySQL APIs in PHP?
  2. How to change mysql to mysqli?
  3. How to prevent duplicate usernames when people register?
  4. Best way to INSERT many values in mysqli?
  5. SELECT COUNT(*) AS count – How to use this count
  6. How to use mysqli prepared statements?
  7. MySQLi prepared statements error reporting [duplicate]
  8. mysqli::mysqli(): (HY000/2002): Can’t connect to local MySQL server through socket ‘MySQL’ (2)
  9. mysqli last insert id
  10. mysqli: can it prepare multiple queries in one statement?
  11. CodeIgniter: Unable to connect to your database server using the provided settings Error Message
  12. How to call a MySQL stored procedure from within PHP code?
  13. What is the difference between MySQL, MySQLi and PDO? [closed]
  14. Do I have to guard against SQL injection if I used a dropdown?
  15. Using MySQLi from another class in PHP
  16. How do I display a MySQL error in PHP for a long query that depends on the user input? [duplicate]
  17. Call to a member function execute() on boolean in [duplicate]
  18. Mysql No connection could be made because the target machine actively refused it
  19. Unknown database error in PHP but it exists in PHPMyAdmin
  20. Fatal error: Uncaught exception ‘mysqli_sql_exception’ with message ‘No index used in query/prepared statement’
  21. Can I blindly replace all mysql_ functions with mysqli_?
  22. Fatal error: Call to undefined function mysqli_result()
  23. Using PHP to execute multiple MYSQL Queries
  24. Fatal error: Call to undefined method mysqli_stmt::fetch_array() [duplicate]
  25. How to run multiple insert query in SQL using PHP in one go?
  26. Using Prepared Statement, how I return the id of the inserted row?
  27. Strange problem with the length of the field gotten from database [duplicate]
  28. PHP prepared statements and transactions in a loop [duplicate]
  29. When should I close a database connection in PHP?
  30. Mysqli multiple row insert, simple multi insert query [duplicate]

Leave a Comment