How to make multiple queries with PHP prepared statements (Commands out of sync error)
Posted
by Tirithen
on Stack Overflow
See other posts from Stack Overflow
or by Tirithen
Published on 2010-01-22T21:32:51Z
Indexed on
2010/04/10
10:03 UTC
Read the original article
Hit count: 322
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?
© Stack Overflow or respective owner