PDO Fetch Modes

PDO Fetch Modes What Are Fetch Modes? In simple terms: After a query runs, PDO hands you the result rows. A fetch mode decides what each row looks like: an associative array, a numbered array, an object, a single value, or even an object of your own class. Choosing the right mode often removes the need for loops that reshape data afterwards.

Technically: Fetch modes are PDO::FETCH_* constants passed to fetch(), fetchAll(), or setFetchMode(), or set as the connection default with PDO::ATTR_DEFAULT_FETCH_MODE. Some modes can be combined with the bitwise OR operator (PDO::FETCH_GROUP | PDO::FETCH_COLUMN).

The examples use the pdo() helper from PDO Connect and the products table:

id sku name price category 1 PEN-01 Gel Pen 20.00 stationery 2 NB-A5 Notebook A5 60.00 stationery 3 LAMP-01 Desk Lamp 1299.00 electronics 4 BAG-02 Backpack 899.00 bags 5 MOUSE-01 Wireless Mouse 599.00 electronics Fetching Methods Method Returns fetch($mode) The next row, or false when there are no more rows fetchAll($mode) All remaining rows as an array fetchColumn($index = 0) One column of the next row, or false fetchObject($class) The next row as an object foreach ($stmt as $row) Iterates rows in the statement's fetch mode Rows as Arrays: FETCH_ASSOC, FETCH_NUM, FETCH_BOTH <?php require DIR . '/src/pdo.php';

$pdo = pdo(); $sql = 'SELECT sku, name FROM products WHERE id = 1';

print_r($pdo->query($sql)->fetch(PDO::FETCH_ASSOC)); print_r($pdo->query($sql)->fetch(PDO::FETCH_NUM)); print_r($pdo->query($sql)->fetch(PDO::FETCH_BOTH)); Output Array ( [sku] => PEN-01 [name] => Gel Pen ) Array ( [0] => PEN-01 [1] => Gel Pen ) Array ( [sku] => PEN-01 [0] => PEN-01 [name] => Gel Pen [1] => Gel Pen ) Mode Keys Typical Use FETCH_ASSOC Column names The usual choice; set it as the connection default FETCH_NUM Column positions Compact processing, list() destructuring FETCH_BOTH Both (PDO's built-in default) Rarely useful; doubles memory per row Rows as Objects: FETCH_OBJ <?php require DIR . '/src/pdo.php';

$product = pdo()->query('SELECT sku, name, price FROM products WHERE id = 3')->fetch(PDO::FETCH_OBJ);

echo "{$product->name} ({$product->sku}) costs ₹{$product->price}\n"; echo get_class($product) . "\n"; Output Desk Lamp (LAMP-01) costs ₹1299.00 stdClass FETCH_OBJ returns stdClass objects with one property per column — the same data as FETCH_ASSOC, accessed with -> instead of [].

Single Columns: FETCH_COLUMN <?php require DIR . '/src/pdo.php';

$names = pdo()->query('SELECT name FROM products ORDER BY id')->fetchAll(PDO::FETCH_COLUMN); echo implode(', ', $names) . "\n";

$count = pdo()->query('SELECT COUNT(*) FROM products')->fetchColumn(); echo "Total products: $count\n"; Output Gel Pen, Notebook A5, Desk Lamp, Backpack, Wireless Mouse Total products: 5 fetchAll(PDO::FETCH_COLUMN) returns a flat list of one column's values; fetchColumn() returns a single value — ideal for counts and sums.

Key-Value Pairs: FETCH_KEY_PAIR <?php require DIR . '/src/pdo.php';

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

print_r($prices); echo "Desk Lamp price: ₹{$prices['LAMP-01']}\n"; Output Array ( [PEN-01] => 20.00 [NB-A5] => 60.00 [LAMP-01] => 1299.00 [BAG-02] => 899.00 [MOUSE-01] => 599.00 ) Desk Lamp price: ₹1299.00 The query must select exactly two columns: the first becomes the key, the second the value. This is perfect for lookup tables and options (id => name).

Grouping: FETCH_GROUP <?php require DIR . '/src/pdo.php';

$byCategory = pdo() ->query('SELECT category, name FROM products ORDER BY category, name') ->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN);

