PHP From ZeroModern PHP course

databases storage and caching

PDO

PDO, short for PHP Data Objects, is PHP's common database access layer. It lets PHP connect to databases such as SQLite, MySQL, MariaDB, and PostgreSQL through a consistent API.

PDO does not remove the need to understand SQL. It gives you a safer and more predictable way to connect, prepare statements, bind values, fetch rows, handle errors, and use transactions.

Creating A Connection

A PDO connection needs a DSN, a username, a password, and options. SQLite can run without a separate server, so it is ideal for examples.

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);

echo 'Connected' . PHP_EOL;

// Prints:
// Connected

For MySQL or PostgreSQL, the DSN changes, but the PDO methods are similar.

PHP example
<?php

declare(strict_types=1);

$mysqlDsn = 'mysql:host=127.0.0.1;dbname=app;charset=utf8mb4';
$postgresDsn = 'pgsql:host=127.0.0.1;port=5432;dbname=app';

echo $mysqlDsn . PHP_EOL;
echo $postgresDsn . PHP_EOL;

// Prints:
// mysql:host=127.0.0.1;dbname=app;charset=utf8mb4
// pgsql:host=127.0.0.1;port=5432;dbname=app

Prepared Statements

Prepared statements separate the SQL command from the values. This is the normal way to protect against SQL injection.

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 UNIQUE)');

$insert = $pdo->prepare('INSERT INTO users (email) VALUES (:email)');
$insert->execute(['email' => 'sam@example.com']);

$select = $pdo->prepare('SELECT id, email FROM users WHERE email = :email');
$select->execute(['email' => 'sam@example.com']);

print_r($select->fetch());

// Prints:
// [id] => 1
// [email] => sam@example.com

Do not build SQL like "WHERE email = '$email'". The value may contain quotes, SQL syntax, or unexpected characters. Let PDO bind the value.

Fetching Rows

Common fetch methods include:

  • fetch() gets one row.
  • fetchAll() gets all remaining rows.
  • fetchColumn() gets one column from the next row.
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 tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL)');
$pdo->exec("INSERT INTO tasks (title, done) VALUES ('Write code', 1), ('Review code', 0)");

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

echo 'tasks=' . $count . PHP_EOL;

// Prints:
// tasks=2

Be careful with fetchAll() on large result sets. It loads the entire result into memory.

Transactions

A transaction groups database changes so they succeed or fail together.

PHP example
<?php

declare(strict_types=1);

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

$pdo->exec('CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL)');
$pdo->exec('INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 500)');

$pdo->beginTransaction();
$pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE id = :id')->execute(['amount' => 200, 'id' => 1]);
$pdo->prepare('UPDATE accounts SET balance = balance + :amount WHERE id = :id')->execute(['amount' => 200, 'id' => 2]);
$pdo->commit();

foreach ($pdo->query('SELECT id, balance FROM accounts ORDER BY id') as $row) {
    echo $row['id'] . ':' . $row['balance'] . PHP_EOL;
}

// Prints:
// 1:800
// 2:700

In real code, wrap the transaction in try/catch and roll back when an exception happens.

Error Mode

Set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION. Without exceptions, database errors can be easy to miss.

Also avoid displaying raw database errors to users. They may reveal table names, SQL fragments, or infrastructure details. Log safe diagnostic information and return a suitable application error.

What To Check

Before moving on, make sure you can:

  • Create a PDO connection.
  • Set exception mode and default fetch mode.
  • Use prepared statements with named placeholders.
  • Fetch one row, many rows, and one column.
  • Use a transaction for related writes.
  • Explain why PDO does not replace understanding SQL.

Practice

Practice: Use PDO Safely

Write a self-contained PHP script that uses PDO with SQLite in memory.

Requirements

  • Create a PDO connection.
  • Enable exceptions and associative fetch mode.
  • Create a table.
  • Insert rows with a prepared statement.
  • Select one row with a prepared statement.
  • Use fetch() or fetchColumn() deliberately.
  • Include a transaction for two related updates.
Show solution

This solution uses SQLite so the full PDO example can run without a separate database service.

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 UNIQUE, credits INTEGER NOT NULL)');

$insert = $pdo->prepare('INSERT INTO users (email, credits) VALUES (:email, :credits)');
$insert->execute(['email' => 'sam@example.com', 'credits' => 10]);
$insert->execute(['email' => 'lee@example.com', 'credits' => 5]);

$select = $pdo->prepare('SELECT id, email, credits FROM users WHERE email = :email');
$select->execute(['email' => 'sam@example.com']);

$user = $select->fetch();
echo $user['email'] . ' has ' . $user['credits'] . ' credits' . PHP_EOL;

$pdo->beginTransaction();
$pdo->prepare('UPDATE users SET credits = credits - :amount WHERE email = :email')
    ->execute(['amount' => 2, 'email' => 'sam@example.com']);
$pdo->prepare('UPDATE users SET credits = credits + :amount WHERE email = :email')
    ->execute(['amount' => 2, 'email' => 'lee@example.com']);
$pdo->commit();

$totalCredits = $pdo->query('SELECT SUM(credits) FROM users')->fetchColumn();
echo 'total=' . $totalCredits . PHP_EOL;

// Prints:
// sam@example.com has 10 credits
// total=15

The inserts and selects use bound parameters. The two updates are in one transaction because a transfer should not half-complete.