PDO Fetch Modes

Also Known As

TL;DR

Explanation

PDO::FETCH_ASSOC returns associative arrays (column name keys). PDO::FETCH_OBJ returns stdClass objects. PDO::FETCH_CLASS maps rows directly into a specified class. PDO::FETCH_COLUMN returns a single column as a flat array. PDO::FETCH_KEY_PAIR returns a two-column result as key→value pairs. Setting PDO::ATTR_DEFAULT_FETCH_MODE on the connection avoids repeating the constant on every fetch() call.

Common Misconception

Why It Matters

Common Mistakes

• Using the default FETCH_BOTH which returns both numeric and named keys, wasting memory.
• Forgetting to set a default fetch mode and using different modes inconsistently across the codebase.
• Using FETCH_OBJ on untrusted data where property names may conflict with magic methods.

Avoid When

• Avoid FETCH_BOTH — it duplicates every value under both numeric and named keys, wasting memory.
• Avoid FETCH_OBJ on untrusted column names — property names from user-controlled data can shadow object methods.

When To Use

• Use FETCH_ASSOC as the default for all queries — explicit, predictable, no memory waste.
• Use FETCH_CLASS when mapping query results directly to domain model objects.
• Use FETCH_KEY_PAIR when building id→name lookup maps from two-column queries.

Code Examples

// FETCH_BOTH wastes memory — every value stored twice
$row = $stmt->fetch(PDO::FETCH_BOTH); // ['id'=>1, 0=>1, 'email'=>'...', 1=>'...']

$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

// FETCH_ASSOC — safe default
$row = $stmt->fetch(); // ['id' => 1, 'email' => '...']

// FETCH_CLASS — auto-map to domain object
$stmt->setFetchMode(PDO::FETCH_CLASS, User::class);
$user = $stmt->fetch(); // User instance with properties set

// FETCH_COLUMN — flat array of one column
$emails = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);

// FETCH_KEY_PAIR — id => name map
$map = $pdo->query('SELECT id, name FROM roles')->fetchAll(PDO::FETCH_KEY_PAIR);

References

• ↗ https://www.php.net/manual/en/pdostatement.fetch.php

Tags

Related Terms