Skip to content
Tutorials Center
My learning

Topics

PHP Reference ยท Databases (PDO)

PHP PDOStatement::fetchAll() method

Reference for PHP PDOStatement::fetchAll(): get every row at once, with FETCH_COLUMN, FETCH_KEY_PAIR, FETCH_GROUP and FETCH_CLASS, plus memory tips for big results.

PDOStatement::fetchAll() reads all remaining rows of a result set into one PHP array. It is the shortest way to turn a query into data you can loop over, count, sort or pass to a template.

Besides plain rows, a few fetch modes reshape the whole result: a flat list of one column, a key => value map, or rows grouped by a column. Those are the real strength of fetchAll().

PHP
$names = $pdo->query('SELECT name FROM city')->fetchAll(PDO::FETCH_COLUMN);

Try it

PHP Rows, columns, pairs and groups

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

PHP Objects, callbacks and a prepared query

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Syntax

PHP
PDOStatement::fetchAll(int $mode = PDO::FETCH_DEFAULT): array
PDOStatement::fetchAll(int $mode = PDO::FETCH_COLUMN, int $column): array
PDOStatement::fetchAll(int $mode = PDO::FETCH_CLASS, string $class, ?array $constructorArgs): array
PDOStatement::fetchAll(int $mode = PDO::FETCH_FUNC, callable $callback): array
Name Description
$mode The fetch mode, optionally combined with PDO::FETCH_GROUP, PDO::FETCH_UNIQUE or PDO::FETCH_KEY_PAIR. Defaults to the statement or connection default.
$column With FETCH_COLUMN: which column to return, counting from 0.
$class, $constructorArgs With FETCH_CLASS: the class to instantiate and its constructor arguments.
$callback With FETCH_FUNC: a function that receives each row's values as arguments and returns the item.

Useful shapes:

Mode Result
FETCH_COLUMN A flat list of the first (or chosen) column.
FETCH_KEY_PAIR [col1 => col2]; the query must select exactly two columns.
FETCH_UNIQUE Rows keyed by the first column (the key is removed from the row).
FETCH_GROUP Rows grouped under the first column's value; each group is a list.

Return value: an array of all remaining rows. It is an empty array when there are none.

Errors / Exceptions: PDOException in exception mode when the fetch fails.

Usage notes

  • Everything is loaded into memory. For thousands of rows, loop with fetch() or foreach ($stmt as $row) instead, so only one row is held at a time. Better still, limit the result in SQL.
  • FETCH_KEY_PAIR with duplicate keys keeps the last row for each key.
  • FETCH_UNIQUE also overwrites rows that share a key; use FETCH_GROUP when keys repeat.
  • Rows already fetched are gone. The cursor only moves forward, so a second fetchAll() on the same executed statement returns [].
  • Count rows from the array, count($rows), not with rowCount(), which is not reliable for SELECT on every driver.
  • Mode set with setFetchMode() or PDO::ATTR_DEFAULT_FETCH_MODE is used when you call fetchAll() without arguments.
  • Native types. Integer and float columns are returned as PHP numbers with SQLite and native MySQL prepares; with emulated MySQL prepares they can be strings.