Working with Databases

Most web applications need to store and retrieve data. PHP's PDO (PHP Data Objects) extension provides a consistent interface for working with databases like MySQL, PostgreSQL, and SQLite. PDO supports prepared statements, which protect against SQL injection, and transactions, which ensure data integrity. In this lesson you will learn the core database operations every PHP developer needs.

What You'll Learn

Connecting to a database with PDO
Creating tables and inserting data
Prepared statements and parameter binding
Fetching results in different formats
Updating and deleting records
Transactions for atomic operations
A practical repository pattern

Connecting with PDO

PDO connects to databases using a DSN (Data Source Name) string. Always set the error mode to exceptions so failures are caught immediately:

<?php

// SQLite connection (file-based, no server needed)
try {
    $pdo = new PDO('sqlite::memory:', null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
    echo "Connected to SQLite in-memory database\n";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage() . "\n");
}

// MySQL connection example (commented out):
// $pdo = new PDO(
//     'mysql:host=localhost;dbname=myapp;charset=utf8mb4',
//     'username',
//     'password',
//     [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
// );

// PostgreSQL connection example (commented out):
// $pdo = new PDO(
//     'pgsql:host=localhost;dbname=myapp',
//     'username',
//     'password',
//     [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
// );

// Create a table
$pdo->exec("
    CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        age INTEGER NOT NULL,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )
");
echo "Users table created\n";

// Verify the table exists
$stmt = $pdo->query("SELECT name FROM sqlite_master WHERE type='table'");
$tables = $stmt->fetchAll();
echo "Tables: " . implode(", ", array_column($tables, 'name')) . "\n";

The three key PDO attributes to set are: ERRMODE_EXCEPTION (throw exceptions on errors), FETCH_ASSOC (return rows as associative arrays), and EMULATE_PREPARES = false (use real prepared statements for better security).

Prepared Statements and CRUD

Prepared statements separate SQL structure from data, preventing SQL injection attacks. Use ? for positional parameters or :name for named parameters:

<?php

