PHP MYSQL error when calling multiple stored procedures

Question

I find it difficult to call and display content when I call a procedure more than once on a page. I am trying to display two separate recordsets from two different SP calls for MYSQL. I can display the first call, but the second fails. I'm not sure what I'm doing wrong, but maybe someone can help?

I keep getting an error when I call the second procedure:

Error calling SPCommands out of sync; you can't run this command now 

I run windows

Code below ... PHP

 // First call to SP $page = 2; $section = 1; include("DatabaseConnection.php"); //general connection - works fine $sql = 'CALL GetPageContent("'.$page.'", "'.$section.'")'; $result = mysqli_query($conn, $sql) or die('Error calling SP' .mysqli_error($conn)); while($row=mysqli_fetch_assoc($result)) { // DO STUFF< REMOVED TO MAKE READING CLEARER } mysqli_free_result($result); //SECOND CALL BELOW $section = 2; // change parameter for different results $sql = 'CALL GetPageContent("'.$page.'", "'.$section.'")'; $result = mysqli_query($conn, $sql) or die('Error calling SP' .mysqli_error($conn)); while($row=mysql_fetch_assoc($result)) { // DO STUFF< REMOVED TO MAKE READING CLEARER } 

Answer 1

To fix this problem, be sure to call the next_result () function of the mysqli object after each call to the stored procedure. See the 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(); ?>