PHP Commands Out of Sync error

Question

I am using two prepared statements in PHP/MySQLi to retrieve data from a mysql database. However, when I run the statements, I get the "Commands out of sync, you can't run the command now" error.

Here is my code:

    $stmt = $mysqli->prepare("SELECT id, username, password, firstname, lastname, salt FROM members WHERE email = ? LIMIT 1";
    $stmt->bind_param('s', $loweredEmail);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($user_id, $username, $db_password, $firstname, $lastname, $salt);
    $stmt->fetch();

    $stmt->free_result();
    $stmt->close();

    while($mysqli->more_results()){
        $mysqli->next_result();
    }

    $stmt1 = $mysqli->prepare("SELECT privileges FROM delegations WHERE id = ? LIMIT 1");
    //This is where the error is generated
    $stmt1->bind_param('s', $user_id);
    $stmt1->execute();
    $stmt1->store_result();
    $stmt1->bind_result($privileges);
    $stmt1->fetch();

What I've tried:

• Moving the prepared statements to two separate objects.

• Using the code:

  while($mysqli->more_results()){
      $mysqli->next_result();
  }
  //To make sure that no stray result data is left in buffer between the first
  //and second statements
  

• Using free_result() and mysqli_stmt->close()

PS: The 'Out of Sync' error comes from the second statement's '$stmt1->error'

Answer 1

In mysqli::query If you use MYSQLI_USE_RESULT all subsequent calls will return error Commands out of sync unless you call mysqli_free_result()

When calling multiple stored procedures, you can run into the following error: "Commands out of sync; you can't run this command now". This can happen even when using the close() function on the result object between calls. To fix the problem, remember to call the next_result() function on the mysqli object after each stored procedure call. See example below:

<?php
// New Connection
$db = new mysqli('localhost','user','pass','database');

// Check for errors
if(mysqli_connect_errno()){
 echo mysqli_connect_error();
}

// 1st Query
$result = $db->query("call getUsers()");
if($result){
     // Cycle through results
    while ($row = $result->fetch_object()){
        $user_arr[] = $row;
    }
    // Free result set
    $result->close();
    $db->next_result();
}

// 2nd Query
$result = $db->query("call getGroups()");
if($result){
     // Cycle through results
    while ($row = $result->fetch_object()){
        $group_arr[] = $row;
    }
     // Free result set
     $result->close();
     $db->next_result();
}
else echo($db->error);

// Close connection
$db->close();
?>

I hope this will help

Answer 2

"Commands out of sync; you can't run this command now"

Details about this error can be found in the mysql docs. Reading those details makes it clear that the result sets of a prepared statement execution need to be fetched completely before executing another prepared statement on the same connection.

Fixing the issue can be accomplished by using the store result call. Here is an example of what I initially was trying to do:

<?php

  $db_connection = new mysqli('127.0.0.1', 'user', '', 'test');

  $post_stmt = $db_connection->prepare("select id, title from post where id = 1000");
  $comment_stmt = $db_connection->prepare("select user_id from comment where post_id = ?");

  if ($post_stmt->execute())
  {
    $post_stmt->bind_result($post_id, $post_title);

    if ($post_stmt->fetch())
    {
      $comments = array();

      $comment_stmt->bind_param('i', $post_id);
      if ($comment_stmt->execute())
      {
        $comment_stmt->bind_result($user_id);
        while ($comment_stmt->fetch())
        {
          array_push($comments, array('user_id' => $user_id));
        }
      }
      else
      {
        printf("Comment statement error: %s\n", $comment_stmt->error);
      }
    }
  }
  else
  {
    printf("Post statement error: %s\n", $post_stmt->error);
  }

  $post_stmt->close();
  $comment_stmt->close();

  $db_connection->close();

  printf("ID: %d -> %s\n", $post_id, $post_title);
  print_r($comments);
?>

The above will result in the following error:

Comment statement error: Commands out of sync; you can't run this command now

PHP Notice: Undefined variable: post_title in error.php on line 41 ID: 9033 -> Array ( )

Here is what needs to be done to make it work correctly:

<?php

  $db_connection = new mysqli('127.0.0.1', 'user', '', 'test');

  $post_stmt = $db_connection->prepare("select id, title from post where id = 1000");
  $comment_stmt = $db_connection->prepare("select user_id from comment where post_id = ?");

  if ($post_stmt->execute())
  {
    $post_stmt->store_result();
    $post_stmt->bind_result($post_id, $post_title);

    if ($post_stmt->fetch())
    {
      $comments = array();

      $comment_stmt->bind_param('i', $post_id);
      if ($comment_stmt->execute())
      {
        $comment_stmt->bind_result($user_id);
        while ($comment_stmt->fetch())
        {
          array_push($comments, array('user_id' => $user_id));
        }
      }
      else
      {
        printf("Comment statement error: %s\n", $comment_stmt->error);
      }
    }

    $post_stmt->free_result();
  }
  else
  {
    printf("Post statement error: %s\n", $post_stmt->error);
  }

  $post_stmt->close();
  $comment_stmt->close();

  $db_connection->close();

  printf("ID: %d -> %s\n", $post_id, $post_title);
  print_r($comments);
?>

A couple things to note about the above example:

The bind and fetch on the statement still works correctly.
Make sure the results are freed when the processing is done.

Answer 3

For those of you who do the right thing and use stored procedures with prepared statements.

For some reason mysqli cannot free the resources when using an output variable as a parameter in the stored proc. To fix this simply return a recordset within the body of the procedure instead of storing the value in an output variable/parameter.

For example, instead of having SET outputVar = LAST_INSERT_ID(); you can have SELECT LAST_INSERT_ID(); Then in PHP I get the returned value like this:

$query= "CALL mysp_Insert_SomeData(?,?)"; 
$stmt = $mysqli->prepare($query); 
$stmt->bind_param("is", $input_param_1, $input_param_2); 
$stmt->execute() or trigger_error($mysqli->error); // trigger_error here is just for troubleshooting, remove when productionizing the code
$stmt->store_result();
$stmt->bind_result($output_value);
$stmt->fetch();
$stmt->free_result();
$stmt->close();
$mysqli->next_result();
echo $output_value;

Now you are ready to execute a second stored procedure without having the "Commands out of sync, you can't run the command now" error. If you were returning more than one value in the record set you can loop through and fetch all of them like this:

while ($stmt->fetch()) {
    echo $output_value;
}

If you are returning more than one record set from the stored proc (you have multiple selects), then make sure to go through all of those record sets by using $stmt->next_result();