🗄️ Lesson 19: PDO — Connecting to MySQL & Prepared Statements

Why PDO?

PHP has two ways to talk to MySQL: the older mysqli extension and the newer PDO. While both work, PDO is the modern standard for several important reasons.

Connecting to MySQL

A PDO connection requires three things: a DSN (Data Source Name) that tells PDO which database to connect to, a username, and a password.

The DSN (Data Source Name)

// DSN format for MySQL:
// "mysql:host=HOSTNAME;dbname=DATABASE;charset=utf8mb4"

// Common DSN examples:
"mysql:host=localhost;dbname=myapp;charset=utf8mb4"     // Local MySQL
"mysql:host=127.0.0.1;dbname=myapp;charset=utf8mb4"    // Local via IP
"mysql:host=localhost;port=3307;dbname=myapp;charset=utf8mb4" // Custom port
"mysql:host=db.example.com;dbname=myapp;charset=utf8mb4"     // Remote server

Basic Connection

<?php
// Database credentials
$host = "localhost";
$dbname = "php_course";  // Your database from MySQL Foundations
$user = "root";
$pass = "";              // Your MySQL password

// Build the DSN
$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

// Create the connection
try {
    $pdo = new PDO($dsn, $user, $pass);
    echo "Connected successfully!";
} catch (PDOException $e) {
    // Connection failed — handle the error
    die("Connection failed: " . $e->getMessage());
}

// The $pdo object is your database connection
// Use it for all queries in this script

Credentials from Environment Variables

<?php
// Option 1: Environment variables (best practice)
$host   = $_ENV["DB_HOST"]   ?? "localhost";
$dbname = $_ENV["DB_NAME"]   ?? "php_course";
$user   = $_ENV["DB_USER"]   ?? "root";
$pass   = $_ENV["DB_PASS"]   ?? "";

// Option 2: Config file (excluded from git via .gitignore)
// $config = require "config/database.php";
// $host = $config["host"];

$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

try {
    $pdo = new PDO($dsn, $user, $pass);
} catch (PDOException $e) {
    error_log("DB connection failed: " . $e->getMessage());
    die("Database unavailable. Please try again later.");
}

Connection Lifecycle

<?php
// Connection is created when you call new PDO(...)
$pdo = new PDO($dsn, $user, $pass);

// Connection stays open for the lifetime of the $pdo variable
// ... run queries here ...

// Connection closes automatically when:
// 1. The script ends
// 2. $pdo goes out of scope
// 3. You explicitly set it to null:
$pdo = null; // Closes the connection

PDO Configuration Options

A bare new PDO() call works, but the defaults aren't ideal. You should always configure PDO for safety and convenience.

Recommended Options

<?php
$dsn = "mysql:host=localhost;dbname=php_course;charset=utf8mb4";

$options = [
    // Throw exceptions on errors (instead of silent failures)
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,

    // Return associative arrays by default (instead of both numeric + assoc)
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

    // Use real prepared statements (not emulated)
    PDO::ATTR_EMULATE_PREPARES   => false,

    // Return column names in lowercase (consistent across databases)
    PDO::ATTR_CASE               => PDO::CASE_NATURAL,
];

$pdo = new PDO($dsn, "root", "", $options);

What Each Option Does

⚠️ Important: The most critical option is ERRMODE_EXCEPTION. Without it, PDO fails silently — queries return false instead of throwing errors, and you won't know something went wrong until you see missing data or blank pages.

Error Modes Compared

<?php
// SILENT (default — dangerous!) — errors return false, no feedback
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$result = $pdo->query("SELECT * FROM nonexistent_table");
// $result is false — but you get no error message

// WARNING — PHP warning is generated
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
// Generates: Warning: PDO::query(): SQLSTATE[42S02]: ...

// EXCEPTION (recommended!) — throws PDOException
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
    $result = $pdo->query("SELECT * FROM nonexistent_table");
} catch (PDOException $e) {
    echo "Query failed: " . $e->getMessage();
    // "SQLSTATE[42S02]: Base table or view not found: ..."
}

