How to make multiple queries with PHP prepared statements (Commands out of sync error)
- by Tirithen
I'm trying to run three MySQL queries from a PHP script, the first one works fine, but on the second one I get the "Commands out of sync; you can’t run this command now" error. I have managed to understand that I need to "empty" the resultset before preparing a new query but I can't seem to understand how. I thought that $statement-close; would do that for me. Here is the relevant part of the code:
<?php
$statement = $db_conn->prepare("CALL getSketches(?,?)"); // Prepare SQL routine to check if user is accepted
$statement->bind_param("is", $user_id, $loaded_sketches); // Bind variables to send
$statement->execute(); // Execute the query
$statement->bind_result( // Set return varables
$id, $name,
$description,
$visibility,
$createdby_id,
$createdby_name,
$createdon,
$permission
);
$new_sketches_id = array();
while($statement->fetch()) {
$result['newSketches'][$id] = array(
"name" => $name,
"description" => $description,
"visibility" => $visibility,
"createdById" => $createdby_id,
"createdByName" => $createdby_name,
"createdOn" => $createdon,
"permission" => $permission
);
$new_sketches_id[] = $id;
}
$statement->close; // Close satement
$new_sketches_ids = implode(",", $new_sketches_id);
// Get the new sketches elements
$statement = $db_conn->prepare("CALL getElements(?,'',?,'00000000000000')"); // Prepare SQL routine to check if user is accepted
// The script crashes here with $db_conn->error
// "Commands out of sync; you can't run this command now"
$statement->bind_param("si", $new_sketches_ids, $user_id); // Bind variables to send
$statement->execute(); // Execute the query
$statement->bind_result( // Set return varables
$id,
$user_id,
$type,
$attribute_d,
$attribute_stroke,
$attribute_strokeWidth,
$sketch_id,
$createdon
);
while($statement->fetch()) {
$result['newSketches'][$sketch_id]['newElements']["u".$user_id."e".$id] = array(
"type" => $type,
"d" => $attribute_d,
"stroke" => $attribute_stroke,
"strokeWidth" => $attribute_strokeWidth,
);
}
$statement->close; // Close satement
?>
How can I make the second query without closing and reopening the entire database connection?
