为什么 mysqli 给出“命令不同步"?错误? [英] Why is mysqli giving a "Commands out of sync" error?

问题描述

我正在尝试运行以下内容.

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"];

到....

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

一切都会好起来的.如果没有，我会收到以下错误:

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

命令不同步；你现在不能运行这个命令

在研究中，我认为这可能是由于多个 MySQLi 查询同时运行，其中使用了 mysqli_multi_query 但对于 指南 似乎不适用.

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.

有什么想法吗?

推荐答案

MySQL 客户端不允许您在仍有要从正在进行的查询中提取的行的情况下执行新查询.请参阅命令不同步关于常见错误的 MySQL 文档.

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.

您可以使用 mysqli_store_result() 从外查询.这将在 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_result::fetch_all() 它将完整结果集作为 PHP 数组返回，然后您可以遍历该数组.

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

调用存储过程是一种特殊情况，因为存储过程有可能返回多个结果集，每个结果集都可能有自己的一组行.这就是为什么@a1ex07 的答案提到使用 mysqli_multi_query() 并循环直到 mysqli_next_result() 没有更多结果集.这对于满足 MySQL 协议是必要的，即使在您的情况下，您的存储过程只有一个结果集.

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:顺便说一下，我看到您正在执行嵌套查询，因为您有表示层次结构的数据.您可能需要考虑以不同方式存储数据，以便更轻松地查询数据.我做了一个关于这个标题为 Models for Hierarchical Data with SQL and PHP 的演讲.我还在我的书 SQL 反模式:避免数据库编程的陷阱.

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.

这里是如何在 CodeIgnitor 3.0.3 中实现 mysqli_next_result():

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

system/database/drivers/mysqli/mysqli_driver.php的第262行变化

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

到这里

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;
}

自 2.x 以来，这一直是一个问题.我刚刚更新到 3.x，不得不将这个 hack 复制到新版本.

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 给出“命令不同步"?错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！