PHP Database

PDO is the standard database abstraction layer. It supports 12+ drivers (MySQL, PostgreSQL, SQLite…) with a unified API.

Connection Setup

function createConnection(): PDO
{
    $dsn = sprintf(
        'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
        getenv('DB_HOST'),
        getenv('DB_PORT') ?: 3306,
        getenv('DB_NAME'),
    );

    return new PDO($dsn, getenv('DB_USER'), getenv('DB_PASS'), [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // throw on error
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // assoc arrays
        PDO::ATTR_EMULATE_PREPARES   => false,                  // real prepared stmts
        PDO::ATTR_PERSISTENT         => true,                   // connection pool
    ]);
}

Prepared Statements

Prepared statements send SQL structure and data separately — eliminating SQL injection by design.

// NEVER do this — SQL injection! ❌
$name = $_GET['name']; // "'; DROP TABLE users;--"
$sql  = "SELECT * FROM users
  WHERE name = '$name'";
$pdo->query($sql); // 💀

// Named placeholders — safe ✅
$stmt = $pdo->prepare(
    'SELECT * FROM users WHERE name = :name'
);
$stmt->execute([':name' => $_GET['name']]);
$user = $stmt->fetch();

// Reuse prepared statement in loop — parsed once, executed N times
$insert = $pdo->prepare(
    'INSERT INTO events (user_id, action, created_at) VALUES (:uid, :act, NOW())'
);

foreach ($events as $event) {
    $insert->execute([
        ':uid' => $event['user_id'],
        ':act' => $event['action'],
    ]);
}

Fetch Modes

$stmt = $pdo->query('SELECT id, name, email FROM users LIMIT 10');

// FETCH_ASSOC — plain array (most common)
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// FETCH_CLASS — map directly to a class
$users = $stmt->fetchAll(PDO::FETCH_CLASS, User::class);

// FETCH_KEY_PAIR — [id => name] map
$map = $stmt->fetchAll(PDO::FETCH_KEY_PAIR); // [1=>'Alice', 2=>'Bob']

// FETCH_COLUMN — single column as flat array
$ids = $pdo->query('SELECT id FROM users')->fetchAll(PDO::FETCH_COLUMN);

Transactions guarantee ACID properties: all operations succeed together, or all are rolled back atomically.

$pdo->beginTransaction();
try {
    // Debit sender
    $pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE id = :id')
        ->execute([':amount' => $amount, ':id' => $from]);

    // Credit receiver
    $pdo->prepare('UPDATE accounts SET balance = balance + :amount WHERE id = :id')
        ->execute([':amount' => $amount, ':id' => $to]);

    // Log transfer
    $pdo->prepare('INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)')
        ->execute([$from, $to, $amount]);

    $pdo->commit();   // All three succeed atomically ✅
} catch (PDOException $e) {
    $pdo->rollBack(); // Undo everything if any step fails ✅
    throw $e;
}

Isolation Levels

The N+1 problem occurs when fetching N records then making 1 additional query per record to load related data — producing N+1 total queries against the database.

Solving N+1 in Raw PHP

// N+1: one query per post ❌
$posts = fetchAll('SELECT * FROM posts');

foreach ($posts as &$post) {
    // NEW QUERY for every post!
    $post['author'] = fetchOne(
        'SELECT * FROM users WHERE id = ?',
        [$post['user_id']]
    );
}

// Eager: 2 queries total ✅
$posts = fetchAll('SELECT * FROM posts');

// Collect all unique user IDs
$ids = array_unique(array_column($posts, 'user_id'));

// Single IN() query
$in    = implode(',', array_fill(0, count($ids), '?'));
$users = fetchAll("SELECT * FROM users WHERE id IN($in)", $ids);

// Index by id for O(1) lookup
$byId  = array_column($users, null, 'id');

foreach ($posts as &$post) {
    $post['author'] = $byId[$post['user_id']];
}

An index is a B-tree data structure maintained alongside the table. It trades write overhead for dramatically faster reads on filtered or sorted columns.

-- Create indexes strategically
CREATE INDEX idx_users_email      ON users (email);
CREATE INDEX idx_posts_user_date  ON posts (user_id, created_at); -- composite
CREATE UNIQUE INDEX idx_tokens    ON api_tokens (token);
CREATE FULLTEXT INDEX idx_search  ON articles (title, body);

-- Left-prefix rule: index (a, b, c) serves:
-- WHERE a=1             ✅ uses index
-- WHERE a=1 AND b=2     ✅ uses index
-- WHERE b=2             ❌ cannot skip first column

-- Covering index: include all columns the query needs
CREATE INDEX idx_covering ON posts (user_id, created_at, title);
-- Query satisfied from index alone — no table lookup needed

Reading EXPLAIN

EXPLAIN ANALYZE
SELECT p.title, u.name
FROM   posts p
JOIN   users u ON u.id = p.user_id
WHERE  p.created_at > '2024-01-01'
ORDER BY p.created_at DESC
LIMIT  20;

-- id | type   | table | key               | rows | Extra
-- 1  | SIMPLE | p     | idx_posts_created | 142  | Using index
-- 1  | SIMPLE | u     | PRIMARY           | 1    | Using index

// Repository Pattern — decouples domain logic from persistence
interface UserRepository
{
    public function findById(int $id): ?User;
    public function findByEmail(string $email): ?User;
    public function save(User $user): void;
    public function delete(int $id): void;
}

class PdoUserRepository implements UserRepository
{
    public function __construct(private readonly PDO $db) {}

    public function findById(int $id): ?User
    {
        $stmt = $this->db->prepare('SELECT * FROM users WHERE id = :id');
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row ? User::fromArray($row) : null;
    }
}

// Batch insert — much faster than N individual inserts
$pdo->prepare('
    INSERT INTO events (user_id, action) VALUES (?, ?), (?, ?), (?, ?)
')->execute([$uid1, $act1, $uid2, $act2, $uid3, $act3]);

// Upsert — INSERT or UPDATE on conflict
$pdo->prepare('
    INSERT INTO page_views (page, count) VALUES (:page, 1)
    ON DUPLICATE KEY UPDATE count = count + 1
')->execute([':page' => $page]);

// ❌ escaping is NOT enough — don't do this
$name = $pdo->quote($_GET['name']);
$sql  = "SELECT * FROM users WHERE name = $name"; // fragile

// ✅ prepared statement — structurally safe
$stmt = $pdo->prepare('SELECT * FROM users WHERE name = ?');
$stmt->execute([$_GET['name']]);

// ✅ dynamic column names cannot be parameterised — use an allowlist
$allowed = ['name', 'email', 'created_at'];
$col     = $_GET['sort'] ?? 'name';
if (!in_array($col, $allowed, true)) {
    throw new InvalidArgumentException("Invalid sort column");
}
$sql = "SELECT * FROM users ORDER BY $col"; // safe — from allowlist