PDO fetch() and fetchAll(): Read Query Results

After you run a SELECT, you read the rows from the statement. PDO has several ways.

fetch(): one row at a time

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

$statement = $pdo->query("SELECT id, name FROM users ORDER BY id");

while ($row = $statement->fetch()) {
    echo $row["id"] . ": " . $row["name"] . "\n";
}

Output

OUTPUT

1
2
3

1: Ana
2: Ben
3: Cy

fetch() returns the next row, and false when there are no more. It uses little memory, because only one row is in PHP at a time.

fetchAll(): everything at once

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

$users = $pdo->query("SELECT id, name FROM users ORDER BY id")->fetchAll();

echo count($users) . " users, first: " . $users[0]["name"];

Output

OUTPUT

3 users, first: Ana

Fine for small results. For very large results, loop with fetch() or foreach.

fetchColumn(): one value

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

echo $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();

Output

OUTPUT

foreach directly on the statement

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

foreach ($pdo->query("SELECT name FROM users ORDER BY name") as $row) {
    echo $row["name"] . " ";
}

Output

OUTPUT

Ana Ben Cy

Fetch modes

Mode	Row looks like
`PDO::FETCH_ASSOC`	`["id" => 1, "name" => "Ana"]`
`PDO::FETCH_NUM`	`[1, "Ana"]`
`PDO::FETCH_BOTH`	Both (the PDO default, usually not wanted)
`PDO::FETCH_OBJ`	An object: `$row->name`
`PDO::FETCH_CLASS`	An object of your own class
`PDO::FETCH_KEY_PAIR`	`[id => name]` for two columns
`PDO::FETCH_COLUMN`	A flat array of one column

Set FETCH_ASSOC as the default in your connection options (see Database Connections), and override it when you need something else.

Objects

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

$user = $pdo->query("SELECT name, email FROM users WHERE id = 1")->fetch(PDO::FETCH_OBJ);

echo $user->name . " <" . $user->email . ">";

Output

OUTPUT

Ana <ana@example.com>

Into your own class

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

class Product
{
    public int $id;
    public string $name;
    public int $stock;

    public function inStock(): bool
    {
        return $this->stock > 0;
    }
}

$statement = $pdo->query("SELECT id, name, stock FROM products ORDER BY id LIMIT 3");
$products = $statement->fetchAll(PDO::FETCH_CLASS, Product::class);

foreach ($products as $p) {
    echo $p->name . ": " . ($p->inStock() ? "available" : "sold out") . "\n";
}

Output

OUTPUT

1
2
3

Notebook: available
Pen: available
Bag: sold out

PDO fills the properties by column name. The result is real Product objects with your methods.

Key and value pairs

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

$names = $pdo->query("SELECT id, name FROM users")->fetchAll(PDO::FETCH_KEY_PAIR);

print_r($names);

Output

OUTPUT

Array
(
    [1] => Ana
    [2] => Ben
    [3] => Cy
)

Perfect for a <select> list.

One column as a list

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

$emails = $pdo->query("SELECT email FROM users ORDER BY id")->fetchAll(PDO::FETCH_COLUMN);

echo implode(", ", $emails);

Output

OUTPUT

ana@example.com, ben@example.com, cy@example.com

Group rows by a column

PDO::FETCH_GROUP uses the first column as the group key.

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

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

foreach ($grouped as $category => $names) {
    echo $category . ": " . implode(", ", $names) . "\n";
}

Output

OUTPUT

1
2
3

bags: Bag
paper: Diary, Notebook
writing: Marker, Pen

Types

With ATTR_EMULATE_PREPARES => false, integer columns come back as PHP int. DECIMAL columns come back as strings to keep their exact value. Convert on purpose when you need a number.

lightbulb

Pick the smallest tool

One value: fetchColumn(). One row: fetch(). A list of one column: FETCH_COLUMN. A table: fetchAll().