In PHP, what do PDO's FETCH_ASSOC, FETCH_OBJ, FETCH_COLUMN and FETCH_KEY_PAIR fetch modes return, and when does each fit?

middleshould knowfreq 52%

answer

FETCH_BOTH is the default: duplicates
stdClass vs name-keyed array
one column as a flat list
exactly two columns: key => value
FETCH_GROUP and FETCH_UNIQUE use column one

basics

~10 s

FETCH_ASSOC gives name-keyed arrays, FETCH_OBJ stdClass objects, FETCH_COLUMN a flat list of one column, and FETCH_KEY_PAIR a map from the first of exactly two columns to the second. The default, FETCH_BOTH, duplicates every value.

solid answer

~40 s

A fetch mode decides the shape of each row. FETCH_ASSOC returns arrays keyed by column name, the usual choice for application code. FETCH_OBJ returns stdClass objects, the same data with -> access. FETCH_COLUMN, used with fetchAll(), returns a flat list of one column (the first unless you pass an index), ideal for a list of ids. FETCH_KEY_PAIR requires exactly two columns and returns [first => second], perfect for a dropdown of doctor id to name; duplicate keys overwrite. The built-in default is FETCH_BOTH, which keys each value by name and by position, so rows are twice as big and awkward to dump or encode; that is why most projects set PDO::ATTR_DEFAULT_FETCH_MODE to FETCH_ASSOC. fetch() returns false once rows run out, and fetchAll() returns an empty array for no rows.

A fetch mode decides the shape of each row. **`FETCH_ASSOC`** returns arrays keyed by column name, the usual choice for application code. **`FETCH_OBJ`** returns `stdClass` objects, the same data with `->` access. **`FETCH_COLUMN`**, used with `fetchAll()`, returns a flat list of one column (the first unless you pass an index), ideal for a list of ids. **`FETCH_KEY_PAIR`** requires exactly two columns and returns `[first => second]`, perfect for a dropdown of doctor id to name; duplicate keys overwrite. The built-in default is **`FETCH_BOTH`**, which keys each value by name and by position, so rows are twice as big and awkward to dump or encode; that is why most projects set `PDO::ATTR_DEFAULT_FETCH_MODE` to `FETCH_ASSOC`. `fetch()` returns `false` once rows run out, and `fetchAll()` returns an empty array for no rows.

code

php · 14 lines

php

<?php
declare(strict_types=1);

/** @var PDO $pdo */
$doctors = $pdo->query('SELECT id, full_name FROM doctors ORDER BY full_name')
    ->fetchAll(PDO::FETCH_KEY_PAIR);          // [7 => 'Dr Okafor', 3 => 'Dr Silva']

$stmt = $pdo->prepare('SELECT id FROM appointments WHERE starts_at::date = CURRENT_DATE AND doctor_id = ?');
$stmt->execute([7]);
$todayIds = $stmt->fetchAll(PDO::FETCH_COLUMN);   // [101, 102, 108]

$byDoctor = $pdo->query('SELECT doctor_id, id, starts_at FROM appointments')
    ->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);
// [7 => [['id' => 101, 'starts_at' => ...], ...], 3 => [...]]

go deeper

for a junior

Know FETCH_ASSOC and FETCH_OBJ, that fetch() returns false at the end, and that the default returns each value twice.

for a middle

Pick the mode by shape: FETCH_COLUMN for id lists, FETCH_KEY_PAIR for maps, FETCH_GROUP and FETCH_UNIQUE for indexed results, and alias duplicate columns.

for a senior

Watch memory on large results, the overwrite behaviour of key-based modes, and where the default fetch mode is set so behaviour is predictable across statements.

for a principal

Decide whether raw arrays may cross layer boundaries at all, or whether the data layer must return typed objects, and make the fetch default part of that rule.

What a fetch mode is

When a PDOStatement produces rows, the fetch mode decides what PHP value each row becomes. You set it in three places, from broadest to narrowest:

PDO::ATTR_DEFAULT_FETCH_MODE on the connection, for every statement.
$stmt->setFetchMode(...), for one statement, including foreach ($stmt as $row).
The $mode argument of fetch() or fetchAll(), for one call.

PDOStatement::setAttribute() cannot set the default fetch mode: it only accepts driver-specific attributes and silently ignores the rest.

The everyday modes

