PHP Fundamentals

PDO: Alternative Database Access

13 min Lesson 40 of 45

What is PDO?

PDO (PHP Data Objects) is a database abstraction layer providing a uniform interface for accessing different database systems. Unlike MySQLi, which only works with MySQL, PDO supports 12+ database drivers including MySQL, PostgreSQL, SQLite, Oracle, and Microsoft SQL Server.

PDO vs MySQLi: Quick Comparison

Feature	MySQLi	PDO
Database Support	MySQL only	12+ databases
API Style	Procedural + OOP	OOP only
Parameter Binding	? placeholders only	? and :named
Fetching Results	Multiple methods	Flexible fetch modes
Learning Curve	Easier for beginners	More features, slightly steeper

When to Use PDO

Database flexibility: You might switch databases in the future
Named parameters: You prefer :name over ? placeholders
Object mapping: You want to fetch results directly into custom classes
Transactions: You need advanced transaction handling
Portability: Your code might run on different database systems

Connecting to Database with PDO

<?php
$host = 'localhost';
$dbname = 'my_database';
$username = 'root';
$password = '';

try {
    // Create PDO instance
    $pdo = new PDO(
        "mysql:host=$host;dbname=$dbname;charset=utf8mb4",
        $username,
        $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false
        ]
    );

    echo "Connected successfully";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

PDO Connection Options Explained

PDO::ATTR_ERRMODE - Set to ERRMODE_EXCEPTION to throw exceptions on errors
PDO::ATTR_DEFAULT_FETCH_MODE - Set default fetch mode (associative array recommended)
PDO::ATTR_EMULATE_PREPARES - Disable emulated prepared statements for true prepared statements

PDO Configuration Class

<?php
class Database {
    private $host = 'localhost';
    private $dbname = 'my_database';
    private $username = 'root';
    private $password = '';
    private $pdo;

    public function __construct() {
        $dsn = "mysql:host={$this->host};dbname={$this->dbname};charset=utf8mb4";

        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_PERSISTENT => false // Set to true for persistent connections
        ];

        try {
            $this->pdo = new PDO($dsn, $this->username, $this->password, $options);
        } catch (PDOException $e) {
            throw new PDOException($e->getMessage(), (int)$e->getCode());
        }
    }

    public function getConnection() {
        return $this->pdo;
    }
}

// Usage
$db = new Database();
$pdo = $db->getConnection();
?>

Prepared Statements with Question Mark Placeholders

<?php
// Prepare statement
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND email = ?");

// Execute with array of parameters
$stmt->execute(['johndoe', 'john@example.com']);

// Fetch results
$user = $stmt->fetch();

if ($user) {
    echo "User found: " . $user['username'];
} else {
    echo "User not found";
}
?>

Named Parameters (PDO Exclusive Feature)

Named parameters make your code more readable and maintainable:

<?php
// Prepare statement with named parameters
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND email = :email");

// Execute with associative array
$stmt->execute([
    ':username' => 'johndoe',
    ':email' => 'john@example.com'
]);

$user = $stmt->fetch();
?>

Tip: Named parameters are especially useful when you have many parameters or when the same parameter appears multiple times in the query.

INSERT with PDO