Running Simple Queries

For queries that don't include any user input, you can use $pdo->query() directly. This runs the SQL and returns a result set.

SELECT with query()

<?php
// Simple SELECT — no user input, so query() is safe
$stmt = $pdo->query("SELECT id, name, email FROM users ORDER BY name");

// Fetch all rows as an array of associative arrays
$users = $stmt->fetchAll();

foreach ($users as $user) {
    echo "{$user['name']} — {$user['email']}\n";
}
// Alice — alice@example.com
// Bob — bob@example.com
// Charlie — charlie@example.com

// Get the number of rows returned
echo "Found: " . count($users) . " users";

exec() for Non-SELECT Queries

<?php
// exec() runs INSERT, UPDATE, DELETE, CREATE, etc.
// Returns the number of affected rows

// Create a table
$pdo->exec("CREATE TABLE IF NOT EXISTS logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255) NOT NULL,
    level VARCHAR(20) DEFAULT 'INFO',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)");

// Insert a row
$affected = $pdo->exec("INSERT INTO logs (message, level) VALUES ('App started', 'INFO')");
echo "$affected row(s) inserted";

// Delete old logs
$deleted = $pdo->exec("DELETE FROM logs WHERE created_at < '2026-01-01'");
echo "$deleted row(s) deleted";

<?php
// ❌ NEVER DO THIS — SQL injection vulnerability!
$name = $_GET["name"]; // User input
$stmt = $pdo->query("SELECT * FROM users WHERE name = '$name'");
// If $name is: ' OR '1'='1  → returns ALL users
// If $name is: '; DROP TABLE users; --  → deletes the table!

// ✅ ALWAYS use prepared statements for user input
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?");
$stmt->execute([$name]);
// Safe — the input is treated as data, never as SQL code

Prepared Statements

Prepared statements are the cornerstone of secure database access. They separate the SQL structure from the data, making SQL injection impossible. The database knows what's code and what's data — even if the data looks like SQL.

Positional Placeholders (?)

<?php
// Step 1: Prepare — write SQL with ? placeholders
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");

// Step 2: Execute — pass values in an array (order must match ?)
$stmt->execute([42]);

// Step 3: Fetch the results
$user = $stmt->fetch(); // Single row
echo $user["name"];

// Multiple placeholders — values are bound by position
$stmt = $pdo->prepare(
    "SELECT * FROM products WHERE price > ? AND category = ? ORDER BY price LIMIT ?"
);
$stmt->execute([10.00, "Electronics", 20]);
$products = $stmt->fetchAll();

Named Placeholders (:name)

<?php
// Named placeholders are more readable — especially with many parameters
$stmt = $pdo->prepare(
    "SELECT * FROM products WHERE price > :min_price AND category = :category ORDER BY price LIMIT :limit"
);

// Pass values as an associative array — order doesn't matter
$stmt->execute([
    ":min_price" => 10.00,
    ":category"  => "Electronics",
    ":limit"     => 20,
]);

$products = $stmt->fetchAll();

// You can also omit the colon in the array keys:
$stmt->execute([
    "min_price" => 10.00,
    "category"  => "Electronics",
    "limit"     => 20,
]);

Re-executing Prepared Statements

A prepared statement can be executed multiple times with different values — the query is compiled once and reused, which is more efficient than running separate queries.

<?php
// Prepare once, execute many times — efficient for batch operations
$stmt = $pdo->prepare(
    "INSERT INTO users (name, email) VALUES (:name, :email)"
);

$newUsers = [
    ["name" => "Alice",   "email" => "alice@example.com"],
    ["name" => "Bob",     "email" => "bob@example.com"],
    ["name" => "Charlie", "email" => "charlie@example.com"],
];

foreach ($newUsers as $user) {
    $stmt->execute($user); // Same prepared statement, different data
}

echo count($newUsers) . " users inserted.";

Binding Parameters

Passing an array to execute() is the simplest approach, but PDO also offers explicit binding methods for more control over data types.

execute() with Array (Simplest)

