Retrieving Multiple Result Sets With Stored Procedure in PHP/MySQLi

Retrieving Multiple Result sets with stored procedure in php/mysqli

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

PHP Unable to read second result set from mysqli statement

I've got multiple result sets working using Barmar's suggestion of using mysqli_multi_query(), which I'll post below. However, it does not use mysqli_stmt_next_result(), so if someone does provide a working solution using mysqli_stmt_next_result() I'll mark theirs as the accepted solution.

        public function GetSession($sessionId) {
        $conn = mysqli_connect(DbConstants::$servername, DbConstants::$username, DbConstants::$password, DbConstants::$dbname);
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        $query = 'CALL Sp_Session_GetById(' . $sessionId . ');';
        mysqli_multi_query($conn, $query);

        $sessionResult = mysqli_store_result($conn);
        $sessionRow = mysqli_fetch_row($sessionResult);

        $session = new Session(
            $sessionRow[0],
            $sessionRow[1],
            $sessionRow[2],
            $sessionRow[3],
            $sessionRow[4],
            $sessionRow[5],
            $sessionRow[6],
            $sessionRow[7],
            $sessionRow[8],
            []);

        mysqli_free_result($sessionResult);

        mysqli_next_result($conn);

        $sessionTypeResult = mysqli_store_result($conn);
        while($sessionTypeRow = mysqli_fetch_row($sessionTypeResult)) {
            array_push($session->sessionTypesForSession, $sessionTypeRow[0]);
        }

        $conn->close();

        return $session;

    }

Looping through MySQL results from stored procedure

According to @JurisGregov comment, the solution for this is;

//first stored procedure

$sql1="CALL YoungEmployees('$dymd')";

if($rst1=$mysqli->query($sql1))
{
  while($row1=$rst1->fetch_row())
  {
    ...
  }
  $rst1->close();
  $mysqli->next_result(); //!!!
}

//second stored procedure

$sql2="CALL OldEmployees('$dymd')";

if($rst2=$mysqli->query($sql2))
{
  while($row2=$rst2->fetch_row())
  {
    ...
  }
  $rst2->close();
  $mysqli->next_result(); //!!!
}

---

Related Topics