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

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

提问by Joshxtothe4

I am trying to execute my PHP code, which calls two MySQL queries via mysqli, and get the error "Commands out of sync; you can't run this command now".

我正在尝试执行我的 PHP 代码，该代码通过 mysqli 调用两个 MySQL 查询，并收到错误“命令不同步；您现在无法运行此命令”。

Here is the code I am using

这是我正在使用的代码

<?php
$con = mysqli_connect("localhost", "user", "password", "db");
if (!$con) {
    echo "Can't connect to MySQL Server. Errorcode: %s\n". Mysqli_connect_error();
    exit;
}
$con->query("SET NAMES 'utf8'");
$brand ="o";
$countQuery = "SELECT ARTICLE_NO FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE % ? %";
if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("s", $brand);
    $numRecords->execute();
    $data = $con->query($countQuery) or die(print_r($con->error));
    $rowcount = $data->num_rows;
    $rows = getRowsByArticleSearch("test", "Auctions", " ");
    $last = ceil($rowcount/$page_rows);
}  else {

print_r($con->error);
}
foreach ($rows as $row) {
    $pk = $row['ARTICLE_NO'];
    echo '<tr>' . "\n";
    echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['USERNAME'].'</a></td>' . "\n";
    echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['shortDate'].'</a></td>' . "\n";
    echo '<td><a href="#" onclick="deleterec(\'Layer2\', \'' . $pk . '\')">DELETE RECORD</a></td>' . "\n";
    echo '</tr>' . "\n";
}
function getRowsByArticleSearch($searchString, $table, $max) {
    $con = mysqli_connect("localhost", "user", "password", "db");
    $recordsQuery = "SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME, date_format(str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), '%d %m %Y' ) AS shortDate FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE '%?%' ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s')" . $max;
    if ($getRecords = $con->prepare($recordsQuery)) {
        $getRecords->bind_param("s", $searchString);
        $getRecords->execute();
        $getRecords->bind_result($ARTICLE_NO, $USERNAME, $ACCESSSTARTS, $ARTICLE_NAME, $shortDate);
        while ($getRecords->fetch()) {
            $result = $con->query($recordsQuery);
            $rows = array();
            while($row = $result->fetch_assoc()) {
                $rows[] = $row;
            }
            return $rows;
        }
    }
}

I have tried reading up on this, but I am unsure of what to do. I have read about store result and free result, however these have made no difference when using them. I am unsure at exactly which point this error is being caused, and would like to know why it is being caused, and how to fix it.

我曾尝试阅读此内容，但我不确定该怎么做。我已经阅读了有关存储结果和免费结果的信息，但是在使用它们时这些没有任何区别。我不确定这个错误究竟是在哪一点引起的，想知道它是为什么引起的，以及如何解决它。

Going by my debug statements, the first if loop for countQuery is not even being entered, because of an error in my sql syntax near near '% ? %'. However if I just select *instead of trying to limit based on a LIKE clause, I still get the command out of sync error.

根据我的调试语句，countQuery 的第一个 if 循环甚至没有被输入，因为我的 sql 语法中的错误接近'% ? %'。但是，如果我只是选择*而不是尝试基于 LIKE 子句进行限制，我仍然会收到命令不同步错误。

回答by

You can't have two simultaneous queries because mysqli uses unbuffered queries by default (for prepared statements; it's the opposite for vanilla mysql_query). You can either fetch the first one into an array and loop through that, or tell mysqli to buffer the queries (using $stmt->store_result()).

您不能同时进行两个查询，因为 mysqli 默认使用无缓冲查询（对于准备好的语句；对于 vanilla 则相反mysql_query）。您可以将第一个提取到数组中并循环遍历，或者告诉 mysqli 缓冲查询（使用$stmt->store_result()）。

See herefor details.

有关详细信息，请参见此处。

回答by tracy.brown

I solved this problem in my C application - here's how I did it:

我在我的 C 应用程序中解决了这个问题 - 我是这样做的：

1. Quoting from mysql forums:

   This error results when you terminate your query with a semicolon delimiter inside the application. While it is required to terminate a query with a semicolon delimiter when executing it from the command line or in the query browser, remove the delimiter from the query inside your application.

2. After running my query and dealing with the results [C API: mysql_store_result()], I iterate over any further potentially pending results that occurs via multiple SQL statement execution such as two or more select statements (back to back without dealing with the results).

   The fact is that my procedures don't return multiple results but the database doesn't know that until I execute: [C API: mysql_next_result()]. I do this in a loop (for good measure) until it returns non-zero. That's when the current connection handler knows it's okay to execute another query (I cache my handlers to minimize connection overhead).

   This is the loop I use:

   for(; mysql_next_result(mysql_handler) == 0;) 
     /* do nothing */;
   

1. 从mysql论坛引用：

   当您在应用程序中使用分号分隔符终止查询时会导致此错误。虽然从命令行或查询浏览器执行查询时需要使用分号分隔符终止查询，但请从应用程序内的查询中删除分隔符。

