PHP 命令不同步错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14554517/
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
PHP Commands Out of Sync error
提问by user191125
I am using two prepared statements in PHP/MySQLi to retrieve data from a mysql database. However, when I run the statements, I get the "Commands out of sync, you can't run the command now" error.
我在 PHP/MySQLi 中使用两个准备好的语句从 mysql 数据库中检索数据。但是，当我运行这些语句时，出现“命令不同步，您现在无法运行该命令”错误。
Here is my code:
这是我的代码：
$stmt = $mysqli->prepare("SELECT id, username, password, firstname, lastname, salt FROM members WHERE email = ? LIMIT 1";
$stmt->bind_param('s', $loweredEmail);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($user_id, $username, $db_password, $firstname, $lastname, $salt);
$stmt->fetch();
$stmt->free_result();
$stmt->close();
while($mysqli->more_results()){
$mysqli->next_result();
}
$stmt1 = $mysqli->prepare("SELECT privileges FROM delegations WHERE id = ? LIMIT 1");
//This is where the error is generated
$stmt1->bind_param('s', $user_id);
$stmt1->execute();
$stmt1->store_result();
$stmt1->bind_result($privileges);
$stmt1->fetch();
What I've tried:
我试过的：
- Moving the prepared statements to two separate objects.
Using the code:
while($mysqli->more_results()){ $mysqli->next_result(); } //To make sure that no stray result data is left in buffer between the first //and second statements- Using free_result() and mysqli_stmt->close()
- 将准备好的语句移动到两个单独的对象。
使用代码：
while($mysqli->more_results()){ $mysqli->next_result(); } //To make sure that no stray result data is left in buffer between the first //and second statements- 使用 free_result() 和 mysqli_stmt->close()
PS: The 'Out of Sync' error comes from the second statement's '$stmt1->error'
PS：“不同步”错误来自第二条语句的“$stmt1->error”
采纳答案by G. N. Vashishtha
In mysqli::query If you use MYSQLI_USE_RESULT all subsequent calls will return error Commands out of sync unless you call mysqli_free_result()
在 mysqli::query 如果您使用 MYSQLI_USE_RESULT 所有后续调用将返回错误命令不同步，除非您调用 mysqli_free_result()
When calling multiple stored procedures, you can run into the following error: "Commands out of sync; you can't run this command now". This can happen even when using the close() function on the result object between calls. To fix the problem, remember to call the next_result() function on the mysqli object after each stored procedure call. See example below:
调用多个存储过程时，您可能会遇到以下错误：“命令不同步；您现在无法运行此命令”。即使在调用之间对结果对象使用 close() 函数时，也会发生这种情况。要解决此问题，请记住在每次调用存储过程后调用 mysqli 对象上的 next_result() 函数。请参阅下面的示例：
<?php
// New Connection
$db = new mysqli('localhost','user','pass','database');
// Check for errors
if(mysqli_connect_errno()){
echo mysqli_connect_error();
}
// 1st Query
$result = $db->query("call getUsers()");
if($result){
// Cycle through results
while ($row = $result->fetch_object()){
$user_arr[] = $row;
}
// Free result set
$result->close();
$db->next_result();
}
// 2nd Query
$result = $db->query("call getGroups()");
if($result){
// Cycle through results
while ($row = $result->fetch_object()){
$group_arr[] = $row;
}
// Free result set
$result->close();
$db->next_result();
}
else echo($db->error);
// Close connection
$db->close();
?>
I hope this will help
我希望这个能帮上忙
回答by G. N. Vashishtha
"Commands out of sync; you can't run this command now"
“命令不同步；您现在无法运行此命令”
Details about this error can be found in the mysql docs. Reading those details makes it clear that the result sets of a prepared statement execution need to be fetched completely before executing another prepared statement on the same connection.
有关此错误的详细信息可以在 mysql 文档中找到。阅读这些细节可以清楚地表明，在同一连接上执行另一个准备好的语句之前，需要完全获取准备好的语句执行的结果集。
Fixing the issue can be accomplished by using the store result call. Here is an example of what I initially was trying to do:
可以通过使用存储结果调用来解决该问题。这是我最初尝试做的一个例子：
<?php
$db_connection = new mysqli('127.0.0.1', 'user', '', 'test');
$post_stmt = $db_connection->prepare("select id, title from post where id = 1000");
$comment_stmt = $db_connection->prepare("select user_id from comment where post_id = ?");
if ($post_stmt->execute())
{
$post_stmt->bind_result($post_id, $post_title);
if ($post_stmt->fetch())
{
$comments = array();
$comment_stmt->bind_param('i', $post_id);
if ($comment_stmt->execute())
{
$comment_stmt->bind_result($user_id);
while ($comment_stmt->fetch())
{
array_push($comments, array('user_id' => $user_id));
}
}
else
{
printf("Comment statement error: %s\n", $comment_stmt->error);
}
}
}
else
{
printf("Post statement error: %s\n", $post_stmt->error);
}
$post_stmt->close();
$comment_stmt->close();
$db_connection->close();
printf("ID: %d -> %s\n", $post_id, $post_title);
print_r($comments);
?>
The above will result in the following error:
以上将导致以下错误：
Comment statement error: Commands out of sync; you can't run this command now
注释语句错误：命令不同步；你现在不能运行这个命令
PHP Notice: Undefined variable: post_title in error.php on line 41 ID: 9033 -> Array ( )
PHP 注意：未定义变量：第 41 行的 error.php 中的 post_title ID：9033 -> Array ( )
Here is what needs to be done to make it work correctly:
要使其正常工作，需要执行以下操作：
<?php
$db_connection = new mysqli('127.0.0.1', 'user', '', 'test');
$post_stmt = $db_connection->prepare("select id, title from post where id = 1000");
$comment_stmt = $db_connection->prepare("select user_id from comment where post_id = ?");
if ($post_stmt->execute())
{
$post_stmt->store_result();
$post_stmt->bind_result($post_id, $post_title);
if ($post_stmt->fetch())
{
$comments = array();
$comment_stmt->bind_param('i', $post_id);
if ($comment_stmt->execute())
{
$comment_stmt->bind_result($user_id);
while ($comment_stmt->fetch())
{
array_push($comments, array('user_id' => $user_id));
}
}
else
{
printf("Comment statement error: %s\n", $comment_stmt->error);
}
}
$post_stmt->free_result();
}
else
{
printf("Post statement error: %s\n", $post_stmt->error);
}
$post_stmt->close();
$comment_stmt->close();
$db_connection->close();
printf("ID: %d -> %s\n", $post_id, $post_title);
print_r($comments);
?>
A couple things to note about the above example:
关于上面的例子有几点需要注意：
The bind and fetch on the statement still works correctly.
Make sure the results are freed when the processing is done.
回答by Dimitar Darazhanski
For those of you who do the right thing and use stored procedures with prepared statements.
对于那些做正确的事情并使用带有预准备语句的存储过程的人。
For some reason mysqli cannot free the resources when using an output variable as a parameter in the stored proc. To fix this simply return a recordset within the body of the procedure instead of storing the value in an output variable/parameter.
出于某种原因，当使用输出变量作为存储过程中的参数时，mysqli 无法释放资源。要解决此问题，只需在过程主体内返回一个记录集，而不是将值存储在输出变量/参数中。
For example, instead of having SET outputVar = LAST_INSERT_ID(); you can have SELECT LAST_INSERT_ID(); Then in PHP I get the returned value like this:
例如，而不是 SET outputVar = LAST_INSERT_ID(); 你可以有 SELECT LAST_INSERT_ID(); 然后在 PHP 中我得到这样的返回值：
$query= "CALL mysp_Insert_SomeData(?,?)";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("is", $input_param_1, $input_param_2);
$stmt->execute() or trigger_error($mysqli->error); // trigger_error here is just for troubleshooting, remove when productionizing the code
$stmt->store_result();
$stmt->bind_result($output_value);
$stmt->fetch();
$stmt->free_result();
$stmt->close();
$mysqli->next_result();
echo $output_value;
Now you are ready to execute a second stored procedure without having the "Commands out of sync, you can't run the command now" error. If you were returning more than one value in the record set you can loop through and fetch all of them like this:
现在您已准备好执行第二个存储过程，而不会出现“命令不同步，您现在无法运行命令”错误。如果您在记录集中返回多个值，您可以循环遍历并获取所有值，如下所示：
while ($stmt->fetch()) {
echo $output_value;
}
If you are returning more than one record set from the stored proc (you have multiple selects), then make sure to go through all of those record sets by using $stmt->next_result();
如果您从存储过程中返回多个记录集（您有多个选择），请确保使用 $stmt->next_result(); 遍历所有这些记录集；
