Moving Between Results with nextRowset()

The nextRowset() method is used when a single query or a stored procedure returns more than one result set (e.g., when you execute two SELECT statements simultaneously). The method moves the pointer to the next available data set, allowing you to read different tables without having to re-establish a connection or send new commands. If the next rowset exists, it returns true, otherwise false.

EXAMPLE

<?php
class Database {
    private $server = "localhost";
    private $username = "root";
    private $password = "";
    private $db_name = "pdo_db";
    public $connection;

    public function __construct() {
        try {
            // Added charset=utf8mb4 for character support
            $dsn = "mysql:host=" . $this->server . ";dbname=" . $this->db_name . ";charset=utf8mb4";
            $this->connection = new PDO($dsn, $this->username, $this->password);
            $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            die("Connection failed: " . $e->getMessage());
        }
    }

    public function getMultipleResults() {
        // We execute two SELECT commands over the same table simultaneously for ID 3
        $sql = "SELECT first_name FROM users WHERE id = 3; SELECT email FROM users WHERE id = 3";
        $statement = $this->connection->query($sql);

        // First rowset: First Name
        $u = $statement->fetch(PDO::FETCH_ASSOC);
        echo "User: " . $u['first_name'] . "<br>";

        // Switch to the next rowset: Email
        if ($statement->nextRowset()) {
            $e = $statement->fetch(PDO::FETCH_ASSOC);
            echo "Email: " . $e['email'];
        }
    }
}

// PART 3: Usage
$db = new Database();
$db->getMultipleResults();
?>

RESULT

The result will be displayed here...

Practical Application: We most commonly use this method for complex reports where the database simultaneously returns, for example, a list of users and company statistics. Check the process at home by following these steps:

Start the Apache and MySQL modules in the XAMPP control panel.
Ensure that the users and settings tables exist within the pdo_db database.
The method returns a boolean value indicating whether another dataset is available.
Insert the example below into a PHP file and observe how the script switches between reading two different tables.