How Can I Retrieve Multiple Result Sets from a Single PDO MySQL Query?

Retrieving Multiple Result Sets from PDO Multiple Queries

The PDO_MYSQLND driver introduced support for multiple queries in PHP version 5.3. This allows users to execute multiple SQL queries at once. However, retrieving all the result sets from these queries can be challenging.

Problem Statement

The query "SELECT 1; SELECT 2;" will execute both queries, but only the result set of the first query is returned. The second result set appears to be discarded.

Solution

To retrieve multiple result sets, the PDOStatement::nextRowset method must be used. This method advances the cursor to the start of the next rowset. By calling nextRowset after the first query, users can access the result set of the second query.

$stmt = $db->query("SELECT 1; SELECT 2;");
$stmt->nextRowset();
var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));

This will return the result set for the second query.

Advantages and Disadvantages

This implementation allows for fetching each query's result using different FETCH styles. However, it can be more complex than an implementation that simply returned all result sets under one array. Nonetheless, the ability to handle multiple results flexibly can be valuable in some scenarios.

The above is the detailed content of How Can I Retrieve Multiple Result Sets from a Single PDO MySQL Query?. For more information, please follow other related articles on the PHP Chinese website!