All rows with fetchAll()

PHP course · Advanced · Lesson 5/92

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.

All lessons in Advanced

All rows with fetchAll()