PHP Zero to Advanced #9: Databases with PDO - Prepared Statements, CRUD & Transactions

Almost every web application stores data in a database. PHP's PDO (PHP Data Objects) extension gives you one consistent API for MySQL, MariaDB, PostgreSQL, SQLite, and more. In this part you will connect safely using credentials from the environment, learn why prepared statements are non-negotiable, build full CRUD operations, and use transactions to keep your data consistent.

Preparing the Database

We will use MySQL syntax in this article, but the PHP code works the same on PostgreSQL or SQLite with only the DSN changed. Create a database named php_course and run:

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    balance INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE transfers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    from_id INT UNSIGNED NOT NULL,
    to_id INT UNSIGNED NOT NULL,
    amount INT NOT NULL
);

INSERT INTO users (name, email, balance) VALUES
    ('Budi', 'budi@example.com', 100000),
    ('Siti', 'siti@example.com', 50000);

Connecting Securely with PDO

Never hardcode database credentials in your source code. Code gets committed, shared, and copied into screenshots. Instead, read credentials from environment variables set by your server, container, or a secrets manager. For local development, many projects use a .env file loaded by a library such as vlucas/phpdotenv:

# .env (add this file to .gitignore, never commit it)
DB_HOST=127.0.0.1
DB_PORT=3306
DB_NAME=php_course
DB_USER=
DB_PASS=

Put the connection in one file that returns the PDO instance:

<?php
// db.php

declare(strict_types=1);

// TODO: Securely load this value from an environment variable or secrets vault. Do not hardcode.
$host = getenv('DB_HOST') ?: '127.0.0.1';
$port = getenv('DB_PORT') ?: '3306';
$name = getenv('DB_NAME') ?: 'php_course';
$user = getenv('DB_USER');
$pass = getenv('DB_PASS');

if ($user === false || $pass === false) {
    throw new RuntimeException('Database credentials are not configured');
}

$dsn = "mysql:host=$host;port=$port;dbname=$name;charset=utf8mb4";
// PostgreSQL: "pgsql:host=$host;port=5432;dbname=$name"

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,        // throw PDOException on errors
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,   // rows as associative arrays
    PDO::ATTR_EMULATE_PREPARES => false,                // use real prepared statements
]);

return $pdo;

The three options are important:

• ERRMODE_EXCEPTION: errors throw PDOException instead of failing silently.
• FETCH_ASSOC: rows come back as ['name' => 'Budi'] instead of duplicated numeric and named keys.
• EMULATE_PREPARES => false: the database itself handles parameters, and integers come back as integers.

Also, never echo a failed connection's exception message to users; it can reveal the host and username. Log it instead (see Part 8).

SQL Injection and Prepared Statements

SQL injection happens when user input becomes part of the SQL text. Look what happens with a malicious "email":

<?php

$pdo = require __DIR__ . '/db.php';

// NEVER do this: user input concatenated into SQL
$email = "' OR '1'='1";
$sql = "SELECT name FROM users WHERE email = '$email'";
echo $sql . "\n";

$rows = $pdo->query($sql)->fetchAll();
echo "Leaked rows: " . count($rows) . "\n";   // returns ALL users!

Output:

SELECT name FROM users WHERE email = '' OR '1'='1'
Leaked rows: 2

The attacker changed the meaning of the query and got every row. With prepared statements, the SQL and the data travel separately, so input can never change the query structure:

<?php

$pdo = require __DIR__ . '/db.php';

// Named placeholders
$stmt = $pdo->prepare('SELECT id, name, email FROM users WHERE email = :email');
$stmt->execute(['email' => 'siti@example.com']);
$user = $stmt->fetch();
print_r($user);

// The same attack is now just a harmless string value
$stmt->execute(['email' => "' OR '1'='1"]);
var_dump($stmt->fetch());   // false: no user with that literal email

// Positional placeholders
$stmt = $pdo->prepare('SELECT name FROM users WHERE balance >= ? AND name <> ?');
$stmt->execute([60000, 'Siti']);
echo $stmt->fetchColumn() . "\n";

Output:

Array
(
    [id] => 2
    [name] => Siti
    [email] => siti@example.com
)
bool(false)
Budi

Use named placeholders (:email) for readability, or positional ones (?) for short queries. Do not mix both in one statement.

CRUD Operations

Create

Insert with a prepared statement and get the new ID with lastInsertId(). The UNIQUE constraint on email throws a PDOException with SQLSTATE 23000 for duplicates:

<?php

