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: 295

Filed under:
|
|
|

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

Related posts about mysql

Related posts about prepared-statement