PDOStatement::fetchAll — Returns an array containing all rows in the result set (PHP 5 >= 5.1.0, PECL pdo >= 0.1.0)
array PDOStatement::fetchAll ([ int $fetch_style [, mixed $fetch_argument [, array $ctor_args = array() ]]] )
fetch_style
Controls how the next line is returned to the caller. This value must be one of the PDO::FETCH_* series of constants, and the default is the value of PDO::ATTR_DEFAULT_FETCH_MODE (the default is PDO::FETCH_BOTH).
To return an array containing all the values of a single column in the result set, specify PDO::FETCH_COLUMN. Get the desired column by specifying the column-index parameter.
To obtain the unique value of a single column in the result set, you need to bitwise OR PDO::FETCH_COLUMN and PDO::FETCH_UNIQUE.
To return an associative array with values grouped according to the specified column, you need to bitwise OR PDO::FETCH_COLUMN and PDO::FETCH_GROUP.
fetch_argumentThis parameter has different meanings depending on the value of the fetch_style parameter:
PDO::FETCH_COLUMN : Returns the specified column indexed starting from 0.
PDO::FETCH_CLASS : Returns an instance of the specified class, mapping the columns of each row to the corresponding attribute name in the class.
PDO::FETCH_FUNC : Pass the columns of each row as parameters to the specified function and return the result after calling the function.
ctor_args When the fetch_style parameter is PDO::FETCH_CLASS, the parameters of the constructor of the custom class.
PDOStatement::fetchAll() returns an array containing all remaining rows in 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 obtain large result sets will place a heavy burden on the system and may consume significant network resources. Rather than retrieving all the data and then operating it with PHP, consider using a database service to process the result set. For example, use WHERE and ORDER BY clauses in SQL to qualify the results before retrieving the data and processing it through PHP.
<?php$sth = $dbh->prepare("SELECT name, color FROM fruit");$sth->execute();/* Get all the remaining rows in the result set*/print("Fetch all of the remaining rows in the result set:n");$result = $sth->fetchAll();print_r($result);?>The output of the above example is:
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 ))
The following example demonstrates how to return all values of a single column from a result set, although the SQL statement itself may return multiple columns per row.
<?php$sth = $dbh->prepare("SELECT name, color FROM fruit");$sth->execute();/* Get all values in the first column*/$result = $sth->fetchAll(PDO ::FETCH_COLUMN, 0);var_dump($result);?>The output of the above example is:
Array(3)( [0] => string(5) => apple [1] => string(4) => pear [2] => string(10) => watermelon)
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, color FROM fruit");$sth->execute();/* Group based on the first column*/var_dump($sth->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP));?>The output of the above example is:
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" }}The following example demonstrates the behavior of PDO::FETCH_CLASS acquisition style.
<?phpclass fruit { public $name; public $colour;}$sth = $dbh->prepare("SELECT name, color FROM fruit");$sth->execute();$result = $sth->fetchAll( PDO::FETCH_CLASS, "fruit");var_dump($result);?>The output of the above example is:
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" }}The following example demonstrates the behavior of the PDO::FETCH_FUNC get style.
<?phpfunction fruit($name, $colour) { return "{$name}: {$colour}";}$sth = $dbh->prepare("SELECT name, color FROM fruit");$sth->execute ();$result = $sth->fetchAll(PDO::FETCH_FUNC, "fruit");var_dump($result);?>The output of the above example is:
array(3) { [0]=> string(12) "apple: green" [1]=> string(12) "pear: yellow" [2]=> string(16) "watermelon: pink"}