<?php
// All values are sent as strings by default
$stmt = $pdo->prepare("SELECT * FROM users WHERE age > ? AND active = ?");
$stmt->execute([18, 1]);
// Works fine — MySQL converts strings to the right types automatically

bindValue() — Bind a Value

<?php
$stmt = $pdo->prepare("SELECT * FROM users WHERE age > :age AND active = :active");

// bindValue binds a COPY of the value at the time of binding
$stmt->bindValue(":age", 18, PDO::PARAM_INT);
$stmt->bindValue(":active", true, PDO::PARAM_BOOL);
$stmt->execute();

$users = $stmt->fetchAll();

// Positional example:
$stmt = $pdo->prepare("SELECT * FROM products WHERE price < ? LIMIT ?");
$stmt->bindValue(1, 50.00);    // First ? (1-indexed)
$stmt->bindValue(2, 10, PDO::PARAM_INT);  // Second ?
$stmt->execute();

bindParam() — Bind a Reference

<?php
// bindParam binds by REFERENCE — the value is read at execute() time
$stmt = $pdo->prepare("INSERT INTO logs (message, level) VALUES (:message, :level)");

$message = "";
$level = "";
$stmt->bindParam(":message", $message);
$stmt->bindParam(":level", $level);

// Change the variables and re-execute
$message = "User logged in";
$level = "INFO";
$stmt->execute(); // Uses current values of $message and $level

$message = "Invalid password attempt";
$level = "WARNING";
$stmt->execute(); // Uses the new values — no need to rebind

PDO Parameter Types

Fetching Results

After executing a SELECT query, you need to retrieve the results. PDO offers several fetch methods and modes to get data in the format you need.

fetch() — One Row

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

// Fetch a single row (returns false if no row found)
$user = $stmt->fetch();

if ($user) {
    echo "Name: {$user['name']}\n";
    echo "Email: {$user['email']}\n";
} else {
    echo "User not found.";
}

// Fetch rows one at a time in a loop (memory-efficient for large results)
$stmt = $pdo->query("SELECT * FROM products ORDER BY name");
while ($product = $stmt->fetch()) {
    echo "{$product['name']}: \${$product['price']}\n";
}

fetchAll() — All Rows

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

// Fetch all rows into an array at once
$users = $stmt->fetchAll();

echo count($users) . " users found.\n";

foreach ($users as $user) {
    echo "{$user['name']} — {$user['email']}\n";
}

fetchColumn() — Single Value

<?php
// Get a single value — great for COUNT, MAX, SUM, etc.
$stmt = $pdo->query("SELECT COUNT(*) FROM users");
$totalUsers = $stmt->fetchColumn();
echo "Total users: $totalUsers";

// Get a specific column by index (0-based)
$stmt = $pdo->query("SELECT id, name, email FROM users");
while ($name = $stmt->fetchColumn(1)) { // Column index 1 = name
    echo "$name\n";
}

Fetch Modes

<?php
$stmt = $pdo->query("SELECT id, name, email FROM users");

// FETCH_ASSOC — associative array (our default)
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// ["id" => 1, "name" => "Alice", "email" => "alice@example.com"]

// FETCH_NUM — numeric array
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_NUM);
// [0 => 1, 1 => "Alice", 2 => "alice@example.com"]

// FETCH_OBJ — anonymous object
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_OBJ);
// echo $row->name;  → "Alice"
// echo $row->email; → "alice@example.com"

// FETCH_CLASS — map rows to a specific class
class User {
    public int $id;
    public string $name;
    public string $email;

    public function getDisplayName(): string {
        return "{$this->name} <{$this->email}>";
    }
}

$stmt = $pdo->query("SELECT id, name, email FROM users");
$stmt->setFetchMode(PDO::FETCH_CLASS, User::class);
$users = $stmt->fetchAll();

foreach ($users as $user) {
    echo $user->getDisplayName() . "\n";
    // "Alice "
}

// FETCH_KEY_PAIR — two columns as key => value
$stmt = $pdo->query("SELECT id, name FROM users");
$lookup = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
// [1 => "Alice", 2 => "Bob", 3 => "Charlie"]

