databases storage and caching

Fetch Modes

For most beginner and junior PHP work, PDO::FETCH_ASSOC is the clearest default because each row is an associative array keyed by column name.

Associative Rows

PHP example

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL)');
$pdo->exec("INSERT INTO users (email) VALUES ('sam@example.com')");

$row = $pdo->query('SELECT id, email FROM users')->fetch();

echo $row['email'] . PHP_EOL;

// Prints:
// sam@example.com

With FETCH_ASSOC, there are no duplicate numeric keys, and code reads by column name.

Fetching One Column

Use fetchColumn() for counts, IDs, names, or any query where the result is one value.

PHP example

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL)');
$pdo->exec("INSERT INTO users (email) VALUES ('a@example.com'), ('b@example.com')");

$count = $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();

echo 'users=' . $count . PHP_EOL;

// Prints:
// users=2

Do not fetch a full row just to read one value.

Fetching All Rows

fetchAll() is convenient for small result sets. It loads every row into memory.

PHP example

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$pdo->exec('CREATE TABLE tags (id INTEGER PRIMARY KEY, name TEXT NOT NULL)');
$pdo->exec("INSERT INTO tags (name) VALUES ('php'), ('sql')");

$tags = $pdo->query('SELECT name FROM tags ORDER BY name')->fetchAll(PDO::FETCH_COLUMN);

print_r($tags);

// Prints:
// [0] => php
// [1] => sql

For large exports or long-running jobs, iterate over rows instead of loading everything at once.

Objects And Classes

PDO can fetch objects, but be deliberate. Arrays are often easier for simple database access. Objects are useful when your codebase already has a clear data object pattern.

PHP example

<?php

declare(strict_types=1);

final class UserRow
{
    public int $id;
    public string $email;
}

echo UserRow::class . PHP_EOL;

// Prints:
// UserRow

If you fetch into classes, make sure property names and database column names match your expectations.

False Means No Row

fetch() returns false when there is no row. Handle that case before reading array keys.

PHP example

<?php

declare(strict_types=1);

function emailFromRow(array|false $row): ?string
{
    if ($row === false) {
        return null;
    }

    return (string) $row['email'];
}

var_dump(emailFromRow(false));
var_dump(emailFromRow(['email' => 'sam@example.com']));

// Prints:
// NULL
// string(15) "sam@example.com"

Ignoring the false case is a common source of warnings and broken not-found handling.

What To Check

Before moving on, make sure you can:

Set PDO::ATTR_DEFAULT_FETCH_MODE to PDO::FETCH_ASSOC.
Use fetch() for one row.
Use fetchColumn() for one value.
Use fetchAll(PDO::FETCH_COLUMN) for a small list of values.
Avoid fetchAll() for very large results.
Handle false when a query returns no rows.

Practice

Practice: Choose The Right Fetch Shape

Write a self-contained PHP script that demonstrates different PDO fetch shapes.

Requirements

Use SQLite in memory.
Set default fetch mode to PDO::FETCH_ASSOC.
Fetch one user row with fetch().
Fetch a count with fetchColumn().
Fetch a list of emails with fetchAll(PDO::FETCH_COLUMN).
Handle a missing row without reading array keys from false.

Show solution

This solution uses a different fetch method for each result shape.

PHP example

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL)');
$pdo->exec("INSERT INTO users (email) VALUES ('sam@example.com'), ('lee@example.com')");

$oneUser = $pdo->query('SELECT id, email FROM users WHERE id = 1')->fetch();
echo $oneUser['email'] . PHP_EOL;

$count = $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();
echo 'count=' . $count . PHP_EOL;

$emails = $pdo->query('SELECT email FROM users ORDER BY email')->fetchAll(PDO::FETCH_COLUMN);
echo implode(', ', $emails) . PHP_EOL;

$missing = $pdo->query('SELECT id, email FROM users WHERE id = 999')->fetch();

if ($missing === false) {
    echo 'missing user' . PHP_EOL;
}

// Prints:
// sam@example.com
// count=2
// lee@example.com, sam@example.com
// missing user

The code uses fetch() for one row, fetchColumn() for one value, and fetchAll(PDO::FETCH_COLUMN) for a small one-column list.