Retrieving Multiple Result sets with stored procedure in php/mysqli

Question

I have a stored procedure that has multiple result sets. How do I advance to the 2nd result set in mysqli to get those results?

Let's say it's a stored proc like:

create procedure multiples( param1 INT, param2 INT )
BEGIN

SELECT * FROM table1 WHERE id = param1;

SELECT * FROM table2 WHERE id = param2;

END $$

The PHP is something like this:

$stmt = mysqli_prepare($db, 'CALL multiples(?, ?)');

mysqli_stmt_bind_param( $stmt, 'ii', $param1, $param2 );

mysqli_stmt_execute( $stmt );

mysqli_stmt_bind_result( $stmt, $id );

Then this is the part I can't get to work. I've tried using mysqli_next_result to move to the next result set, but can't get it to work. We did get it to work with mysqli_store_result and mysqli_fetch_assoc/array/row, but for some reason all the ints get returned as blank strings.

Any one else come across this and have a solution?

Answer 1

It looks like MySQLi may only support multiple result sets through mysqli_multi_query(), since MySQLi_STMT objects work differently from MySQLi_Result objects.

PDO seems to be somewhat more abstracted, with the PDOStatement objects being able to handle multiple result sets for both regular queries (PDO::query) and prepared statements(PDO:prepare).

Answer 2

I think you're missing something here (the following has not been tested):

$stmt = mysqli_prepare($db, 'CALL multiples(?, ?)');
mysqli_stmt_bind_param($stmt, 'ii', $param1, $param2);
mysqli_stmt_execute($stmt);
// fetch the first result set
$result1 = mysqli_use_result($db);
// you have to read the result set here 
while ($row = $result1->fetch_assoc()) {
    printf("%d\n", $row['id']);
}
// now we're at the end of our first result set.
mysqli_free_result($result1);

//move to next result set
mysqli_next_result($db);
$result2 = mysqli_use_result($db);
// you have to read the result set here 
while ($row = $result2->fetch_assoc()) {
    printf("%d\n", $row['id']);
}
// now we're at the end of our second result set.
mysqli_free_result($result2);

// close statement
mysqli_stmt_close($stmt);

Using PDO your code would look like:

$stmt = $db->prepare('CALL multiples(:param1, :param2)');
$stmt->execute(array(':param1' => $param1, ':param2' => $param2));
// read first result set
while ($row = $stmt->fetch()) {
    printf("%d\n", $row['id']);
}
$stmt->nextRowset();
// read second result set
while ($row = $stmt->fetch()) {
    printf("%d\n", $row['id']);
}

But I have heard that the PDOStatement::nextRowset() is not implemented with the MySQL PDO driver making it impossible to retrieve multiple result sets:

• PDO nextRowset not working on MySQL
• pdo_mysql: stored procedure call returning single rowset blocks future queries
• Can't use stored procedures from PDO on Windows

So, depending on your PHP version, you'd have to stick with your mysqli-solution. By the way: do you use the procedural style deliberately? Using object oriented style with mysqli would make your code look a little bit more appealing (my personal opinion).