Mode	One row becomes	Typical use in a clinic-booking app
`PDO::FETCH_BOTH` (default)	array keyed by name and by position	rarely wanted
`PDO::FETCH_ASSOC`	`['id' => 7, 'full_name' => 'Dr Okafor']`	general application code
`PDO::FETCH_NUM`	`[7, 'Dr Okafor']`	positional unpacking, CSV export
`PDO::FETCH_OBJ`	`stdClass` with `->id`, `->full_name`	templates that prefer `->`
`PDO::FETCH_COLUMN`	one scalar per row	ids of today's appointments
`PDO::FETCH_KEY_PAIR`	contributes `key => value` to one map	doctor id => name for a dropdown

Notes on the less obvious ones:

FETCH_BOTH makes every row twice as large, and dumping it or passing it to json_encode() produces both the named and the numbered keys. That alone is reason to change the default.
FETCH_COLUMN takes an optional column index as the next argument, fetchAll(PDO::FETCH_COLUMN, 1). An index that does not exist throws ValueError.
FETCH_KEY_PAIR requires the result to have exactly two columns; anything else is an error ("requires the result set to contain exactly 2 columns"). If the first column repeats, later rows overwrite earlier ones.

Grouping and indexing with fetchAll()

Two flags work only with fetchAll() and consume the first column:

PDO::FETCH_GROUP, combined with a mode such as FETCH_ASSOC, returns [first-column value => list of rows], for example appointments grouped by doctor id. The first column is used as the key and left out of each row unless you select it twice.
PDO::FETCH_UNIQUE returns one row per first-column value, keyed by it, for example patients indexed by id.

Reading results correctly

fetch() returns the next row, or false when no rows are left. A while ($row = $stmt->fetch()) loop relies on that.
fetchAll() returns an array, empty when nothing matched, never false for "no rows".
fetchColumn() returns one column of the next row, or false when no rows are left. A NULL column comes back as null, which is distinct from false, but a stored boolean false can be ambiguous, so compare strictly.
Duplicate column names from a join collapse into one key, and which value wins is undefined. Alias them in SQL (d.name AS doctor_name, p.name AS patient_name), or use PDO::FETCH_NAMED to receive both values as a list.

FETCH_UNIQUE and FETCH_OBJ in practice

$pdo->query('SELECT id, full_name, phone FROM patients')->fetchAll(PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC) returns [12 => ['full_name' => ..., 'phone' => ...], 15 => [...]]: a lookup table keyed by patient id, with the id itself left out of each row. If two rows share the first column, the later one replaces the earlier one.

FETCH_OBJ is handy in templates ($row->full_name), but the result is a stdClass with no declared properties, so static analysis cannot check property names, and a typo reads as an undefined property warning rather than a type error. Arrays with FETCH_ASSOC have the same weakness; typed objects built by your own mapping code do not.

Choosing per call versus per statement

A one-off shape, such as a FETCH_KEY_PAIR dropdown, belongs in the fetchAll() call.
A statement iterated with foreach needs setFetchMode(), because foreach has no mode argument.
The connection default should be the shape most code expects, usually FETCH_ASSOC.

Memory

fetchAll() materialises every row in a PHP array at once. For a report over months of appointments, iterate with fetch() or foreach instead so only one row is converted at a time. With MySQL, results are buffered on the client by default, so the rows are still transferred up front; switching that off is a driver-specific option with its own trade-offs.

