PHP MYSQL Error when calling multiple Stored Procedures

Question

I am having difficulty calling and displaying the content when I call a procedure more than once in a page. I am trying to display two separate record sets from two different SP calls for MYSQL. I can display the first call but the second fails. I'm not sure what I am doing wrong but perhaps someone can kind help?

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

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

I'm running on 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
}

shouldn't the second fetch be mysqli_fetch_assoc?

Aaron W.
– Aaron W.

2012-05-24 20:53:25 +00:00
Commented May 24, 2012 at 20:53 — Aaron W.
– Aaron W., Commented May 24, 2012 at 20:53
Yes but either way I still get the same error... ? thx

user1415936
– user1415936

2012-05-24 20:59:35 +00:00
Commented May 24, 2012 at 20:59 — user1415936
– user1415936, Commented May 24, 2012 at 20:59
This not work? stackoverflow.com/q/4997601/138383

Aaron W.
– Aaron W.

2012-05-24 21:10:04 +00:00
Commented May 24, 2012 at 21:10 — Aaron W.
– Aaron W., Commented May 24, 2012 at 21:10

Dharman · Accepted Answer · 2021-10-28 22:45:30Z

10

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();
?>

edited Oct 28, 2021 at 22:45

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered May 24, 2012 at 21:22

Sunil Kartikey

5253 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1415936 Over a year ago

That's great, I no longer get any errors but how do I display the following in the while loop... thank you so much for the next step ahead... I was previously calling the following in the while loop but now I get error saying: Fatal error: Cannot use object of type stdClass as array in echo "<li>\n"; echo "<a href='utilities/".$row['Image']."' rel='shadowbox[Gallery]' title='".$row['Text']."'><img src='utilities/".$row['Image']."' /></a>\n"; echo "<span class='cs-title'>".$row['Text']."</span>"; echo "</li>\n";

Sunil Kartikey Over a year ago

print $row first var_dump($row); check if it is returning what you have expected.

Kiran Maniya · Accepted Answer · 2021-07-11 08:15:38Z

4

if you are calling more than one procedure on a single script page, you should call mysqli_next_result($connection_link) before calling another procedure. consider below sample code block:

<?php
    $data = new \stdClass();
    require('./controller/dbController.php');
    
    $query = 'CALL getActiveProductCount()';
    $result = mysqli_query($con, $query);
    $result = mysqli_fetch_assoc($result);
    $data->active_product = $result['count'];
    mysqli_next_result($con);  // required to perform before calling the procedure again

    $query = 'CALL getPurchasedProductCount()';
    $result = mysqli_query($con, $query);
    $result = mysqli_fetch_assoc($result);
    $data->purchased = $result['count'];
    mysqli_next_result($con);

edited Jul 11, 2021 at 8:15

answered Apr 25, 2018 at 11:51

Kiran Maniya

9,08911 gold badges63 silver badges87 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL Error when calling multiple Stored Procedures

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related