PDO — Talking to the Database

The mental model

PDO is a layer between your PHP code and any database. Your code speaks PHP method calls, the database speaks SQL — PDO translates between them in both directions, and abstracts away the connection details so you can swap databases later without rewriting your queries (mostly).

🐍 Python: PDO is the PHP equivalent of Python's DB-API 2.0 (PEP 249) — the standard followed by sqlite3, psycopg2, mysql-connector, etc. Same lifecycle: connect → cursor/prepare → execute(sql, params) → fetch. FETCH_ASSOC ≈ using sqlite3.Row or psycopg2.extras.DictCursor.

Connect properly — with the three options

The first step is opening a connection. Here's the right way, with all the important options set:

<?php
$dsn  = 'mysql:host=127.0.0.1;dbname=medtrack;charset=utf8mb4';
$user = 'medtrack_user';
$pass = 'your-password';

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

The DSN ("Data Source Name") is the connection string. mysql: picks the MySQL/MariaDB driver. The rest specifies host, database, and charset. Username and password as separate args.

The fourth argument — options — is where most tutorials skip critical settings. Don't skip these three:

• ERRMODE_EXCEPTION — when a database error happens, throw an exception instead of silently returning false. Without this, half your bugs become invisible. Critical.
• FETCH_ASSOC — fetches give you associative arrays ($row['name']) instead of the default "both," which returns each value twice (once with column name, once with numeric index). Saves memory and confusion.
• EMULATE_PREPARES => false — use the database's real prepared statements rather than PHP-side emulation. More secure (prevents some edge-case injection), returns native types (integers come back as integers, not strings).

Use 127.0.0.1, not localhost, in the DSN. Why? Because PHP treats the literal string "localhost" as "connect via Unix socket" — which usually doesn't work without extra config. 127.0.0.1 forces TCP and just works. We covered this in Part 1; it's worth repeating.

The cardinal rule — prepared statements

Pay attention to this section. It's literally the most important security topic in web development, and the mistake we're about to discuss has been the cause of countless data breaches for decades.

Never, ever, ever concatenate user input into SQL. Not even "just this one time for a tiny thing":

// ☠ SQL INJECTION
$sql = "SELECT * FROM users WHERE name = '$name'";
$pdo->query($sql);

Why is this catastrophic? Because if $name contains '; DROP TABLE users; --, the final SQL string becomes:

SELECT * FROM users WHERE name = ''; DROP TABLE users; --'

That's actually two SQL statements — a harmless SELECT followed by "DROP TABLE users." Your users table is now gone. This is called SQL injection. It's been around since the 90s. People still write code like this and get hacked.

The fix: prepared statements. The SQL pattern and the data travel through separate channels. The database parses the SQL first, then plugs in the data as pure values that can't possibly become SQL:

$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?");
$stmt->execute([$name]);
$user = $stmt->fetch();

The ? is a placeholder. When you call execute() with values, the database treats them as pure data, period. They cannot become SQL syntax, no matter what's in them. SQL injection becomes impossible by construction.

Analogy: imagine the difference between handing a stranger a piece of paper that says "please get me a sandwich" vs handing them a paper that says "please do whatever this person tells you to" and then telling them "make me a sandwich." With concatenation, the SQL and the data are mixed into one piece of paper — the database can't tell which is which. With prepared statements, structure is separated from values.

This is one of the highest-leverage habits in all of web programming. Just do it every single time you write SQL. There is no scenario where you should concatenate user input into SQL. None.

🐍 Python: Exact same rule. cursor.execute("SELECT * FROM users WHERE name = ?", (name,)). The ? (sqlite3) or %s (psycopg2/mysql) is the same parametrized-query pattern. Never use Python string formatting to build SQL with user input — same SQL injection nightmare in any language.

Named placeholders for clarity

For queries with more than two or three parameters, positional ? placeholders get confusing — which value goes where? Use named placeholders instead:

