Database Access with PDO

Database Access with PDO

PDO (PHP Data Objects) is PHP's modern database abstraction layer that provides a consistent interface for accessing different database systems including MySQL, PostgreSQL, SQLite, and many others. PDO supports prepared statements which prevent SQL injection attacks — one of the most common and dangerous web vulnerabilities. Understanding PDO is essential for any PHP developer because virtually every web application needs to store and retrieve data from a database.

Connecting and Querying

<?php
// Connect to MySQL
$dsn = "mysql:host=localhost;dbname=myapp;charset=utf8mb4";
$pdo = new PDO($dsn, 'root', 'password', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

// Simple query
$stmt = $pdo->query("SELECT * FROM users LIMIT 10");
$users = $stmt->fetchAll();
foreach ($users as $user) {
    echo "{$user['name']}: {$user['email']}
";
}

// Prepared statement with named parameters (SAFE — prevents SQL injection)
$stmt = $pdo->prepare("SELECT * FROM users WHERE age > :age AND role = :role");
$stmt->execute(['age' => 18, 'role' => 'admin']);
$admins = $stmt->fetchAll();

// Prepared statement with positional parameters
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute(['alice@example.com']);
$user = $stmt->fetch();  // Single row

// NEVER do this — SQL injection vulnerability!
// $pdo->query("SELECT * FROM users WHERE name = '$name'");
?>

CRUD Operations

<?php
// CREATE
$stmt = $pdo->prepare("
    INSERT INTO users (name, email, password_hash, role)
    VALUES (:name, :email, :password, :role)
");
$stmt->execute([
    'name' => 'Alice',
    'email' => 'alice@example.com',
    'password' => password_hash('secret123', PASSWORD_DEFAULT),
    'role' => 'user'
]);
$newId = $pdo->lastInsertId();
echo "Created user with ID: $newId
";

// READ with pagination
function getUsers(PDO $pdo, int $page = 1, int $perPage = 20): array {
    $offset = ($page - 1) * $perPage;
    $stmt = $pdo->prepare("SELECT * FROM users ORDER BY name LIMIT :limit OFFSET :offset");
    $stmt->bindValue('limit', $perPage, PDO::PARAM_INT);
    $stmt->bindValue('offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll();
}

// UPDATE
$stmt = $pdo->prepare("UPDATE users SET name = :name, email = :email WHERE id = :id");
$stmt->execute(['name' => 'Alice Smith', 'email' => 'alice.smith@example.com', 'id' => $newId]);
echo "Updated {$stmt->rowCount()} rows
";

// DELETE
$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute(['id' => $newId]);
?>

Transactions

<?php
// Transactions — all or nothing
function transferMoney(PDO $pdo, int $fromId, int $toId, float $amount): void {
    $pdo->beginTransaction();
    try {
        // Deduct from sender
        $stmt = $pdo->prepare("UPDATE accounts SET balance = balance - :amount WHERE id = :id AND balance >= :amount");
        $stmt->execute(['amount' => $amount, 'id' => $fromId]);
        if ($stmt->rowCount() === 0) {
            throw new RuntimeException("Insufficient funds");
        }

        // Add to receiver
        $stmt = $pdo->prepare("UPDATE accounts SET balance = balance + :amount WHERE id = :id");
        $stmt->execute(['amount' => $amount, 'id' => $toId]);
        if ($stmt->rowCount() === 0) {
            throw new RuntimeException("Recipient not found");
        }

        // Log the transaction
        $stmt = $pdo->prepare("INSERT INTO transactions (from_id, to_id, amount) VALUES (?, ?, ?)");
        $stmt->execute([$fromId, $toId, $amount]);

        $pdo->commit();
        echo "Transfer successful!
";
    } catch (Exception $e) {
        $pdo->rollBack();
        echo "Transfer failed: " . $e->getMessage() . "
";
        throw $e;
    }
}
?>

Database Helper Class

<?php
class Database {
    private static ?PDO $instance = null;

    public static function getConnection(): PDO {
        if (self::$instance === null) {
            $dsn = sprintf("mysql:host=%s;dbname=%s;charset=utf8mb4",
                getenv('DB_HOST') ?: 'localhost',
                getenv('DB_NAME') ?: 'myapp'
            );
            self::$instance = new PDO($dsn,
                getenv('DB_USER') ?: 'root',
                getenv('DB_PASS') ?: '',
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false,
                ]
            );
        }
        return self::$instance;
    }

    public static function query(string $sql, array $params = []): \PDOStatement {
        $stmt = self::getConnection()->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }

    public static function fetchAll(string $sql, array $params = []): array {
        return self::query($sql, $params)->fetchAll();
    }

    public static function fetchOne(string $sql, array $params = []): ?array {
        $result = self::query($sql, $params)->fetch();
        return $result ?: null;
    }
}

// Usage
$users = Database::fetchAll("SELECT * FROM users WHERE role = ?", ['admin']);
$user = Database::fetchOne("SELECT * FROM users WHERE id = ?", [1]);
?>

Pro tip: Always use prepared statements with parameter binding — never concatenate user input into SQL strings. Set PDO::ATTR_EMULATE_PREPARES to false to use real database-level prepared statements, and set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION so database errors throw exceptions instead of silently failing.

Key Takeaways

• PDO provides a consistent interface for MySQL, PostgreSQL, SQLite, and other databases.
• Always use prepared statements with parameter binding to prevent SQL injection attacks.
• Use transactions (beginTransaction, commit, rollBack) for operations that must succeed or fail together.
• Set proper PDO attributes: ERRMODE_EXCEPTION, FETCH_ASSOC, and disable emulated prepares.
• Use password_hash() and password_verify() when storing and checking passwords in the database.