2

Hi so I know this is something that seems to have been answered before on here, but nothing that I've tried has worked. Basically what I am trying to do is to call up a stored procedure that inserts some data, and gives back the id of the auto_increment that was just inserted. Then that statement is closed and I keep the id.

Then the fun part happens. I go into a loop and for each instance of the loop I need to call another stored procedure that inserts some data, and returns the id of the last auto_increment. Using that id I will then go and do some more things. However, right now it is failing in the loop. It executes the first time without a problem, and then on the prepare for the next go around it gives me the error Commands out of sync; you can't run this command now.

I really have tried to somehow free up the results from the first time around using stmt->free(), but that didn't work, or to free up the mysqli2 connection, but nothing I've done has worked at this point. Any tips or hints would be really appreciated!

$insert_questionnaire_sql = "CALL insert_questionnaire_info(?, ?, ?, ?, ?, ?, ?)";

$questionnaire_insert_stmt = $mysqli->prepare($insert_questionnaire_sql);
$questionnaire_insert_stmt->bind_param("ssisiis", $meta[0], $meta[4], $length_of_questions, $user, $meta[2], $meta[3], $meta[1]);

//execute the statement
$success = $questionnaire_insert_stmt->execute();

$qn_id = -1;
//bind the id of the questionnaire that was just inserted
$questionnaire_insert_stmt->bind_result($qn_id);

//fetch the id
$questionnaire_insert_stmt->fetch();

//close the statement
$questionnaire_insert_stmt->close();


//next we insert each question into the database
$i = 0;
for($i; $i < count($Questions); $i++){
    //only if the question has been submitted
    if($Questions[$i]->submitted){
        //prepare the statement
        $insert_question_sql = "CALL insert_question_info(?, ?, ?, ?, ?, ?, ?)";
        $question_insert_stmt = $mysqli2->prepare($insert_question_sql) or die ($mysqli2->error);

        $type = -1;
        $width = -1;
        $height = -1;
        //count the number of answers
        $numAnswers = countNotDeletedAnswers($Questions[$i]);
        $text = $Questions[$i]->text;
        //figure out what kind of thing this is
        if($Questions[$i]->instruction == true){
            $type = 2;
        }
        else if($Questions[$i]->image == true){
            $type = 3;
            $width = $Questions[$i]->width;
            $height = $Questions[$i]->height;
            //if we have an image we want to put the path as the text
            $text = $Questions[$i]->path;
        }
        else{
            $type = 1;
        }

        //bind the params
        $question_insert_stmt->bind_param("isiisii", $qn_id, $text, $type, $numAnswers, $user, $width, $height);
        //execute
        $success = $question_insert_stmt->execute() or die ($mysqli2->error);
        //bind the id of the questionnaire that was just inserted
        $q_id = -1;
        $question_insert_stmt->bind_result($q_id);
        //fetch the id
        $data = $question_insert_stmt->fetch();

        //close the statement
        $question_insert_stmt->close();

    }

}
Pompey
  • 616
  • 2
  • 9
  • 23
  • Do not prepare statements inside a loop. That entirely defeats the purpose of having them. prepare OUTSIDE the loop, then repeatedly execute them inside the loop. – Marc B Sep 25 '13 at 19:00
  • @MarcB I've taken it outside of the loop, and no I don't get an error, but it still doesn't execute the second time around. Only 1 thing is inserted into my database – Pompey Sep 25 '13 at 19:07

1 Answers1

1

CALL-queries fetching additional resultsets, as mentioned in docs. After such query try to loop throght additional resultsets and free them, using something like this:

function cleanUp(mysqli_stmt $stmt)
    {
        do
            {
                // $stmt->store_result();
                $stmt->free_result();
            }
        while($stmt->more_results() && $stmt->next_result());
    }

Otherwise, you will get this error, if your procedure return any resultset, that wasn't free()d properly.

BlitZ
  • 12,038
  • 3
  • 49
  • 68
  • So I would call this from inside my for loop like this?: cleanUp($question_insert_stmt) but that just tells me that $get_result() is an undefined method. – Pompey Sep 25 '13 at 19:10
  • @AndrewMoldovan Ok, looks like `mysqlnd` is not installed on your server. I'll correct my answer soon. – BlitZ Sep 25 '13 at 19:11
  • @AndrewMoldovan try this one. – BlitZ Sep 25 '13 at 19:14
  • Now it's saying that more_results() is undefined – Pompey Sep 25 '13 at 19:16
  • @AndrewMoldovan Well, that is [interesting](http://www.php.net/manual/en/mysqli-stmt.more-results.php). – BlitZ Sep 25 '13 at 19:17
  • If I switch to PDO should this error go away? Or does PDO have the same issues? – Pompey Sep 25 '13 at 19:18
  • @AndrewMoldovan Maybe. Try it. Just run 2 two stored procedures after each other. Could you please notify me with your results? I'm curious about it too. – BlitZ Sep 25 '13 at 19:20
  • ok sure I will give that a try and let you know – Pompey Sep 25 '13 at 19:21
  • The strangest thing is happening. I tried 2 stored procedures just right after each other using the same connection (PDO), and it worked. Then I put it back inside my loop so that it would execute the stored procedures like that, and it executes the first time through the loop but not the second time. No errors thrown or anything. Just doesn't put it into the database. – Pompey Sep 25 '13 at 20:01
  • @AndrewMoldovan That's very odd... I might suggest to try restructurize your script. If it will not help, then ask on `mysql.com` about the issue. Also, check your stored routines. It might be, that this issue is not produced by php. – BlitZ Sep 25 '13 at 20:04
  • Ya I'm thinking there has to be something else wrong in my code at this point, because this should be working. I appreciate the help!! – Pompey Sep 25 '13 at 20:06
  • @AndrewMoldovan I wish you good luck with fixing it. Cheers. – BlitZ Sep 25 '13 at 20:06