$pdo = require __DIR__ . '/db.php';

function createUser(PDO $pdo, string $name, string $email, int $balance = 0): int
{
    $stmt = $pdo->prepare(
        'INSERT INTO users (name, email, balance) VALUES (:name, :email, :balance)'
    );
    $stmt->execute(['name' => $name, 'email' => $email, 'balance' => $balance]);

    return (int) $pdo->lastInsertId();
}

$id = createUser($pdo, 'Andi', 'andi@example.com', 75000);
echo "Created user #$id\n";

try {
    createUser($pdo, 'Andi Again', 'andi@example.com');   // duplicate email
} catch (PDOException $e) {
    echo "Could not create user (SQLSTATE " . $e->getCode() . ")\n";
}

Output:

Created user #3
Could not create user (SQLSTATE 23000)

Read

PDO offers several fetch styles. Choose the one that matches the shape you need:

<?php

$pdo = require __DIR__ . '/db.php';

// All rows
$users = $pdo->query('SELECT id, name, balance FROM users ORDER BY balance DESC')->fetchAll();
foreach ($users as $u) {
    printf("#%d %-5s %s\n", $u['id'], $u['name'], number_format($u['balance'], 0, ',', '.'));
}

// A single value
$total = $pdo->query('SELECT SUM(balance) FROM users')->fetchColumn();
echo "Total balance: $total\n";

// One column as a flat list, or as id => name pairs
print_r($pdo->query('SELECT name FROM users ORDER BY name')->fetchAll(PDO::FETCH_COLUMN));
print_r($pdo->query('SELECT id, name FROM users')->fetchAll(PDO::FETCH_KEY_PAIR));

Output:

#1 Budi  100.000
#3 Andi  75.000
#2 Siti  50.000
Total balance: 225000
Array
(
    [0] => Andi
    [1] => Budi
    [2] => Siti
)
Array
(
    [1] => Budi
    [2] => Siti
    [3] => Andi
)

You can also hydrate rows directly into objects with FETCH_CLASS, and a statement can be iterated with foreach, which avoids loading huge result sets into memory at once:

<?php

$pdo = require __DIR__ . '/db.php';

final class UserRow
{
    public int $id;
    public string $name;
    public int $balance;

    public function isRich(): bool
    {
        return $this->balance >= 75000;
    }
}

$stmt = $pdo->query('SELECT id, name, balance FROM users');
$stmt->setFetchMode(PDO::FETCH_CLASS, UserRow::class);

foreach ($stmt as $user) {
    echo $user->name . ($user->isRich() ? ' (premium)' : '') . "\n";
}

Output:

Budi (premium)
Siti
Andi (premium)

Update and Delete

rowCount() tells you how many rows were affected, which is useful for detecting "not found":

<?php

$pdo = require __DIR__ . '/db.php';

$stmt = $pdo->prepare('UPDATE users SET balance = balance + :amount WHERE id = :id');
$stmt->execute(['amount' => 25000, 'id' => 2]);
echo "Updated rows: " . $stmt->rowCount() . "\n";

$stmt = $pdo->prepare('DELETE FROM users WHERE id = :id');
$stmt->execute(['id' => 3]);
echo "Deleted rows: " . $stmt->rowCount() . "\n";

$stmt->execute(['id' => 999]);
echo "Deleted rows: " . $stmt->rowCount() . "\n";   // 0: nothing matched

print_r($pdo->query('SELECT name, balance FROM users')->fetchAll(PDO::FETCH_KEY_PAIR));

Output:

Updated rows: 1
Deleted rows: 1
Deleted rows: 0
Array
(
    [Budi] => 100000
    [Siti] => 75000
)

Dynamic IN lists and sorting

Placeholders replace values only. For an IN (...) list, generate one placeholder per item. For column names or sort direction, use a strict allow-list:

<?php

$pdo = require __DIR__ . '/db.php';

$ids = [1, 3];
$placeholders = implode(', ', array_fill(0, count($ids), '?'));   // "?, ?"

$stmt = $pdo->prepare("SELECT name FROM users WHERE id IN ($placeholders)");
$stmt->execute($ids);
echo implode(', ', $stmt->fetchAll(PDO::FETCH_COLUMN)) . "\n";

// Column names cannot be placeholders: use an allow-list
$sortInput = 'balance';   // e.g. from $_GET['sort']
$sort = in_array($sortInput, ['name', 'balance'], true) ? $sortInput : 'name';
echo implode(', ', $pdo->query("SELECT name FROM users ORDER BY $sort")->fetchAll(PDO::FETCH_COLUMN)) . "\n";

