连续的MySQLi编写的语句不起作用

Question

I want to make successive calls to MySQL stored routines (using prepared statements) from the same PHP file, like this:

$conn = getconn();
$stmt = $conn->prepare("CALL GetUserLoginData(?);");
$stmt->bind_param("s", $username);
$stmt->execute();

// Process data here... until next stored routine call

$conn = getconn();
// var_dump($conn);
$stmt2 = $conn->prepare("CALL SetUserLoginTime(?);");
$stmt2->bind_param("i", $userid);
$stmt2->execute();

where getconn() returns a database connection with new mysqli().

This code works on a web server running PHP 5.3.8-1/MySQL 5.1.54, but not on a server with PHP 5.3.10/5.5.29.

If I uncomment var_dump($conn) I get ["error"]=> string(52) "Commands out of sync; you can't run this command now".

I can't figure out why this is happening on just one server.

Answer 1

$conn = getconn();
$stmt = $conn->prepare("CALL GetUserLoginData(?);");
$stmt->bind_param("s", $username);
$stmt->execute();

// Process data here... until next stored routine call

$conn = getconn();
// var_dump($conn);
$stmt2 = $conn->prepare("CALL SetUserLoginTime(?);");
$stmt2->bind_param("i", $userid);
$stmt2->execute();

Because prepared statements use an unbuffered query type. You have two options:

1) Close your query after executions

$conn = getconn();
$stmt = $conn->prepare("CALL GetUserLoginData(?);");
$stmt->bind_param("s", $username);
$stmt->execute();
$stmt->close(); // Frees the buffer

2) Use free_results: http://php.net/manual/en/mysqli-result.free.php

Scenario Not Within Your Question:

Sometimes Closing the query or freeing the results is not an option. For example a while loop... In A Situation like this, you should use:

store_result();