<?php
// With named parameters
$stmt = $pdo->prepare("
    INSERT INTO users (username, email, password, full_name)
    VALUES (:username, :email, :password, :full_name)
");

$stmt->execute([
    ':username' => 'johndoe',
    ':email' => 'john@example.com',
    ':password' => password_hash('password123', PASSWORD_DEFAULT),
    ':full_name' => 'John Doe'
]);

// Get last insert ID
$user_id = $pdo->lastInsertId();
echo "New user ID: $user_id";
?>

UPDATE with PDO

<?php
$stmt = $pdo->prepare("
    UPDATE users
    SET email = :email, full_name = :full_name
    WHERE id = :id
");

$stmt->execute([
    ':email' => 'newemail@example.com',
    ':full_name' => 'John Smith',
    ':id' => 42
]);

// Get number of affected rows
$affected = $stmt->rowCount();
echo "Rows updated: $affected";
?>

DELETE with PDO

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

echo "Rows deleted: " . $stmt->rowCount();
?>

Fetch Modes in PDO

PDO offers multiple ways to fetch results:

<?php
$stmt = $pdo->query("SELECT * FROM users");

// 1. Fetch as associative array (default if set in constructor)
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// Usage: $row['username']

// 2. Fetch as numeric array
$row = $stmt->fetch(PDO::FETCH_NUM);
// Usage: $row[0], $row[1]

// 3. Fetch as both associative and numeric
$row = $stmt->fetch(PDO::FETCH_BOTH);
// Usage: $row['username'] or $row[0]

// 4. Fetch as object (stdClass)
$row = $stmt->fetch(PDO::FETCH_OBJ);
// Usage: $row->username

// 5. Fetch all rows at once
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 6. Fetch single column
$stmt = $pdo->query("SELECT username FROM users");
$usernames = $stmt->fetchAll(PDO::FETCH_COLUMN);
// Returns: ['user1', 'user2', 'user3']

// 7. Fetch key-value pairs
$stmt = $pdo->query("SELECT id, username FROM users");
$users = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
// Returns: [1 => 'user1', 2 => 'user2']
?>

Fetching Into Custom Classes

PDO can map database rows directly to your custom classes:

<?php
class User {
    public $id;
    public $username;
    public $email;
    public $full_name;

    public function greet() {
        return "Hello, my name is {$this->full_name}";
    }
}

// Fetch into custom class
$stmt = $pdo->query("SELECT * FROM users WHERE id = 1");
$user = $stmt->fetchObject('User');

echo $user->greet(); // "Hello, my name is John Doe"

// Fetch all into custom class
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_CLASS, 'User');

foreach ($users as $user) {
    echo $user->greet() . "<br>";
}
?>

Transactions in PDO

Transactions ensure a series of database operations succeed or fail together:

<?php
try {
    // Begin transaction
    $pdo->beginTransaction();

    // Multiple operations
    $stmt1 = $pdo->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
    $stmt1->execute(['user1', 'user1@example.com']);

    $stmt2 = $pdo->prepare("INSERT INTO profiles (user_id, bio) VALUES (?, ?)");
    $stmt2->execute([$pdo->lastInsertId(), 'My bio']);

    $stmt3 = $pdo->prepare("UPDATE stats SET user_count = user_count + 1");
    $stmt3->execute();

    // Commit if all succeeded
    $pdo->commit();
    echo "All operations completed successfully";

} catch (Exception $e) {
    // Rollback if any failed
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}
?>

Note: Transactions only work with storage engines that support them (e.g., InnoDB in MySQL, not MyISAM).

Complete CRUD Class with PDO

<?php
class UserRepository {
    private $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    // CREATE
    public function create($data) {
        $stmt = $this->pdo->prepare("
            INSERT INTO users (username, email, password, full_name)
            VALUES (:username, :email, :password, :full_name)
        ");

        $stmt->execute([
            ':username' => $data['username'],
            ':email' => $data['email'],
            ':password' => password_hash($data['password'], PASSWORD_DEFAULT),
            ':full_name' => $data['full_name']
        ]);

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

    // READ - Get one
    public function findById($id) {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE id = :id");
        $stmt->execute([':id' => $id]);
        return $stmt->fetch();
    }

    // READ - Get all
    public function findAll() {
        $stmt = $this->pdo->query("SELECT * FROM users ORDER BY created_at DESC");
        return $stmt->fetchAll();
    }

    // READ - Search
    public function search($term) {
        $stmt = $this->pdo->prepare("
            SELECT * FROM users
            WHERE username LIKE :term OR email LIKE :term OR full_name LIKE :term
        ");
        $stmt->execute([':term' => "%$term%"]);
        return $stmt->fetchAll();
    }

    // UPDATE
    public function update($id, $data) {
        $stmt = $this->pdo->prepare("
            UPDATE users
            SET username = :username, email = :email, full_name = :full_name
            WHERE id = :id
        ");

        return $stmt->execute([
            ':username' => $data['username'],
            ':email' => $data['email'],
            ':full_name' => $data['full_name'],
            ':id' => $id
        ]);
    }

    // DELETE
    public function delete($id) {
        $stmt = $this->pdo->prepare("DELETE FROM users WHERE id = :id");
        return $stmt->execute([':id' => $id]);
    }

    // CHECK EXISTS
    public function exists($username, $email) {
        $stmt = $this->pdo->prepare("
            SELECT COUNT(*) FROM users
            WHERE username = :username OR email = :email
        ");
        $stmt->execute([':username' => $username, ':email' => $email]);
        return $stmt->fetchColumn() > 0;
    }

    // AUTHENTICATE
    public function authenticate($username, $password) {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE username = :username");
        $stmt->execute([':username' => $username]);
        $user = $stmt->fetch();

        if ($user && password_verify($password, $user['password'])) {
            unset($user['password']); // Remove password from result
            return $user;
        }

        return false;
    }
}

// Usage
$db = new Database();
$pdo = $db->getConnection();
$userRepo = new UserRepository($pdo);

// Create user
$user_id = $userRepo->create([
    'username' => 'johndoe',
    'email' => 'john@example.com',
    'password' => 'password123',
    'full_name' => 'John Doe'
]);

// Get user
$user = $userRepo->findById($user_id);

// Search users
$results = $userRepo->search('john');

// Update user
$userRepo->update($user_id, [
    'username' => 'johndoe',
    'email' => 'newemail@example.com',
    'full_name' => 'John Smith'
]);

// Authenticate
$user = $userRepo->authenticate('johndoe', 'password123');
if ($user) {
    $_SESSION['user'] = $user;
}

// Delete user
$userRepo->delete($user_id);
?>

Error Handling in PDO

<?php
try {
    $stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
    $stmt->execute(['johndoe', 'john@example.com']);
} catch (PDOException $e) {
    // Check for specific errors
    if ($e->getCode() == 23000) {
        echo "Duplicate entry - username or email already exists";
    } else {
        echo "Database error: " . $e->getMessage();
    }
}
?>

Warning: Never display raw error messages to users in production. Log errors and show generic messages instead.

MySQLi vs PDO: Which Should You Use?

Choose MySQLi if:

You're only using MySQL and won't change databases
You prefer procedural programming style
You need slightly better performance for MySQL-specific operations

Choose PDO if:

You want database portability
You prefer object-oriented programming
You want named parameters for better readability
You need to fetch directly into custom classes
You're working on a larger application with complex requirements

Exercise: Convert MySQLi Code to PDO

Take the Task Manager application from the previous lesson
Convert all MySQLi code to PDO
Use named parameters instead of question marks
Create a TaskRepository class with all CRUD methods
Implement transactions for operations that involve multiple tables
Add a custom Task class and use fetchObject()
Test all functionality to ensure it works identically
Compare the code readability between MySQLi and PDO versions

Best Practices for PDO

Always use prepared statements: Never concatenate user input into queries
Set error mode to exceptions: Enable PDO::ERRMODE_EXCEPTION
Disable emulated prepares: Set PDO::ATTR_EMULATE_PREPARES to false
Use named parameters: They're more readable than question marks
Set default fetch mode: Use PDO::FETCH_ASSOC as default
Use transactions: For operations that must succeed or fail together
Don't use persistent connections: Unless you have a specific need
Handle errors gracefully: Catch exceptions and log errors

Summary

PDO is a database abstraction layer supporting multiple database systems
Unlike MySQLi, PDO works with MySQL, PostgreSQL, SQLite, and more
PDO supports both question mark (?) and named (:name) parameters
PDO offers flexible fetch modes including fetching into custom classes
Transactions in PDO ensure atomic operations (all succeed or all fail)
Both MySQLi and PDO are secure when used with prepared statements
Choose based on your needs: MySQLi for MySQL-only, PDO for flexibility
Always use prepared statements regardless of which extension you choose