2. 在运行我的查询并处理结果 [C API: mysql_store_result()] 之后，我遍历通过多个 SQL 语句执行（例如两个或多个 select 语句（背靠背，不处理结果）而发生的任何其他潜在未决结果）。

   事实是，我的过程不会返回多个结果，但直到我执行：[C API: mysql_next_result()]数据库才知道这一点。我在循环中执行此操作（为了很好的度量），直到它返回非零值。那时当前的连接处理程序知道可以执行另一个查询（我缓存我的处理程序以最小化连接开销）。

   这是我使用的循环：

   for(; mysql_next_result(mysql_handler) == 0;) 
     /* do nothing */;
   

I don't know PHP but I'm sure it has something similar.

我不知道 PHP，但我确定它有类似的东西。

回答by stalin beltran

I had today the same problem, but only when working with a stored procedure. This make the query behave like a multi query, so you need to "consume" other results available before make another query.

我今天遇到了同样的问题，但仅限于使用存储过程时。这使查询的行为类似于多查询，因此您需要在进行另一个查询之前“使用”其他可用结果。

while($this->mysql->more_results()){
    $this->mysql->next_result();
    $this->mysql->use_result();
}

回答by Juergen

I call this function every time before using $mysqli->query Works with stored procedures as well.

我每次在使用 $mysqli->query 之前调用这个函数也适用于存储过程。

function clearStoredResults(){
    global $mysqli;

    do {
         if ($res = $mysqli->store_result()) {
           $res->free();
         }
        } while ($mysqli->more_results() && $mysqli->next_result());        

}

回答by Carl James

Once you used

一旦你使用

stmt->execute();

You MAYclose it to use another query.

您可以关闭它以使用另一个查询。

stmt->close();

This problem was hunting me for hours. Hopefully, it will fix yours.

这个问题困扰了我几个小时。希望它会解决你的问题。

回答by Norman

I use CodeIgniter. One server OK ... this one probably older ... Anyway using

我使用 CodeIgniter。一台服务器还可以……这台可能更旧了……无论如何使用

$this->db->reconnect();

Fixed it.

修复。

回答by Nader Ben Mabrouk

to solve this problem you have to store result data before use it

要解决此问题，您必须在使用前存储结果数据

$numRecords->execute();

$numRecords->store_result();

that's all

就这样

回答by staticsan

The problem is the MySQL client C library, which most MySQL APIs are built on. The problem is that the C library doesn't support simultaneous execution of queries, so all APIs built on top of that also do not. Even if you use unbuffered queries. This is one reason why the asynchronous MySQL API was written. It communicates directly with the MySQL server using TCP and the wire-protocol doessupport simultaneous queries.

问题在于 MySQL 客户端 C 库，大多数 MySQL API 都建立在该库上。问题在于 C 库不支持同时执行查询，因此构建在其之上的所有 API 也不支持。即使您使用无缓冲查询。这是编写异步 MySQL API 的原因之一。它使用 TCP 直接与 MySQL 服务器通信，并且有线协议确实支持同时查询。

Your solution is to either modify the algorithm so you don't need to have both in progress at once, or change them to use buffered queries, which is probably one of the original reasons for their existence in the C library (the other is to provide a kind of cursor).

您的解决方案是修改算法以便您不需要同时进行，或者将它们更改为使用缓冲查询，这可能是它们存在于 C 库中的原始原因之一（另一个是提供一种游标）。

回答by David G.

Another cause: store_result() cannot be called twice.

另一个原因：store_result() 不能被调用两次。

For instance, in the following code, Error 5 is printed.

例如，在下面的代码中，会打印错误 5。

<?php

$db = new mysqli("localhost", "something", "something", "something");

$stmt = $db->stmt_init();
if ($stmt->error) printf("Error 1 : %s\n", $stmt->error);

$stmt->prepare("select 1");
if ($stmt->error) printf("Error 2 : %s\n", $stmt->error);

$stmt->execute();
if ($stmt->error) printf("Error 3 : %s\n", $stmt->error);

$stmt->store_result();
if ($stmt->error) printf("Error 4 : %s\n", $stmt->error);

$stmt->store_result();
if ($stmt->error) printf("Error 5 : %s\n", $stmt->error);

(This may not be relevant to the original sample code, but it can be relevant to people seeking answers to this error.)

（这可能与原始示例代码无关，但可能与寻求此错误答案的人有关。）

回答by Ari Waisberg

Here is what was MY PROBLEM!!!

这是我的问题！！！

The param binding was "dynamic" so I had a variable that sets the params of the data in order to use bind_param. So that variable was wrong but instead of throwing an error like "wrong param data" it says "out of sync bla bla bla" so I was confused...

param 绑定是“动态的”，所以我有一个变量来设置数据的 params 以便使用bind_param。所以那个变量是错误的，但它没有抛出“错误的参数数据”之类的错误，而是说“不同步 bla bla bla”，所以我很困惑......