How to retrieve multiple result sets from stored procedures in PHP/MySQLi?

Retrieving Multiple Result Sets with Stored Procedures in PHP/MySQLi

When dealing with stored procedures that return multiple result sets, advancing to the subsequent results can be a challenge in MySQLi.

The Problem:

You have a stored procedure with multiple results and you want to retrieve the second result set using PHP/MySQLi. However, using mysqli_next_result() doesn't seem to work effectively.

The Solution:

To successfully retrieve multiple results from a stored procedure:

1. Prepare and execute the stored procedure:

   <code class="php">$stmt = mysqli_prepare($db, 'CALL multiples(?, ?)');
   mysqli_stmt_bind_param($stmt, 'ii', $param1, $param2);
   mysqli_stmt_execute($stmt);</code>

2. Fetch the first result set:

   <code class="php">$result1 = mysqli_stmt_get_result($stmt);
   while ($row = $result1->fetch_assoc()) {
    // Process first result set
   }</code>

3. Advance to the next result set:

   <code class="php">mysqli_stmt_next_result($stmt);</code>

4. Fetch the second result set:

   <code class="php">$result2 = mysqli_stmt_get_result($stmt);
   while ($row = $result2->fetch_assoc()) {
    // Process second result set
   }</code>

5. Close the statement:

   <code class="php">mysqli_stmt_close($stmt);</code>

Additional Notes:

• Ensure that you read the first result set before advancing to the next.
• Using the object-oriented style in MySQLi can provide a more appealing syntax.

By following these steps, you can successfully retrieve multiple result sets from stored procedures using PHP/MySQLi.

The above is the detailed content of How to retrieve multiple result sets from stored procedures in PHP/MySQLi?. For more information, please follow other related articles on the PHP Chinese website!