// FETCH_UNIQUE — full rows keyed by first column
$stmt = $pdo->query("SELECT id, name, email FROM users");
$usersById = $stmt->fetchAll(PDO::FETCH_UNIQUE);
// [1 => ["name" => "Alice", "email" => "..."], 2 => ["name" => "Bob", ...]]

// FETCH_GROUP — group rows by first column
$stmt = $pdo->query("SELECT role, name, email FROM users ORDER BY role, name");
$byRole = $stmt->fetchAll(PDO::FETCH_GROUP);
// ["admin" => [["name" => "Alice", ...]], "user" => [["name" => "Bob", ...], ...]]

// FETCH_COLUMN — single column as flat array
$stmt = $pdo->query("SELECT email FROM users");
$emails = $stmt->fetchAll(PDO::FETCH_COLUMN);
// ["alice@example.com", "bob@example.com", "charlie@example.com"]

Checking for Empty Results

<?php
// For fetch() — returns false when no rows found
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([999]);
$user = $stmt->fetch();

if ($user === false) {
    echo "User not found.";
}

// For fetchAll() — returns empty array when no rows found
$stmt = $pdo->prepare("SELECT * FROM products WHERE price > ?");
$stmt->execute([99999]);
$products = $stmt->fetchAll();

if (empty($products)) {
    echo "No products found.";
}

// For counting affected rows (INSERT, UPDATE, DELETE)
$stmt = $pdo->prepare("DELETE FROM sessions WHERE expires_at < NOW()");
$stmt->execute();
$deleted = $stmt->rowCount();
echo "$deleted expired sessions removed.";

A Reusable Database Class

Instead of creating a connection in every file, let's build a reusable Database class that handles connection setup, configuration, and provides a clean API.

<?php
// File: Database.php

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

    // Default connection settings
    private const DEFAULT_OPTIONS = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];

    // Prevent direct instantiation (use getConnection instead)
    private function __construct() {}
    private function __clone() {}

    /**
     * Get the shared PDO connection (Singleton pattern).
     * Creates the connection on first call, reuses it afterward.
     */
    public static function getConnection(): PDO {
        if (self::$instance === null) {
            $host   = $_ENV["DB_HOST"]   ?? "localhost";
            $dbname = $_ENV["DB_NAME"]   ?? "php_course";
            $user   = $_ENV["DB_USER"]   ?? "root";
            $pass   = $_ENV["DB_PASS"]   ?? "";

            $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

            try {
                self::$instance = new PDO($dsn, $user, $pass, self::DEFAULT_OPTIONS);
            } catch (PDOException $e) {
                error_log("Database connection failed: " . $e->getMessage());
                throw new RuntimeException("Database unavailable", 0, $e);
            }
        }

        return self::$instance;
    }

    /**
     * Shortcut: prepare and execute a query in one call.
     */
    public static function run(string $sql, array $params = []): PDOStatement {
        $stmt = self::getConnection()->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }

    /**
     * Close the connection (rarely needed — PHP closes it at script end).
     */
    public static function close(): void {
        self::$instance = null;
    }
}

// === Usage ===

// Simple queries become one-liners:
$users = Database::run("SELECT * FROM users WHERE active = ?", [1])->fetchAll();

$user = Database::run(
    "SELECT * FROM users WHERE id = :id",
    ["id" => 42]
)->fetch();

$count = Database::run("SELECT COUNT(*) FROM users")->fetchColumn();

Database::run(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ["Alice", "alice@example.com"]
);

$affected = Database::run(
    "UPDATE users SET active = 0 WHERE last_login < ?",
    ["2025-01-01"]
)->rowCount();

echo "$affected accounts deactivated.";

Using the Database Class in a Page

<?php
// File: users.php
require_once "Database.php";

// Get all active users
$users = Database::run(
    "SELECT id, name, email, created_at FROM users WHERE active = 1 ORDER BY name"
)->fetchAll();

