Chapter 15 · Databases & Forms

Talking to MySQL with PDO

Now the two worlds meet: our PHP objects will speak to our MySQL tables. The bridge is called PDO (PHP Data Objects) — an object-oriented toolkit built into PHP. Everything you've learned about classes suddenly pays off.

Connecting

connect.php<?php
$host = "localhost";
$dbname = "school_db";       // from last chapter
$username = "root";          // XAMPP's default user
$password = "";              // XAMPP's default password is empty

try {
    $pdo = new PDO(
        "mysql:host=$host;dbname=$dbname;charset=utf8mb4",
        $username,
        $password
    );
    // Make PDO throw exceptions when things go wrong (always do this!)
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // Make results come back as associative arrays by default
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

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

Look how many friends from earlier chapters appear: new PDO(...) is a constructor taking arguments; $pdo is an object; setAttribute() is a method call; PDO::ATTR_ERRMODE is a class constant; PDOException is a custom exception. You already speak this language.

Reading rows — query and fetch

// Run a SELECT and get a "statement" object holding the results
$stmt = $pdo->query("SELECT * FROM students ORDER BY full_name");

// fetchAll() = give me every row, as an array of associative arrays
$students = $stmt->fetchAll();

foreach ($students as $row) {
    echo $row["full_name"] . " — " . $row["program"] . "<br>";
}

Each row arrives as an associative array whose keys are the column names — exactly the shape from Chapter 02. fetch() (singular) returns just one row; fetchAll() returns them all.

⚠️ The most important security lesson in this course

Suppose a search box lets users type a name, and we build the query by gluing strings:

// ☠️ NEVER DO THIS ☠️
$name = $_GET["name"];   // whatever the user typed
$stmt = $pdo->query("SELECT * FROM students WHERE full_name = '$name'");

A normal user types Chanda. An attacker types:

x'; DELETE FROM students; --

Glued into your string, that becomes two commands — the second one deletes your entire students table. This attack is called SQL injection, and it has destroyed real companies. The user typed code, and we foolishly executed it.

The cure: prepared statements

// ✅ ALWAYS DO THIS ✅
$stmt = $pdo->prepare("SELECT * FROM students WHERE full_name = ?");
$stmt->execute([$name]);          // the value fills the ? blank — safely
$student = $stmt->fetch();

// Multiple blanks fill in order:
$stmt = $pdo->prepare("SELECT * FROM students WHERE program = ? AND fee_balance > ?");
$stmt->execute(["BSc Computing", 5000]);

// Or use named blanks — clearer when there are many:
$stmt = $pdo->prepare(
    "INSERT INTO students (student_number, full_name, program, fee_balance)
     VALUES (:number, :name, :program, :balance)"
);
$stmt->execute([
    "number"  => "LGU2026-005",
    "name"    => "Natasha Zulu",
    "program" => "BSc Computing",
    "balance" => 5500,
]);

echo "New student id: " . $pdo->lastInsertId();

UPDATE and DELETE the same way

// Update
$stmt = $pdo->prepare("UPDATE students SET fee_balance = ? WHERE id = ?");
$stmt->execute([3500, 1]);
echo $stmt->rowCount() . " row(s) updated.<br>";

// Delete
$stmt = $pdo->prepare("DELETE FROM students WHERE id = ?");
$stmt->execute([5]);
echo $stmt->rowCount() . " row(s) deleted.";

Wrapping the connection in a class

Typing connection code on every page is repetition — and you know how OOP feels about repetition. Let's build a small Database class we'll reuse in the final project:

Database.php<?php
class Database {
    private static ?PDO $pdo = null;   // one shared connection (remember static?)

    public static function connect(): PDO {
        if (self::$pdo === null) {     // connect only the first time
            try {
                self::$pdo = new PDO(
                    "mysql:host=localhost;dbname=school_db;charset=utf8mb4",
                    "root",
                    ""
                );
                self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                self::$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            } catch (PDOException $e) {
                die("Database connection failed: " . $e->getMessage());
            }
        }
        return self::$pdo;
    }
}

any-page.php<?php
require "Database.php";

$pdo = Database::connect();
$students = $pdo->query("SELECT * FROM students")->fetchAll();
echo count($students) . " students found.";

A static method, a private static property, lazy connection, exception handling — every chapter of this course is inside those 20 lines. (This "only ever one instance" pattern even has a famous name: the Singleton.)

<?php
require "Database.php";

$pdo = Database::connect();

$stmt = $pdo->prepare("SELECT full_name, fee_balance FROM students WHERE fee_balance > ?");
$stmt->execute([5000]);

foreach ($stmt->fetchAll() as $row) {
    echo $row["full_name"] . " owes K" . $row["fee_balance"] . "<br>";
}