How do you Access Multiple Result Sets from Stored Procedures in PHP/MySQLi?

Utilizing Multiple Result Sets with Stored Procedures in PHP/MySQLi

A stored procedure can contain multiple result sets, allowing you to retrieve diverse data sets with a single execution. However, accessing subsequent result sets can be challenging in PHP with mysqli. This question delves into a specific issue encountered when trying to retrieve the second result set from a stored procedure using mysqli.

The proposed solution involves the following steps:

1. Prepare and Execute the Stored Procedure:
   Prepare a statement and bind the input parameters to it. Then, 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. Retrieve the First Result Set (Optional):
   Before moving to the second result set, you can choose to retrieve the first result set using mysqli_stmt_get_result(). This step is optional if you're only interested in subsequent results.
3. Move to the Next Result Set:
   Use mysqli_stmt_next_result() to advance to the next result set.
4. Retrieve the Second Result Set:
   Obtain the second result set using mysqli_stmt_get_result().

<code class="php">// Move to the second result set
mysqli_stmt_next_result($stmt);

// Retrieve the second result set
$result2 = mysqli_stmt_get_result($stmt);

// Fetch and print data from the second result set
while ($row = $result2->fetch_assoc()) {
    printf("%d\n", $row['id']);
}</code>

5. Close the Statement:
   Once all result sets have been retrieved, close the statement.

This method allows you to efficiently navigate and access multiple result sets returned by stored procedures in PHP using mysqli.

The above is the detailed content of How do you Access Multiple Result Sets from Stored Procedures in PHP/MySQLi?. For more information, please follow other related articles on the PHP Chinese website!