// Display in HTML
?>
<!DOCTYPE html>
<html>
<head><title>Users</title></head>
<body>
    <h1>Active Users ()</h1>

    <table>
        <thead>
            <tr>
                <th>Name</th>
                <th>Email</th>
                <th>Joined</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($users as $user): ?>
            <tr>
                <td><?= htmlspecialchars($user["name"]) ?></td>
                <td><?= htmlspecialchars($user["email"]) ?></td>
                <td><?= $user["created_at"] ?></td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</body>
</html>

Hands-On Exercises

<?php
// Connect
$dsn = "mysql:host=localhost;dbname=php_course;charset=utf8mb4";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, "root", "", $options);
    echo "Connected!\n\n";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// Create table
$pdo->exec("CREATE TABLE IF NOT EXISTS notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)");
echo "Table ready.\n\n";

// Insert notes
$stmt = $pdo->prepare("INSERT INTO notes (title, content) VALUES (:title, :content)");

$notes = [
    ["title" => "PDO Basics",       "content" => "PDO is PHP's modern database interface."],
    ["title" => "Prepared Statements", "content" => "Always use placeholders for user input."],
    ["title" => "Fetch Modes",       "content" => "FETCH_ASSOC is the most common mode."],
];

foreach ($notes as $note) {
    $stmt->execute($note);
}
echo count($notes) . " notes inserted.\n\n";

// Fetch all notes
$allNotes = $pdo->query("SELECT * FROM notes ORDER BY created_at DESC")->fetchAll();
foreach ($allNotes as $note) {
    echo "#{$note['id']} {$note['title']}\n";
    echo "   {$note['content']}\n";
    echo "   Created: {$note['created_at']}\n\n";
}

// Count
$count = $pdo->query("SELECT COUNT(*) FROM notes")->fetchColumn();
echo "Total notes: $count\n";

<?php
class UserRepository {
    private PDO $pdo;

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

    public function findById(int $id): ?array {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE id = ?");
        $stmt->execute([$id]);
        $user = $stmt->fetch();
        return $user ?: null;
    }

    public function findByEmail(string $email): ?array {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE email = ?");
        $stmt->execute([$email]);
        $user = $stmt->fetch();
        return $user ?: null;
    }

    public function search(string $query): array {
        $pattern = "%" . $query . "%";
        $stmt = $this->pdo->prepare(
            "SELECT * FROM users WHERE name LIKE :q OR email LIKE :q ORDER BY name"
        );
        $stmt->execute(["q" => $pattern]);
        return $stmt->fetchAll();
    }

    public function findActive(int $limit = 10, int $offset = 0): array {
        $stmt = $this->pdo->prepare(
            "SELECT * FROM users WHERE active = 1 ORDER BY name LIMIT :limit OFFSET :offset"
        );
        $stmt->bindValue(":limit", $limit, PDO::PARAM_INT);
        $stmt->bindValue(":offset", $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }
}

// Usage
$dsn = "mysql:host=localhost;dbname=php_course;charset=utf8mb4";
$pdo = new PDO($dsn, "root", "", [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

$repo = new UserRepository($pdo);

// Find by ID
$user = $repo->findById(1);
echo $user ? "Found: {$user['name']}\n" : "Not found\n";

// Search
$results = $repo->search("alice");
echo count($results) . " results for 'alice'\n";

// Paginated
$page1 = $repo->findActive(limit: 5, offset: 0);
$page2 = $repo->findActive(limit: 5, offset: 5);
echo "Page 1: " . count($page1) . " users\n";
echo "Page 2: " . count($page2) . " users\n";

Summary

📚 Additional Resources

• PHP Manual: PDO
• PHP Manual: PDO Connections
• PHP Manual: Prepared Statements
• PHP Manual: PDOStatement::fetch
• PHP Delusions: PDO Tutorial (community favorite)

🚀 What's Next?

Now that you can connect and read data, Lesson 20: PDO — CRUD, Transactions & Fetch Styles will complete your database toolkit. You'll learn INSERT, UPDATE, and DELETE with PDO, use transactions for multi-step operations, get the last inserted ID, and master advanced fetch patterns. Then in Lesson 21, you'll build a complete CRUD application that ties everything together.