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.
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()
PS: The 'Out of Sync' error comes from the second statement's '$stmt1->error'
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()
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:
I hope this will help
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.
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:
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:
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();
"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.
Fixing the issue can be accomplished by using the store result call. Here is an example of what I initially was trying to do:
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 ( )
Here is what needs to be done to make it work correctly:
A couple things to note about the above example: