Chapter 16: Database Access with PDO

PHP Data Objects (PDO) provides a consistent interface for working with many databases using the same set of classes and methods. You write one set of calls that target MySQL, PostgreSQL, SQLite, SQL Server, and more, then switch by altering the Data Source Name (DSN) and a few options. This chapter explains connecting, preparing queries, handling transactions, fetching rows, dealing with errors, and organizing a small repository layer.

Connecting and DSNs

A PDO connection requires a DSN string, a username, a password, and often an array of options. The DSN identifies the driver and connection parameters. You can store credentials securely outside your document root, or inject them via environment variables.

Understanding DSN formats for common drivers

Each driver has its own DSN shape. The following examples show typical forms. Replace host, port, file paths, and database names with values that match your environment.

// MySQL or MariaDB
$dsn = 'mysql:host=localhost;port=3306;dbname=example;charset=utf8mb4';

// PostgreSQL
$dsn = 'pgsql:host=localhost;port=5432;dbname=example';

// SQLite (file)
$dsn = 'sqlite:/path/to/database.sqlite';

// SQL Server (sqlsrv)
$dsn = 'sqlsrv:Server=localhost,1433;Database=example';

Creating a robust connection using options

Options can improve safety and performance. Use exceptions for errors, set a sensible default fetch mode, and request native prepared statements when appropriate.

$options = [
  PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  PDO::ATTR_EMULATE_PREPARES   => false
];

$pdo = new PDO($dsn, $user, $pass, $options);

Prepared Statements and Binding

Prepared statements separate query structure from data, reducing the risk of SQL injection and improving performance when you execute the same statement many times. You can bind by position or by name, and you can specify parameter types to help the driver cast values correctly.

Preparing queries and executing with placeholders

Placeholders keep user data out of the SQL text. You prepare once, then execute with values that match the placeholders.

// Positional placeholders
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ? AND active = ?');
$stmt->execute([$email, 1]);
$rows = $stmt->fetchAll();

// Named placeholders
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND active = :active');
$stmt->execute([':email' => $email, ':active' => 1]);

Binding values with explicit PDO::PARAM_* types

Explicit types reduce ambiguity when the driver converts PHP values to SQL values. Use bindValue() for immediate values, and bindParam() when you want to bind a variable by reference that may change before execution.

$stmt = $pdo->prepare('INSERT INTO logs (user_id, message, created_at) VALUES (:uid, :msg, :ts)');
$stmt->bindValue(':uid', $userId, PDO::PARAM_INT);
$stmt->bindValue(':msg', $message, PDO::PARAM_STR);
$stmt->bindValue(':ts', $timestamp, PDO::PARAM_STR);
$stmt->execute();

Executing many rows efficiently

For repetitive inserts or updates, keep the statement prepared and call execute() repeatedly inside a transaction for better throughput.

$pdo->beginTransaction();
$stmt = $pdo->prepare('INSERT INTO visits (user_id, path) VALUES (?, ?)');
foreach ($batch as [$uid, $path]) {
  $stmt->execute([$uid, $path]);
}
$pdo->commit();

Transactions and Isolation

Transactions group multiple statements into a single unit of work. If any step fails, the whole group can be rolled back. Isolation levels control how concurrent transactions interact, which affects consistency and performance.

Starting, committing, and rolling back

Use the trio of beginTransaction(), commit(), and rollBack() to protect critical sequences. Wrap the work in a try and ensure rollback occurs on failure.

try {
  $pdo->beginTransaction();

  $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')
      ->execute([$amount, $fromId]);

  $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')
      ->execute([$amount, $toId]);

  $pdo->commit();
} catch (Throwable $e) {
  if ($pdo->inTransaction()) {
    $pdo->rollBack();
  }
  // Re-throw or log the error, then handle it upstream
  throw $e;
}

Selecting an isolation level with SQL

PDO does not set isolation levels directly. You issue an SQL command that your engine understands, then start the transaction. Choose a level that balances correctness with concurrency needs.

// Example: PostgreSQL or SQL Server style
$pdo->exec('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
$pdo->beginTransaction();
// perform work...
$pdo->commit();

Fetching Modes and Iteration

PDO can return rows in many shapes. Associative arrays are convenient for most PHP code, numeric arrays are compact, and objects can be useful for mapping to simple data carriers or DTOs. Choose a default fetch mode that fits, then override per statement when needed.

Selecting a suitable PDO::FETCH_* mode

The following table summarizes common fetch modes. You can set a default on the connection, then change per statement with setFetchMode() or by passing the mode to fetch().

Iterating rows efficiently

Use a forward loop that calls fetch() repeatedly to keep memory usage low. For large exports, stream rows rather than loading everything at once.

$stmt = $pdo->query('SELECT id, email FROM users ORDER BY id');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  // process $row['id'], $row['email']
}

Fetching into classes with PDO::FETCH_CLASS

Mapping directly to a simple class can reduce array indexing noise in your code. For complex domains, prefer an explicit mapper or a repository that constructs entities carefully.

class User {
  public int $id;
  public string $email;
}

$stmt = $pdo->query('SELECT id, email FROM users');
$stmt->setFetchMode(PDO::FETCH_CLASS, User::class);
foreach ($stmt as $user) {
  // $user is a User instance
}

Error Modes and Exceptions

PDO supports several error modes. Exception mode pairs well with structured error handling and keeps your control flow clear. Silent and warning modes can hide problems or spread checks across your codebase, which increases the chance of missed failures.

Choosing an error mode with PDO::ATTR_ERRMODE

Set the error mode on the connection, then rely on exceptions to signal failure. The summary table helps you pick a strategy that matches your application style.

Handling PDOException safely

Catch exceptions at boundaries where you can recover or translate them into user-friendly messages. Log the details, avoid exposing SQL text to end users, and ensure transactions are closed properly.

try {
  $stmt = $pdo->prepare('SELECT * FROM orders WHERE id = :id');
  $stmt->execute([':id' => $orderId]);
  $order = $stmt->fetch();
  if (!$order) {
    // handle not found
  }
} catch (PDOException $e) {
  // log $e->getMessage() securely
  http_response_code(500);
  echo 'Database error occurred.';
}

Lightweight Repository Pattern

A small repository module centralizes SQL and reduces duplication. The repository receives a PDO instance, exposes intent-focused methods, and returns arrays or simple objects. Keep it light, keep it testable, and avoid hiding SQL so deeply that performance tuning becomes difficult.

Defining a minimal repository API

The class below wraps a user table with a few operations. It uses prepared statements, typed parameters, and concise return shapes. Expand the pattern gradually rather than inventing a complex abstraction early.

final class UserRepository
{
  public function __construct(private PDO $pdo) {}

  public function findById(int $id): ?array
  {
    $stmt = $this->pdo->prepare('SELECT id, email, active FROM users WHERE id = :id');
    $stmt->execute([':id' => $id]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
  }

  public function findActiveByEmail(string $email): ?array
  {
    $stmt = $this->pdo->prepare(
      'SELECT id, email, active FROM users WHERE email = :email AND active = 1'
    );
    $stmt->execute([':email' => $email]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
  }

  public function create(string $email): int
  {
    $stmt = $this->pdo->prepare('INSERT INTO users (email, active) VALUES (:email, 1)');
    $stmt->bindValue(':email', $email, PDO::PARAM_STR);
    $stmt->execute();
    return (int)$this->pdo->lastInsertId();
  }

  public function deactivate(int $id): void
  {
    $stmt = $this->pdo->prepare('UPDATE users SET active = 0 WHERE id = :id');
    $stmt->bindValue(':id', $id, PDO::PARAM_INT);
    $stmt->execute();
  }
}

Integrating the repository in application code

Construct the repository once per request or per container scope, then call the intent methods. When a unit of work involves many changes, bracket the operations in a transaction for atomic updates.

$repo = new UserRepository($pdo);

try {
  $pdo->beginTransaction();

  $id = $repo->create('new.user@example.com');
  $user = $repo->findById($id);

  if ($user && !$user['active']) {
    // handle inactive user …
  }

  $pdo->commit();
} catch (Throwable $e) {
  if ($pdo->inTransaction()) {
    $pdo->rollBack();
  }
  // translate and log the error here
}