$stmt = $pdo->prepare("
    SELECT * FROM users
    WHERE age >= :min_age AND age <= :max_age AND status = :status
");
$stmt->execute([
    'min_age' => 18,
    'max_age' => 65,
    'status'  => 'active',
]);
$users = $stmt->fetchAll();

Self-documenting and order-independent. Rule of thumb: positional ? for 1-2 parameters, named placeholders for 3+. No performance difference; readability is the only criterion.

Fetching results

After running a SELECT, you have several ways to get the results:

$user  = $stmt->fetch();        // one row, or false
$users = $stmt->fetchAll();     // all rows as array
$count = $stmt->fetchColumn();  // first column of first row (scalar)

// Stream large results
foreach ($stmt as $row) { /* ... */ }

When to use which: fetchAll() for small result sets where you'll process them as a list — easy to work with, but loads everything into memory. foreach on the statement for large result sets — PHP streams rows one at a time. fetch() when you only need one row (find user by ID). fetchColumn() when you only need a single scalar value (a COUNT result).

INSERT, UPDATE, DELETE

Same prepared-statement pattern for writes. The only new method is lastInsertId(), which gives you the auto-generated ID of a freshly-inserted row:

// INSERT
$stmt = $pdo->prepare("
    INSERT INTO users (name, email, created_at)
    VALUES (:name, :email, NOW())
");
$stmt->execute(['name' => $name, 'email' => $email]);

$new_id = $pdo->lastInsertId();
echo "Created user #$new_id";

// UPDATE
$stmt = $pdo->prepare("UPDATE users SET email = :email WHERE id = :id");
$stmt->execute(['email' => $new_email, 'id' => $user_id]);
echo $stmt->rowCount() . " rows updated";

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

rowCount() tells you how many rows the statement affected — useful sanity check. If you expected to update 1 row but rowCount returns 0, something's off (wrong ID, race condition, etc).

Transactions — all or nothing

Sometimes multiple SQL statements have to either all succeed or all fail together. Classic example: transferring money between accounts. You debit one account and credit the other. If the program crashes between those two statements, you have money debited but not credited — accounting nightmare.

Transactions solve this. They group multiple statements into an atomic unit:

$pdo->beginTransaction();
try {
    $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?")
        ->execute([100, $from_id]);
    $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?")
        ->execute([100, $to_id]);
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

Mental model: transactions are like save points in a video game. You hit save, play through a level, and if you die you go back to the save point as if nothing happened. Database transactions work the same way — until you commit, all changes are reversible.

The reusable connection helper

You don't want to write new PDO(...) in every file. Make one helper function and call it everywhere:

// lib/db.php
function db(): PDO {
    static $pdo = null;
    if ($pdo === null) {
        $pdo = new PDO(
            'mysql:host=127.0.0.1;dbname=medtrack;charset=utf8mb4',
            'medtrack_user',
            getenv('DB_PASS') ?: 'your-password',
            [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
            ]
        );
    }
    return $pdo;
}

// Use anywhere in your app:
$users = db()->query("SELECT * FROM users")->fetchAll();

The static $pdo means the variable retains its value between calls. First call creates the connection; every subsequent call returns the same connection without reopening. Cheap and effective. Real apps use proper config files + dependency injection containers — but for a hand-built site, this pattern is more than enough. You can always upgrade later.

Pro tip: store the DB password in an environment variable (set with SetEnv DB_PASS=... in Apache config, or load from a .env file), not hard-coded in source. The instant you commit a password to git, treat it as compromised — even if you delete it later, it lives in the git history forever.

sudo mariadb

CREATE DATABASE medtrack CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'medtrack_user'@'localhost' IDENTIFIED BY 'your-strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON medtrack.* TO 'medtrack_user'@'localhost';
USE medtrack;
CREATE TABLE meds (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    dose INT NOT NULL,
    status ENUM('due','taken','overdue','skipped') NOT NULL DEFAULT 'due',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
EXIT;