Retrieving Multiple Result sets with stored procedure in php/mysqli

by

I think you’re missing something here. This is how you can get multiple results from stored procedure using mysqli prepared statements:

$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_stmt_get_result($stmt);
// 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.

//move to next result set
mysqli_stmt_next_result($stmt);
$result2 = mysqli_stmt_get_result($stmt);
// 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.

// 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']);
}

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).

More Related Contents:

  1. How do I iterate over the results in a MySQLi result set?
  2. How to call a MySQL stored procedure from within PHP code?
  3. Converting MySQL result array to JSON [duplicate]
  4. PHP + MySql + Stored Procedures, how do I get access an “out” value?
  5. Object of class mysqli_result could not be converted to string
  6. SELECT COUNT(*) AS count – How to use this count
  7. Mysqli update throwing Call to a member function bind_param() error [duplicate]
  8. mysqli bind_param() expected to be a reference, value given
  9. mysqli_real_escape_string() expects exactly 2 parameters, 1 given
  10. INSERT – Number of bind variables doesn’t match number of fields in prepared statement
  11. How to write a stored procedure using phpmyadmin and how to use it through php?
  12. How to connect to MySQL database in PHP using mysqli extension?
  13. Calling stored procedure with Out parameter using PDO
  14. How do I count query result rows?
  15. How do you use IN clauses with mysqli prepared statements [duplicate]
  16. What to do with mysqli problems? Errors like mysqli_fetch_array(): Argument #1 must be of type mysqli_result and such
  17. How to fetch all in assoc array from a prepared statement?
  18. Fatal error: Call to undefined function mysqli_result()
  19. Fatal error: Call to undefined method mysqli_stmt::fetch_array() [duplicate]
  20. Why I am getting an error when using w3school tutorial? [duplicate]
  21. Using wildcards in prepared statement
  22. How to remove the fatal error when fetching an assoc array
  23. Fatal error: Call to undefined method mysqli_stmt::get_result()
  24. PHP MySQLi Multiple Inserts
  25. Fatal error: Call to a member function prepare() on a non-object in
  26. prepared parameterized query with PDO
  27. How to make mysqli connect function?
  28. Dynamically bind mysqli_stmt parameters and then bind result
  29. Check to see if an email is already in the database using prepared statements
  30. How to insert into MySQL using mysqli

Leave a Comment