How to Retrieve Multiple Result Sets from a Single PDO Query?

PDO Multiple Queries: Retrieving Multiple Result Sets

PHP 5.3 introduced support for multiple queries in the PDO_MYSQLND driver, allowing you to execute multiple SELECT queries in a single call. However, retrieving the result sets from these queries can be confusing.

Consider the following query:

$db->query("SELECT 1; SELECT 2;")

This query executes two SELECT queries. The fetchAll(PDO::FETCH_ASSOC) method is then used to retrieve the result set. However, this only returns the results of the first query:

array(1) {
  [0] =>
  array(1) {
    [1] =>
    string(1) "1"
  }
}

Using PDOStatement::nextRowset

To retrieve the results of the second query, you need to use the PDOStatement::nextRowset method. This method allows you to move the statement cursor to the next result set:

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

This will return the results of the second query:

array(1) {
  [0] =>
  array(1) {
    [2] =>
    string(1) "2"
  }
}

Multiple FETCH Styles

The advantage of using PDOStatement::nextRowset is that it allows you to fetch each query using different FETCH styles. For example:

$stmt = $db->query("SELECT 1; SELECT 'a';");
$stmt->nextRowset();
$first = $stmt->fetchColumn(); // fetch first column of second query
$stmt->nextRowset();
$second = $stmt->fetch(PDO::FETCH_ASSOC); // fetch associative array of third query

This gives you greater flexibility when working with multiple queries.

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