## What a fetch mode is When a `PDOStatement` produces rows, the **fetch mode** decides what PHP value each row becomes. You set it in three places, from broadest to narrowest: 1. `PDO::ATTR_DEFAULT_FETCH_MODE` on the connection, for every statement. 2. `$stmt->setFetchMode(...)`, for one statement, including `foreach ($stmt as $row)`. 3. The `$mode` argument of `fetch()` or `fetchAll()`, for one call. `PDOStatement::setAttribute()` cannot set the default fetch mode: it only accepts driver-specific attributes and silently ignores the rest. ## The everyday modes | Mode | One row becomes | Typical use in a clinic-booking app | |---|---|---| | `PDO::FETCH_BOTH` (default) | array keyed by name **and** by position | rarely wanted | | `PDO::FETCH_ASSOC` | `['id' => 7, 'full_name' => 'Dr Okafor']` | general application code | | `PDO::FETCH_NUM` | `[7, 'Dr Okafor']` | positional unpacking, CSV export | | `PDO::FETCH_OBJ` | `stdClass` with `->id`, `->full_name` | templates that prefer `->` | | `PDO::FETCH_COLUMN` | one scalar per row | ids of today's appointments | | `PDO::FETCH_KEY_PAIR` | contributes `key => value` to one map | doctor id => name for a dropdown | Notes on the less obvious ones: - **`FETCH_BOTH`** makes every row twice as large, and dumping it or passing it to `json_encode()` produces both the named and the numbered keys. That alone is reason to change the default. - **`FETCH_COLUMN`** takes an optional column index as the next argument, `fetchAll(PDO::FETCH_COLUMN, 1)`. An index that does not exist throws `ValueError`. - **`FETCH_KEY_PAIR`** requires the result to have **exactly two columns**; anything else is an error ("requires the result set to contain exactly 2 columns"). If the first column repeats, later rows overwrite earlier ones. ## Grouping and indexing with fetchAll() Two flags work only with `fetchAll()` and consume the **first column**: - **`PDO::FETCH_GROUP`**, combined with a mode such as `FETCH_ASSOC`, returns `[first-column value => list of rows]`, for example appointments grouped by doctor id. The first column is used as the key and left out of each row unless you select it twice. - **`PDO::FETCH_UNIQUE`** returns one row per first-column value, keyed by it, for example patients indexed by id. ## Reading results correctly - `fetch()` returns the next row, or **`false`** when no rows are left. A `while ($row = $stmt->fetch())` loop relies on that. - `fetchAll()` returns an **array**, empty when nothing matched, never `false` for "no rows". - `fetchColumn()` returns one column of the next row, or `false` when no rows are left. A `NULL` column comes back as `null`, which is distinct from `false`, but a stored boolean false can be ambiguous, so compare strictly. - **Duplicate column names** from a join collapse into one key, and which value wins is undefined. Alias them in SQL (`d.name AS doctor_name, p.name AS patient_name`), or use `PDO::FETCH_NAMED` to receive both values as a list. ## FETCH_UNIQUE and FETCH_OBJ in practice `$pdo->query('SELECT id, full_name, phone FROM patients')->fetchAll(PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC)` returns `[12 => ['full_name' => ..., 'phone' => ...], 15 => [...]]`: a lookup table keyed by patient id, with the id itself left out of each row. If two rows share the first column, the later one replaces the earlier one. `FETCH_OBJ` is handy in templates (`$row->full_name`), but the result is a `stdClass` with no declared properties, so static analysis cannot check property names, and a typo reads as an undefined property warning rather than a type error. Arrays with `FETCH_ASSOC` have the same weakness; typed objects built by your own mapping code do not. ## Choosing per call versus per statement - A one-off shape, such as a `FETCH_KEY_PAIR` dropdown, belongs in the `fetchAll()` call. - A statement iterated with `foreach` needs `setFetchMode()`, because `foreach` has no mode argument. - The connection default should be the shape most code expects, usually `FETCH_ASSOC`. ## Memory `fetchAll()` materialises every row in a PHP array at once. For a report over months of appointments, iterate with `fetch()` or `foreach` instead so only one row is converted at a time. With MySQL, results are buffered on the client by default, so the rows are still transferred up front; switching that off is a driver-specific option with its own trade-offs.

Why is FETCH_KEY_PAIR dangerous when the first column is not unique?
The first column becomes the array key, and PHP arrays hold one value per key, so later rows overwrite earlier ones and the result silently has fewer entries than the query returned. Use it only on a unique key, or switch to `FETCH_GROUP` when several values per key are expected.
A join selects doctors.name and patients.name. What does FETCH_ASSOC give you?
Only one `name` key, because an array cannot hold two entries with the same key, and which of the two values survives is undefined. Alias the columns in SQL, for example `d.name AS doctor_name`, or fetch with `PDO::FETCH_NAMED`, which returns both values under `name` as a list.

saying these in an interview costs you the question

PDO returns name-keyed arrays by default
fetchAll() returns false when the query matched no rows
FETCH_KEY_PAIR works with any number of columns
PDOStatement::setAttribute() can set the statement's default fetch mode
fetchAll() is fine for streaming a very large report

In PHP, what do PDO's FETCH_ASSOC, FETCH_OBJ, FETCH_COLUMN and FETCH_KEY_PAIR fetch modes return, and when does each fit?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

What a fetch mode is

The everyday modes

Grouping and indexing with fetchAll()

Reading results correctly

FETCH_UNIQUE and FETCH_OBJ in practice

Choosing per call versus per statement

Memory

▾common follow-ups2 questions

▾red flags5 flags

What a fetch mode is

The everyday modes

Grouping and indexing with fetchAll()

Reading results correctly

FETCH_UNIQUE and FETCH_OBJ in practice

Choosing per call versus per statement

Memory

answer

go deeper