foreach ($byCategory as $category => $names) { echo "$category: " . implode(', ', $names) . "\n"; } Output bags: Backpack electronics: Desk Lamp, Wireless Mouse stationery: Gel Pen, Notebook A5 FETCH_GROUP uses the first column as the group key. Combined with FETCH_COLUMN, each group contains a list of the second column's values; combined with FETCH_ASSOC, each group contains full rows (without the grouping column).

Rows Indexed by ID: FETCH_UNIQUE <?php require DIR . '/src/pdo.php';

$products = pdo()->query('SELECT id, sku, name FROM products')->fetchAll(PDO::FETCH_UNIQUE);

print_r($products[3]); Output Array ( [sku] => LAMP-01 [name] => Desk Lamp ) FETCH_UNIQUE also uses the first column as the key, but expects it to be unique and stores one row per key. Looking up a product by ID afterwards is a simple array access — useful when combining data from several queries.

Objects of Your Own Class: FETCH_CLASS <?php require DIR . '/src/pdo.php';

final class ProductRow { public string $name; public string $price;

public function formattedPrice(): string { return '₹' . number_format((float) $this->price, 2); } }

$stmt = pdo()->query("SELECT name, price FROM products WHERE category = 'electronics' ORDER BY id"); $products = $stmt->fetchAll(PDO::FETCH_CLASS, ProductRow::class);

foreach ($products as $product) { echo "{$product->name}: {$product->formattedPrice()}\n"; } Output Desk Lamp: ₹1,299.00 Wireless Mouse: ₹599.00 How FETCH_CLASS Works PDO creates a ProductRow object for each row and assigns each column to the property with the same name. Properties are set before the constructor runs. Add PDO::FETCH_PROPS_LATE to run the constructor first. Columns without a matching declared property would become dynamic properties (deprecated since PHP 8.2), so select only columns the class declares. Property types must match the returned values — here, price is a DECIMAL and arrives as a string. For classes with constructors, readonly properties, or conversions (such as price in paise), mapping rows explicitly with a named constructor (Product::fromRow($row)) is usually clearer and safer (see MySQL Select Data).

Value Types in Results With pdo_mysql in PHP 8.1+, integer and float columns are returned as PHP int and float, both with emulated and native prepared statements. DECIMAL columns are returned as strings to preserve their exact value:

<?php require DIR . '/src/pdo.php';

$row = pdo()->query("SELECT id, stock, price FROM products WHERE sku = 'NB-A5'")->fetch(); var_dump($row); Output array(3) { ["id"]=> int(2) ["stock"]=> int(45) ["price"]=> string(5) "60.00" } fetchAll() vs Iteration fetchAll() loads every row into memory at once — convenient for small results. For large results (exports, reports), iterate the statement instead, so only one row is held in PHP at a time:

foreach (pdo()->query('SELECT id, email FROM customers ORDER BY id') as $row) { // write $row to a CSV file } (MySQL's PDO driver buffers query results by default; for very large exports, Pdo\Mysql::ATTR_USE_BUFFERED_QUERY => false streams rows from the server, with the same restrictions as unbuffered MySQLi queries.)

Common Mistakes Leaving FETCH_BOTH as the Default Effect: Duplicate data in every row and confusing print_r() output.

Fix: Set PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC.

FETCH_KEY_PAIR With More Than Two Columns Effect: An error is thrown.

Fix: Select exactly two columns.

Duplicate Group Keys With FETCH_UNIQUE Effect: Later rows overwrite earlier ones.

Fix: Use a unique first column, or FETCH_GROUP for one-to-many data.

Assuming Numeric Strings Are Numbers Effect: Strict comparisons fail with DECIMAL values.

Fix: Convert deliberately (for example, to integer paise).

Best Practices Use FETCH_ASSOC as the default fetch mode. Use fetchColumn(), FETCH_COLUMN, and FETCH_KEY_PAIR to get exactly the shape you need. Use FETCH_GROUP and FETCH_UNIQUE to build indexed or grouped structures without extra loops. Prefer explicit mapping to domain objects over FETCH_CLASS for complex classes. Iterate large results instead of loading them with fetchAll(). Official reference: https://www.php.net/manual/en/pdostatement.fetch.php