PHP
All rows with fetchAll()
Learn to get all rows of a query result as an array with fetchAll().
All rows with fetchAll()
fetchAll() reads all rows from a PDOStatement at once, as an array. Each row becomes a sub-array; you loop over the outer array with foreach.
Example: $rows = $stmt->fetchAll(PDO::FETCH_ASSOC). Then foreach ($rows as $row) { echo $row['name']; } processes each row.
On very large tables there can be a memory risk; in that case the while + fetch loop is preferred.
fetchAll() and memory
fetchAll() is handy but use it carefully on large tables. It loads all rows into memory at once; with many rows it can consume memory.
For a few hundred rows it is ideal; the code is short and readable. With tens of thousands of rows the while + fetch loop is safer.
When you choose, think about row count and the memory limit.
Exercise Print all names with fetchAll()
Create a users table in in-memory SQLite and insert three users. Run SELECT name FROM users ORDER BY id with query(). Get all rows as an array with fetchAll(PDO::FETCH_ASSOC) and print each name field with foreach.
Show example solution
:FETCH_ASSOC);. Print with foreach ($rows as $row) { echo $row['name'] . "\n"; }.
Example solution:
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alex')");
$pdo->exec("INSERT INTO users (name) VALUES ('Sam')");
$pdo->exec("INSERT INTO users (name) VALUES ('Dana')");
$stmt = $pdo->query("SELECT name FROM users ORDER BY id");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
echo $row['name'] . "\n";
}
Test yourself
-
Pick the right method for all rows
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alex')");
$pdo->exec("INSERT INTO users (name) VALUES ('Sam')");
$stmt = $pdo->query("SELECT name FROM users ORDER BY id");
$rows = $stmt->____(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
echo $row['name'] . "\n";
}
?>
Show answer
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alex')");
$pdo->exec("INSERT INTO users (name) VALUES ('Sam')");
$stmt = $pdo->query("SELECT name FROM users ORDER BY id");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
echo $row['name'] . "\n";
}
?>
-
Match the fetchAll concepts
- fetchAll()
- Returns all rows
- fetch()
- Returns one row
- foreach
- Used with fetchAll
- while
- Used with fetch
-
Which is preferred when reading all rows from a very large table?
- Get them all at once with fetchAll()
- Read row by row with while + fetch()
- Use exec() instead of query()
- Call getAttribute() instead of fetch()
Show answer
Read row by row with while + fetch() — Correct. On large tables while + fetch reads row by row and saves memory.
Next lesson: Fetch mode constants
