PHP PDOStatement:: fetchAll Explanation

PDOStatement::fetchAll

PDOStatement:: fetchAll-Returns an array containing all the rows in the result set (PHP 5 > = 5.1.0, PECL pdo > = 0.1.0)

Description

Grammar

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

Parameter

fetch_style

Controls how the next 1 lines are returned to the caller. This value must be one of the PDO:: FETCH_* family constants, default to the value of PDO:: ATTR_DEFAULT_FETCH_MODE (default to PDO:: FETCH_BOTH). To return an array containing all the values of a single column in the result set, you need to specify PDO:: FETCH_COLUMN. Get the desired column by specifying the column-index parameter. To get the only 1 value for a single column in the result set, you need to bitwise OR PDO:: FETCH_COLUMN and PDO:: FETCH_UNIQUE. To return an associative array of values grouped by the specified column, you need to bitwise OR PDO:: FETCH_COLUMN and PDO:: FETCH_GROUP.

fetch_argument

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

PDO:: FETCH_COLUMN: Returns the column whose index begins with 0. PDO:: FETCH_CLASS: Returns an instance of the specified class, mapping the columns of each row to the corresponding attribute names in the class. PDO:: FETCH_FUNC: Pass the columns of each row as arguments to the specified function and return the result of the function call.

ctor_args

Custom class constructor parameters when the fetch_style parameter is PDO:: FETCH_CLASS.

Return value

PDOStatement::fetchAll() Returns an array containing all remaining rows in the result set. Each 1 row of this array is either an array of 1 column values or 1 object whose attribute corresponds to each column name.

Using this method to obtain a large result set will lead to a heavy burden on the system and may occupy a large amount of network resources. Instead of retrieving all the data and operating with PHP, consider using database services to process the result set. For example, the WHERE and ORDER BY clauses are used in SQL to qualify the results before the data is retrieved and processed through PHP.

Instances

Get all remaining rows in the result set

<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
/*  Get all 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
    )
)

Gets all the values of a single 1 column in the result set

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

<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
/*  Get the 1 Column all values  */
$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 according to a separate 1 column

The following example demonstrates how to return an associative array grouped according to the values of the specified columns 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();
/*  According to Article 1 Column grouping  */
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"
 }
}

Instantiate 1 class per row of results

The following column 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 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"
 }
}

Call the function once per line

The following column 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 output of the above example is:

<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
/*  Get all remaining rows in the result set  */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);
?>

0

Summarize