Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 586
JM4 Avatar asked Sep 02 '10 23:09

JM4


People also ask

How do I find MySQLi query error?

Just simply add or die(mysqli_error($db)); at the end of your query, this will print the mysqli error.

What does MySQLi () do?

The MySQLi functions allows you to access MySQL database servers. Note: The MySQLi extension is designed to work with MySQL version 4.1. 13 or newer.

Should I use MySQLi MySQL?

Basically, MySQL is the old database driver, and MySQLi is the Improved driver. The "i" stands for "improved" so it is MySQL improved. MySQLi can be done procedural and object-oriented whereas MySQL can only be used procedurally. Mysqli also supports prepared statements which protect from SQL Injection.


1 Answers

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.

like image 131
Bill Karwin Avatar answered Nov 13 '22 17:11

Bill Karwin