PDOStatement::fetchAll
PDOStatement::fetchAll — Returns an array containing all of the result set rows (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 (which defaults to PDO::FETCH_BOTH).
To return an array consisting of all values of a single column from the result set, specify PDO::FETCH_COLUMN. You can specify which column you want by column index.
To return an associative array grouped by the values of a specified column, specify PDO::FETCH_COLUMN with PDO::FETCH_GROUP.
fetch_argument
-
** : Returns the specified 0-indexed column.
-
** : Returns an instance of the specified class, mapping the columns of each row to named properties in the class.
-
** : Passes the columns of each row as arguments to the specified function, and returns the result.
ctor_args
Return Value
PDOStatement::fetchAll() returns an array containing all of the remaining rows in the result set. Each row in the array is either an array of column values or an object with properties corresponding to each column name.
Fetching a large result set with this method can increase the system load and may consume significant network resources. Instead of fetching all data and then processing it in PHP, consider processing the result set in the database, for example, by using WHERE and ORDER BY clauses in your SQL before fetching and processing the data in PHP.
Examples
Fetch all of the remaining rows in the result set
<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
/* Fetch all of 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
)
)
Fetch all values of a single column from the result set
The following example demonstrates how to return an array of all values from a single column from the 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();
/* Fetch all values of 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"
)
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. The array contains three keys: the apple and pear arrays contain two different colors each, while the watermelon array contains only one color.
<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
/* Fetch all values grouped by the first column */
$result = $sth->fetchAll(PDO::FETCH_COLUMN | PDO::FETCH_GROUP);
var_dump($result);
?>
The output of the above example is:
Array(3)
(
[apple] => Array
(
[0] => red
[1] => green
)
[pear] => Array
(
[0] => green
)
[watermelon] => Array
(
[0] => pink
)
)
$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();
/* Group by 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"
}
}
Instantiating a Class for Each Row Result
The following example demonstrates the behavior of 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 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"
}
}
Calling a Function for Each Row
The following example demonstrates the behavior of 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 output of the above example is:
array(3) {
[0]=>
string(12) "apple: green"
[1]=>
string(12) "pear: yellow"
[2]=>
string(16) "watermelon: pink"
}