Output:

Budi, Andi
Siti, Andi, Budi

Transactions

A money transfer involves several statements: debit one account, credit another, write a log. If the server crashes halfway, you must not lose money. A transaction makes the statements all-or-nothing: commit() saves them together, rollBack() undoes them all.

<?php

$pdo = require __DIR__ . '/db.php';

function transfer(PDO $pdo, int $fromId, int $toId, int $amount): void
{
    $pdo->beginTransaction();
    try {
        $debit = $pdo->prepare(
            'UPDATE users SET balance = balance - :amount WHERE id = :id AND balance >= :min'
        );
        // with native prepares, each named placeholder may appear only once
        $debit->execute(['amount' => $amount, 'id' => $fromId, 'min' => $amount]);
        if ($debit->rowCount() !== 1) {
            throw new RuntimeException('Insufficient balance');
        }

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

        $pdo->prepare('INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)')
            ->execute([$fromId, $toId, $amount]);

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();   // undo every statement since beginTransaction()
        throw $e;
    }
}

$balances = fn() => $pdo->query('SELECT name, balance FROM users')->fetchAll(PDO::FETCH_KEY_PAIR);

transfer($pdo, 1, 2, 30000);
print_r($balances());

try {
    transfer($pdo, 2, 1, 999999);
} catch (RuntimeException $e) {
    echo "Transfer failed: " . $e->getMessage() . "\n";
}
print_r($balances());
echo "Transfers recorded: " . $pdo->query('SELECT COUNT(*) FROM transfers')->fetchColumn() . "\n";

Output:

Array
(
    [Budi] => 70000
    [Siti] => 80000
)
Transfer failed: Insufficient balance
Array
(
    [Budi] => 70000
    [Siti] => 80000
)
Transfers recorded: 1

The failed transfer left both balances untouched, and only one transfer was recorded. Note the balance >= :min condition in the UPDATE: checking and updating in a single statement avoids race conditions where two requests read the same balance at the same time.

Common Mistakes

• Concatenating or interpolating input into SQL. Even "trusted" values like IDs from the URL. Always use placeholders.
• Hardcoding credentials or committing .env. Load them with getenv() or a secrets vault and rotate any credential that leaks.
• Using placeholders for table or column names. They only work for values. Use an allow-list.
• Forgetting rollBack() in the error path, leaving the connection in an open transaction.
• Connecting as the database root user. Create an application user with only the privileges it needs.
• Forgetting charset=utf8mb4 in the MySQL DSN, which breaks emoji and some non-Latin characters.

Exercise

Wrap your queries in a small UserRepository class with find(), search(), and rename() methods. All queries must use placeholders, including the LIKE search. This example assumes a third user, Andi, has been inserted.

<?php

declare(strict_types=1);

$pdo = require __DIR__ . '/db.php';

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

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

    public function search(string $term): array
    {
        $stmt = $this->pdo->prepare('SELECT name FROM users WHERE name LIKE ? ORDER BY name');
        $stmt->execute(['%' . $term . '%']);
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    public function rename(int $id, string $name): bool
    {
        $stmt = $this->pdo->prepare('UPDATE users SET name = ? WHERE id = ?');
        $stmt->execute([$name, $id]);
        return $stmt->rowCount() === 1;
    }
}

$repo = new UserRepository($pdo);
print_r($repo->find(2));
var_dump($repo->find(42));
print_r($repo->search('i'));
var_dump($repo->rename(3, 'Andi Wijaya'));
echo $repo->find(3)['name'] . "\n";

Output:

Array
(
    [id] => 2
    [name] => Siti
    [email] => siti@example.com
)
NULL
Array
(
    [0] => Andi
    [1] => Budi
    [2] => Siti
)
bool(true)
Andi Wijaya

Add a delete() method and a paginate(int $page, int $perPage) method that uses LIMIT and OFFSET with bound integer parameters.

Summary

• PDO provides one API for many databases; configure it with exceptions, associative fetch, and real prepared statements.
• Load credentials from environment variables or a secrets manager, never from source code.
• Prepared statements prevent SQL injection; allow-lists protect dynamic identifiers.
• Use fetch, fetchAll, fetchColumn, and rowCount for CRUD.
• Wrap multi-step changes in transactions with commit and rollBack.

In the final Part 10 we will combine everything into a tiny REST API with sessions, secure password hashing, CSRF protection, PHPUnit tests, and a tour of the Laravel ecosystem.