php 为什么 mysqli 给出“命令不同步”错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3632075/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is mysqli giving a "Commands out of sync" error?
提问by JM4
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_querybut for all the samples and general data in the guidedoes not seem to be applicable.
在研究，我认为这可能是由于多个库MySQLi查询同时运行，其中使用mysqli_multi_query但在所有样品和通用数据导向似乎并不适用。
Any ideas?
有任何想法吗？
回答by Bill Karwin
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 syncin the MySQL doc on common errors.
MySQL 客户端不允许您在仍有要从正在进行的查询中提取的行的情况下执行新查询。有关常见错误，请参阅MySQL 文档中的命令不同步。
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.
您可以使用mysqli_store_result()从外部查询中预取所有行。这将在 MySQL 客户端中缓冲它们，因此从服务器的角度来看，您的应用程序已获取完整的结果集。然后，即使在从现在缓冲的外部结果集中获取行的循环中，您也可以执行更多查询。
Or you mysqli_result::fetch_all()which returns the full result set as a PHP array, and then you can loop over that array.
或者您mysqli_result::fetch_all()将完整的结果集作为 PHP 数组返回，然后您可以遍历该数组。
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.
调用存储过程是一种特殊情况，因为存储过程有可能返回多个结果集，每个结果集都可能有自己的一组行。这就是为什么@a1ex07 的答案提到使用mysqli_multi_query()和循环直到mysqli_next_result()没有更多结果集。这对于满足 MySQL 协议是必要的，即使在您的情况下，您的存储过程只有一个结果集。
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.
PS：顺便说一下，我看到您正在执行嵌套查询，因为您有表示层次结构的数据。您可能需要考虑以不同方式存储数据，以便更轻松地查询数据。我做了一个关于这个标题为Models for Hierarchical Data with SQL and PHP的演讲。我还在我的书SQL 反模式：避免数据库编程的陷阱 的一章中介绍了这个主题。
Here is how to implement mysqli_next_result()in CodeIgnitor 3.0.3:
以下是mysqli_next_result()在 CodeIgnitor 3.0.3 中的实现方法：
On line 262 of system/database/drivers/mysqli/mysqli_driver.phpchange
system/database/drivers/mysqli/mysqli_driver.php变更的第262行
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.
自 2.x 以来，这一直是一个问题。我刚刚更新到 3.x，不得不将这个 hack 复制到新版本。
回答by Deepan Prabhu Babu
Simply, You have to call mysqli_next_result($db) , after mysqli_free_result is called.
简单地说，您必须在调用 mysqli_free_result 之后调用 mysqli_next_result($db) 。
mysqli_free_result($result); mysqli_next_result($db)mysqli_close($db);
mysqli_free_result($result); mysqli_next_result($db)mysqli_close($db);
回答by Rizwan Mirza
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);
}
}
}
回答by Salman Mohammad
You have to close previous connection hold by Stored Procedure. Instead of closing connection each time, you can simply use :
您必须关闭存储过程保持的先前连接。您无需每次都关闭连接，只需使用：
mysqli_next_result($conn);
回答by Mohd Abdul Mujib
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 CALLto Stored Procedureactually messes up the connection. So all you have to do is reset the database connection handle.
恕我直言，某处线，CALL以Stored Procedure实际弄乱的连接。所以你所要做的就是重置数据库连接句柄。
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 queryor CALLto the Stored Procedure
你甚至可以在你的配置文件中有一个函数来为你做这件事，你所做的就是在创建任何query或CALL到Stored Procedure
My implementation is (just ignore the $appsince I am working on silexframework it is there, YMMV)
我的实现是（$app因为我正在研究silex框架，所以忽略它，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'];
}
