Stored procedure stopped other queries in PHP page MySql

Question

I changed my codes from this

    $query = "SELECT first_id, first, webpage_id FROM first WHERE first= ?";
  $stmt = $connect->prepare($query);
  $stmt->bind_param('s', $first);
  $stmt->execute();

$result = $stmt->get_result();

while($row = $result->fetch_array()){
    $first_id = $row['first_id'];
    $first = $row['first'];
    $webpage_id = $row['webpage_id'];

echo '<div class="yellow container">
       <div class="alt">
        <div class="first_name left"><h1>'.$row['first'].'</h1></div>
        <div>
          <div class="add_sub"><input type="button" class="addsub" data-id="'.$row['first_id'].'" /></div>
        </div>
       </div>';

    $query = "SELECT webpage_id, url, explanation  FROM webpage WHERE webpage_id= ?";
      $stmt = $connect->prepare($query);
      $stmt->bind_param('i', $webpage_id);
      $stmt->execute();
      $result_ce = $stmt->get_result();

Into this in order to use stored procedures in my website. My first codes work perfect but second one stop working after execute stored procedure correctly. It says Call to a member function bind_param() on boolean on line 42

$result = $connect->query("CALL selectfirst('$first')");

// all codes above stays same

while($row = $result->fetch_array()){
    $first_id = $row['first_id'];
    $first = $row['first'];
    $webpage_id = $row['webpage_id'];

echo '<div class="yellow container">
       <div class="alt">
        <div class="first_name left"><h1>'.$row['first'].'</h1></div>
        <div>
          <div class="add_sub"><input type="button" class="addsub" data-id="'.$row['first_id'].'" /></div>
        </div>
       </div>';

    $query2 = "SELECT webpage_id, url, explanation  FROM webpage WHERE webpage_id= ?";
      $stmt = $connect->prepare($query2);
      $stmt->bind_param('i', $webpage_id);   //line 42
      $stmt->execute();
      $result_ce = $stmt->get_result();

my stored procedure written by phpmyadmin gui. I just wrote SELECT * FROM first = ufirst; and clicked create. Maybe problem is there?

IMPORTANT!! I used trigger_error($connect->error."[$query2]") after second sql and page says *Commands out of sync; you can't run this command now[SELECT webpage_id, url, explanation FROM webpage WHERE webpage_id=53]* What that means?

@its clearly change something in the 2nd sql command. Am i right? because first codes works perfect — Webber Depor, Dec 09 '15 at 00:46
Did you check the value and type of `$webpage_id`? Are you sure that it contains and `INTEGER` value? — Richard St-Cyr, Dec 09 '15 at 18:32
@RichardSt-Cyr I checked all three rows coming from stored procedure. they works fine. i began to think problem is the version of PHP or MySQL which are coming from XAMPP — Webber Depor, Dec 09 '15 at 19:53
Try calling `mysql_free_result($result)` before you set up `$query2`. This sounds like an issue related to this question: http://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now — kunruh, Dec 09 '15 at 20:39
i am doing `$result->free_result()` just before `$query2` but its not working again — Webber Depor, Dec 09 '15 at 20:56

score 0 · Answer 1 · answered Jul 28 '16 at 13:26

When you call an stored procedure it may result multiple resultsets, you must free all resultsets before to run another query, add this line after getting result from calling a stored procedure:

$connect->next_result();

And instead of use while($row = $result->fetch_array()){...} you can use fetchAll and iterate in resultset array, because when you use next_result the result resource will be loss.

Stored procedure stopped other queries in PHP page MySql

1 Answers1