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