Reading Results
fetch one row at a time, fetchAll for small results, fetchColumn for a single value, and the fetch mode that decides their shape.
PDOStatement::fetch(int $mode = PDO::FETCH_DEFAULT, int $cursorOrientation = PDO::FETCH_ORI_NEXT, int $cursorOffset = 0): mixedAfter execute() on a SELECT, the rows are waiting on the statement object. You take them out with a fetch method. Nothing arrives until you ask.
One row or all of them
fetch() returns the next row and moves forward. It returns false when there are no rows left, which is what ends the loop. fetchAll() returns every remaining row as one array. fetchAll() is shorthand for a fetch() loop that appends to an array, and the two are interchangeable for a small result. They stop being interchangeable at scale: a hundred thousand rows fetched all at once are a hundred thousand rows in PHP's memory. Loop one row at a time when you do not know how many there will be.
What shape a row has
PDO::FETCH_ASSOC gives an array keyed by column name. PDO::FETCH_OBJ gives a stdClass whose properties are the column names, so you write $row->email. The connection default is PDO::FETCH_BOTH, which returns each value twice, once by name and once by position; set PDO::ATTR_DEFAULT_FETCH_MODE on the connection so you do not pass a mode to every call.
A single value
fetchColumn() returns one column of the next row, counting from 0. It is the right call for SELECT COUNT(*) or for one name by id. A PDOStatement also works directly in foreach, which is the same thing as a fetch() loop.
Example
When to use it
- Load one user by id and show a 404 when the row is not there.
- Read a COUNT(*) into an integer for a dashboard figure.
- Walk a million-row export one row at a time without exhausting memory.
- Turn a two-column lookup table into an id => label array for a form.
More examples
One row, a page of rows, one number
These are the three calls that cover almost every read. Note the === false check on the single row: that is how a missing record is detected, and it is the difference between a 404 page and a fatal error further down. The cast on fetchColumn() is deliberate, because COUNT(*) comes back as a string from most drivers.
<?php
declare(strict_types=1);
// One row, or false if the id does not exist.
$stmt = $pdo->prepare('SELECT id, email FROM users WHERE id = :id');
$stmt->execute(['id' => $id]);
$user = $stmt->fetch();
if ($user === false) {
http_response_code(404);
exit('No such user.');
}
// A page of rows, small enough to hold in memory.
$stmt = $pdo->prepare('SELECT id, title FROM products ORDER BY title LIMIT 50');
$stmt->execute();
$products = $stmt->fetchAll();
// One value. fetchColumn() returns the first column of the next row.
$stmt = $pdo->prepare('SELECT COUNT(*) FROM products WHERE active = 1');
$stmt->execute();
$activeCount = (int) $stmt->fetchColumn();
Objects instead of arrays
FETCH_OBJ is a readability choice, not a feature: the row is still just data, and a stdClass has no methods and no types. Reach for it when a template reads better with arrows. The htmlspecialchars() call is there because the email came out of a database and is about to go into a page, which is always an escaping job.
<?php
declare(strict_types=1);
$stmt = $pdo->prepare('SELECT id, email FROM users WHERE active = 1');
$stmt->execute();
// stdClass per row: $row->email instead of $row['email'].
while (($row = $stmt->fetch(PDO::FETCH_OBJ)) !== false) {
echo htmlspecialchars($row->email, ENT_QUOTES, 'UTF-8'), "<br>\n";
}
// fetchObject() is the same fetch with the mode already chosen.
// $row = $stmt->fetchObject();
Keyed and flattened results
FETCH_COLUMN pulls a single column out of every row, and FETCH_KEY_PAIR turns a two-column result into a map of the first column to the second. Both save a loop whose only job was to reshape rows. FETCH_KEY_PAIR needs the query to select exactly two columns, and a duplicate key silently overwrites the earlier row.
<?php
declare(strict_types=1);
$stmt = $pdo->prepare('SELECT id, title FROM products ORDER BY title');
$stmt->execute();
// A flat list of one column: ['Keyboard', 'Monitor', 'Mouse']
$titles = $stmt->fetchAll(PDO::FETCH_COLUMN, 1);
$stmt->execute();
// id => title, ready for a <select> or an in-memory lookup.
$byId = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
Discussion