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().
$names = $pdo->query('SELECT name FROM city')->fetchAll(PDO::FETCH_COLUMN);
Try it
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.
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
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()orforeach ($stmt as $row)instead, so only one row is held at a time. Better still, limit the result in SQL. FETCH_KEY_PAIRwith duplicate keys keeps the last row for each key.FETCH_UNIQUEalso overwrites rows that share a key; useFETCH_GROUPwhen 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 withrowCount(), which is not reliable forSELECTon every driver. - Mode set with
setFetchMode()orPDO::ATTR_DEFAULT_FETCH_MODEis used when you callfetchAll()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.