$pdo = new PDO('sqlite::memory:', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$pdo->exec("
    CREATE TABLE products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price REAL NOT NULL,
        category TEXT NOT NULL,
        in_stock INTEGER DEFAULT 1
    )
");

// INSERT with named parameters
$stmt = $pdo->prepare("
    INSERT INTO products (name, price, category) VALUES (:name, :price, :category)
");

$products = [
    ['name' => 'Laptop', 'price' => 999.99, 'category' => 'Electronics'],
    ['name' => 'Headphones', 'price' => 79.99, 'category' => 'Electronics'],
    ['name' => 'Desk Chair', 'price' => 249.99, 'category' => 'Furniture'],
    ['name' => 'Monitor', 'price' => 399.99, 'category' => 'Electronics'],
    ['name' => 'Bookshelf', 'price' => 149.99, 'category' => 'Furniture'],
];

foreach ($products as $product) {
    $stmt->execute($product);
}
echo "Inserted " . count($products) . " products\n";

// SELECT all
$stmt = $pdo->query("SELECT * FROM products");
$allProducts = $stmt->fetchAll();

echo "\nAll products:\n";
foreach ($allProducts as $row) {
    echo "  #{$row['id']} {$row['name']} - \${$row['price']} ({$row['category']})\n";
}

// SELECT with WHERE using positional parameters
$stmt = $pdo->prepare("SELECT * FROM products WHERE category = ? AND price < ?");
$stmt->execute(['Electronics', 500.00]);
$filtered = $stmt->fetchAll();

echo "\nElectronics under $500:\n";
foreach ($filtered as $row) {
    echo "  {$row['name']} - \${$row['price']}\n";
}

// UPDATE
$stmt = $pdo->prepare("UPDATE products SET price = :price WHERE name = :name");
$stmt->execute(['price' => 899.99, 'name' => 'Laptop']);
echo "\nUpdated laptop price. Rows affected: " . $stmt->rowCount() . "\n";

// DELETE
$stmt = $pdo->prepare("DELETE FROM products WHERE name = ?");
$stmt->execute(['Bookshelf']);
echo "Deleted bookshelf. Rows affected: " . $stmt->rowCount() . "\n";

// Verify final state
$count = $pdo->query("SELECT COUNT(*) as total FROM products")->fetch();
echo "Remaining products: {$count['total']}\n";

Never concatenate user input directly into SQL strings. Always use prepared statements with parameter binding. This is the single most important security practice for database operations.

Fetching Results

PDO offers several methods for retrieving results, each suited to different situations:

<?php

$pdo = new PDO('sqlite::memory:', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$pdo->exec("CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, department TEXT, salary REAL)");
$stmt = $pdo->prepare("INSERT INTO employees VALUES (?, ?, ?, ?)");
$stmt->execute([1, 'Alice', 'Engineering', 95000]);
$stmt->execute([2, 'Bob', 'Marketing', 75000]);
$stmt->execute([3, 'Charlie', 'Engineering', 105000]);
$stmt->execute([4, 'Diana', 'Marketing', 82000]);
$stmt->execute([5, 'Eve', 'Engineering', 110000]);

// fetch() - one row at a time
$stmt = $pdo->query("SELECT * FROM employees WHERE id = 1");
$employee = $stmt->fetch();
echo "Single row: {$employee['name']} - {$employee['department']}\n";

// fetchAll() - all rows at once
$stmt = $pdo->query("SELECT * FROM employees ORDER BY salary DESC");
$all = $stmt->fetchAll();
echo "\nAll employees by salary:\n";
foreach ($all as $row) {
    echo "  {$row['name']}: \${$row['salary']}\n";
}

// fetchColumn() - single column value
$avgSalary = $pdo->query("SELECT AVG(salary) FROM employees")->fetchColumn();
echo "\nAverage salary: $" . number_format((float) $avgSalary, 2) . "\n";

// Fetch as objects
$stmt = $pdo->query("SELECT * FROM employees");
$objects = $stmt->fetchAll(PDO::FETCH_OBJ);
echo "\nAs objects:\n";
foreach ($objects as $obj) {
    echo "  {$obj->name} works in {$obj->department}\n";
}

// Fetch key-value pairs
$stmt = $pdo->query("SELECT name, salary FROM employees");
$salaries = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
echo "\nSalary lookup:\n";
foreach ($salaries as $name => $salary) {
    echo "  {$name}: \${$salary}\n";
}

Use fetch() for single rows, fetchAll() for complete result sets, and fetchColumn() when you need just one value. For large datasets, iterate with fetch() in a while loop to avoid loading everything into memory at once.

Transactions

Transactions group multiple operations into an atomic unit. Either all operations succeed or none of them do:

<?php

$pdo = new PDO('sqlite::memory:', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$pdo->exec("
    CREATE TABLE accounts (
        id INTEGER PRIMARY KEY,
        owner TEXT NOT NULL,
        balance REAL NOT NULL CHECK(balance >= 0)
    )
");

$stmt = $pdo->prepare("INSERT INTO accounts VALUES (?, ?, ?)");
$stmt->execute([1, 'Alice', 1000.00]);
$stmt->execute([2, 'Bob', 500.00]);

function transfer(PDO $pdo, int $fromId, int $toId, float $amount): void {
    $pdo->beginTransaction();

    try {
        // Deduct from sender
        $stmt = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
        $stmt->execute([$amount, $fromId]);

        // Add to receiver
        $stmt = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
        $stmt->execute([$amount, $toId]);

        // Commit if both operations succeed
        $pdo->commit();
        echo "Transferred \${$amount} from account #{$fromId} to #{$toId}\n";
    } catch (PDOException $e) {
        // Rollback on any failure
        $pdo->rollBack();
        echo "Transfer failed: " . $e->getMessage() . "\n";
    }
}

function showBalances(PDO $pdo): void {
    $stmt = $pdo->query("SELECT * FROM accounts");
    foreach ($stmt->fetchAll() as $row) {
        echo "  {$row['owner']}: \${$row['balance']}\n";
    }
}

echo "Initial balances:\n";
showBalances($pdo);

// Successful transfer
transfer($pdo, 1, 2, 200.00);
echo "\nAfter transfer:\n";
showBalances($pdo);

// Failed transfer (insufficient funds due to CHECK constraint)
transfer($pdo, 2, 1, 5000.00);
echo "\nAfter failed transfer:\n";
showBalances($pdo);

Always use transactions when performing multiple related database operations. Without a transaction, a failure midway through could leave your data in an inconsistent state. Call beginTransaction() before the operations, commit() after all succeed, and rollBack() in the catch block.

Key Takeaways

PDO provides a consistent interface across MySQL, PostgreSQL, SQLite, and more
Always set ERRMODE_EXCEPTION to catch database errors properly
Use prepared statements with parameter binding to prevent SQL injection
Choose the right fetch method: fetch() for one row, fetchAll() for all rows, fetchColumn() for a single value
Wrap related operations in transactions with beginTransaction(), commit(), and rollBack()
Never concatenate user input into SQL queries directly
Set EMULATE_PREPARES = false for real prepared statement support

Next Steps

In the next lesson, you will learn about file handling in PHP -- reading and writing files, working with CSV data, and managing directories. Combined with database skills, you will be able to build applications that handle both persistent storage and file-based data.

Pro Tip: Create a reusable database connection class or function that reads credentials from environment variables, sets the proper PDO attributes, and returns a configured PDO instance. Never hardcode credentials in your source files. Use .env files with a library like vlucas/phpdotenv to manage database credentials safely across environments.