PDOStatement::fetchAll

PHP PDO Reference Manual

PDOStatement::fetchAll — Returns an array containing all rows of a result set (PHP 5 >= 5.1.0, PECL pdo >= 0.1.0)

Description

Syntax

array PDOStatement::fetchAll ([ int $fetch_style [, mixed $fetch_argument [, array $ctor_args = array() ]]] )

Parameters

fetch_style

Controls how the next row will be returned to the caller. This value must be one of the PDO::FETCH_* constants, defaulting to the value of PDO::ATTR_DEFAULT_FETCH_MODE (default is PDO::FETCH_BOTH).

To return an array containing all values of a single column from the result set, specify PDO::FETCH_COLUMN. Fetch the desired column by specifying the column-index parameter.

To fetch unique values of a single column from the result set, bitwise OR PDO::FETCH_COLUMN with PDO::FETCH_UNIQUE.

To return an associative array grouped by the values of a specified column, bitwise OR PDO::FETCH_COLUMN with PDO::FETCH_GROUP.

fetch_argument
Depending on the value of the fetch_style parameter, this parameter has different meanings:

• PDO::FETCH_COLUMN: Returns the specified column indexed starting from 0.

• PDO::FETCH_CLASS: Returns instances of the specified class, mapping the columns of each row to the corresponding property names in the class.

• PDO::FETCH_FUNC: Passes the columns of each row as arguments to the specified function, and returns the result of calling the function.

ctor_args
When the fetch_style parameter is PDO::FETCH_CLASS, these are the constructor arguments of the user-defined class.

Return Values

PDOStatement::fetchAll() returns an array containing all remaining rows of the result set. Each row of this array is either an array of column values or an object with properties corresponding to each column name.

Using this method to fetch a large result set will increase system load and may consume a lot of network resources. Rather than retrieving all the data and processing it in PHP, consider using the database service to handle the result set. For example, use WHERE and ORDER BY clauses in SQL to restrict the results before retrieving the data and processing it via PHP.

Examples

Fetch all remaining rows in a result set

<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* 获取结果集中所有剩余的行 */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);
?>

The above example will output:

Fetch all of the remaining rows in the result set:
Array
(
    [0] => Array
        (
            [NAME] => pear
            [0] => pear
            [COLOUR] => green
            [1] => green
        )

    [1] => Array
        (
            [NAME] => watermelon
            [0] => watermelon
            [COLOUR] => pink
            [1] => pink
        )

)

Fetch all values of a single column from a result set

The following example demonstrates how to return all values of a single column from a result set, even though the SQL statement itself may return multiple columns per row.

<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* 获取第一列所有值 */
$result = $sth->fetchAll(PDO::FETCH_COLUMN, 0);
var_dump($result);
?>

The above example will output:

Array(3)
(
    [0] =>
    string(5) => apple
    [1] =>
    string(4) => pear
    [2] =>
    string(10) => watermelon
)

Group all values by a single column

The following example demonstrates how to return an associative array grouped by the values of a specified column in the result set. The array contains three keys: the returned apple and pear arrays contain two different colors, while the returned watermelon array contains only one color.

<?php
$insert = $dbh->prepare("INSERT INTO fruit(name, colour) VALUES (?, ?)");
$insert->execute(array('apple', 'green'));
$insert->execute(array('pear', 'yellow'));

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* 根据第一列分组  */
var_dump($sth->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP));
?>

The above example will output:

array(3) {
  ["apple"]=>
  array(2) {
    [0]=>
    string(5) "green"
    [1]=>
    string(3) "red"
  }
  ["pear"]=>
  array(2) {
    [0]=>
    string(5) "green"
    [1]=>
    string(6) "yellow"
  }
  ["watermelon"]=>
  array(1) {
    [0]=>
    string(5) "green"
  }
}

Instantiates a class for each row of results

The following example demonstrates the behavior of the PDO::FETCH_CLASS fetch style.

<?php
class fruit {
    public $name;
    public $colour;
}

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

$result = $sth->fetchAll(PDO::FETCH_CLASS, "fruit");
var_dump($result);
?>

The above example will output:

array(3) {
  [0]=>
  object(fruit)#1 (2) {
    ["name"]=>
    string(5) "apple"
    ["colour"]=>
    string(5) "green"
  }
  [1]=>
  object(fruit)#2 (2) {
    ["name"]=>
    string(4) "pear"
    ["colour"]=>
    string(6) "yellow"
  }
  [2]=>
  object(fruit)#3 (2) {
    ["name"]=>
    string(10) "watermelon"
    ["colour"]=>
    string(4) "pink"
  }
}

Calls a function for each row

The following example demonstrates the behavior of the PDO::FETCH_FUNC fetch style.

<?php
function fruit($name, $colour) {
    return "{$name}: {$colour}";
}

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

$result = $sth->fetchAll(PDO::FETCH_FUNC, "fruit");
var_dump($result);
?>

The above example will output:

array(3) {
  [0]=>
  string(12) "apple: green"
  [1]=>
  string(12) "pear: yellow"
  [2]=>
  string(16) "watermelon: pink"
}

PHP PDO Reference Manual