The PDO Fetch Modes That Delete Half Your Data-Mapping Code

Somewhere in every PHP codebase lives this loop:

$rows = $stmt->fetchAll();
$byId = [];
foreach ($rows as $row) {
    $byId[$row['id']] = $row;
}

PDO has been able to do that - and several fancier reshapes - since PHP 5, at fetch time, with zero loops. These four modes are the ones I actually use.

FETCH_KEY_PAIR: instant lookup maps

Two-column query in, key => value array out:

$prices = $pdo->query('SELECT sku, price FROM products')
              ->fetchAll(PDO::FETCH_KEY_PAIR);

// ['IMP-001' => 49.00, 'RASK-001' => 0.00, ...]

Settings tables, id => name dropdowns, slug => id resolvers - this is their one-liner. The statement must select exactly two columns, which is a feature: it documents intent right in the SQL.

FETCH_UNIQUE: rows indexed by id

$users = $pdo->query('SELECT id, name, email, role FROM users')
             ->fetchAll(PDO::FETCH_UNIQUE);

// [7 => ['name' => 'Anna', ...], 12 => ['name' => 'Ben', ...]]
$users[$order['user_id']]['name'];   // O(1) joins in PHP-land

The first selected column becomes the array key (and is removed from the row). This is the direct replacement for the foreach at the top of this article. Duplicate keys keep the first row - hence "unique".

FETCH_GROUP: one query, grouped output

$byStatus = $pdo->query('SELECT status, id, total, email FROM orders')
                ->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);

// ['paid' => [[...], [...]], 'pending' => [[...]], 'refunded' => [[...]]]

First column becomes the group key, rows collect underneath. Combine with FETCH_COLUMN for grouped single values - genuinely useful for tag maps:

$tagsPerPost = $pdo->query('SELECT post_id, tag FROM post_tags')
                   ->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN);

// [42 => ['php', 'pdo'], 43 => ['css']]

(JavaScript got this same convenience recently with Object.groupBy() - the backend had it first by two decades.)

FETCH_COLUMN alone, plus the honorable mention

$emails = $pdo->query('SELECT email FROM subscribers')
              ->fetchAll(PDO::FETCH_COLUMN);       // flat array of strings

$total = $pdo->query('SELECT SUM(total) FROM orders')
             ->fetchColumn();                       // single scalar, one call

Honorable mention to FETCH_CLASS (hydrate rows straight into objects - note it sets properties after the constructor by default) and to iterating the statement directly with foreach for constant-memory streaming instead of fetchAll.

None of this replaces an ORM in a big app. But for the reporting scripts, admin screens and CLI tools where PDO is the right size, these modes remove exactly the boilerplate that makes raw PDO feel clunky. The database driver was always willing to shape the data - we just never asked.