Lesson 17 of 20

PDO Database Access

Connecting: the DSN and Three Options That Matter

PDO stands for PHP Data Objects. It is a single interface that speaks to MySQL, PostgreSQL, SQLite and others, so the code you write to query one is almost identical to the code for another. That portability is genuinely useful — a project can develop against SQLite and deploy against MySQL — but the day-to-day benefit is simply that PDO is pleasant to use and is what the wider PHP ecosystem expects.

A connection is created with new PDO(), whose first argument is the DSN, or data source name: a string naming the driver and how to reach the database. For MySQL it names the host, the database, and — importantly — the character set. Putting charset=utf8mb4 in the DSN is the correct way to set it for PDO, and leaving it out is how text in Indian languages ends up mangled.

The fourth argument is an options array, and three entries deserve to be there every time. PDO::ATTR_ERRMODE set to ERRMODE_EXCEPTION makes every failure throw a PDOException instead of returning false quietly; this has been the default since PHP 8.0, but setting it explicitly documents the intent and keeps the code correct on older versions.

PDO::ATTR_DEFAULT_FETCH_MODE set to FETCH_ASSOC makes fetched rows plain associative arrays. Without it, PDO's default returns each value twice — once by column name and once by number — which doubles your memory use and produces confusing output when you dump a row.

PDO::ATTR_EMULATE_PREPARES set to false asks the database to prepare statements natively rather than having PDO simulate them by building the query itself. Real prepared statements are the stronger guarantee, and they also let the database reuse a query plan when you run the same statement repeatedly.

Example 
<?php
// db.php - included by every page. Keep credentials out of this file.
declare(strict_types=1);

$config = require __DIR__ . '/../config/database.php';

$dsn = sprintf(
    'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
    $config['host'],
    $config['port'] ?? 3306,
    $config['name']
);

try {
    $pdo = new PDO($dsn, $config['user'], $config['pass'], [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
} catch (PDOException $e) {
    error_log('DB connection failed: ' . $e->getMessage());
    http_response_code(503);
    exit('The service is temporarily unavailable.');
}

return $pdo;
Notes
  • A PDOException message can contain the failing SQL, and a connection failure message can contain the username. Log it, never echo it. On a live server the combination of display_errors = Off and a catch block that shows a generic message is what keeps an accidental exception from becoming a disclosure.

Placeholders: Positional and Named

PDO supports two placeholder styles. Positional placeholders are question marks filled in order, which is compact and fine for one or two values. Named placeholders look like :email and are matched by name, which is far easier to read once a statement has five or six values — and much safer to edit, because reordering the columns in an INSERT no longer means silently reordering your data.

You cannot mix the two styles in one statement. Pick one per query; most people use named placeholders for inserts and updates, and question marks for short lookups.

When passing an array to execute() with named placeholders, the keys may include the colon or omit it — both work, and omitting it is the more common style. What matters is that every placeholder in the SQL has a matching key, or you get an error about the number of bound variables.

There is one restriction worth knowing before it confuses you: with emulation switched off, a named placeholder cannot be used twice in the same statement. A query that needs the same value in two places must either use two differently named placeholders, or pass the value twice. This is a limitation of how the database itself handles prepared statements, not of PDO.

The other detail that catches people is LIMIT. With ATTR_EMULATE_PREPARES set to false, values passed through the execute() array are sent to MySQL as strings, and MySQL will not accept a string where LIMIT expects a number. The fix is bindValue() with PDO::PARAM_INT, which states the type explicitly. Since the numbers involved are page sizes you control, casting them with (int) first and binding them properly is straightforward.

Example 
<?php
// Positional - fine for short queries
$stmt = $pdo->prepare('SELECT id, name FROM students WHERE branch = ? AND marks >= ?');
$stmt->execute(['CSE', 40]);
$rows = $stmt->fetchAll();

// Named - much clearer once there are several values
$stmt = $pdo->prepare(
    'INSERT INTO students (roll_no, name, email, branch, marks)
     VALUES (:roll, :name, :email, :branch, :marks)'
);
$stmt->execute([
    'roll'   => 118,
    'name'   => 'Ananya Sharma',
    'email'  => 'ananya@example.com',
    'branch' => 'CSE',
    'marks'  => 91,
]);

// Same value needed twice: use two names
$stmt = $pdo->prepare(
    'SELECT * FROM users WHERE email = :email1 OR username = :email2'
);
$stmt->execute(['email1' => $input, 'email2' => $input]);

// LIMIT needs an explicitly typed integer when emulation is off
$perPage = 20;
$offset  = (max(1, (int) ($_GET['page'] ?? 1)) - 1) * $perPage;

$stmt = $pdo->prepare('SELECT name, marks FROM students ORDER BY marks DESC LIMIT :lim OFFSET :off');
$stmt->bindValue(':lim', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':off', $offset,  PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll();

// An IN list needs one placeholder per value
$ids  = [3, 7, 12];
$marks = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT id, name FROM students WHERE id IN ($marks)");
$stmt->execute($ids);
Notes
  • In that IN example, the only thing built by string concatenation is a row of question marks generated from count($ids) — never the ids themselves. That is the pattern to copy: PHP may decide the shape of the query, but the data always arrives as bound parameters.

Fetching Results

fetch() returns the next row, or false when there are none left. fetchAll() returns every remaining row as an array of rows. fetchColumn() returns a single value from the next row, which is exactly right for a COUNT(*) or for pulling one field.

The choice between fetch() in a loop and fetchAll() is about memory. fetchAll() loads the entire result set into PHP at once, which is convenient and completely fine for a page of twenty rows. On a query returning fifty thousand rows it will exhaust memory_limit. Looping with fetch() — or simply iterating the statement directly with foreach, which PDO supports — keeps only one row in memory at a time.

PDO also offers fetch modes that shape the result for you. FETCH_COLUMN gives a flat list of one column's values. FETCH_KEY_PAIR turns a two-column result into an associative array, which is perfect for building a dropdown of id to name. FETCH_CLASS creates objects of a class you name, populating properties from columns — a lightweight way to get real objects out of the database without writing a mapping layer.

One thing to be careful about: rowCount() reliably reports how many rows an INSERT, UPDATE or DELETE affected, but it is not guaranteed to report the number of rows a SELECT returned. Different drivers behave differently. To find out whether a lookup found anything, check whether the fetched row is falsy; to count rows, ask the database with SELECT COUNT(*).

Finally, remember that fetching gives you data typed as the driver decided — often strings, even for integer columns. Cast when it matters, and escape with htmlspecialchars() when you print, exactly as with any other input. Data that came out of your own database was originally typed by a user.

Example 
<?php
// One row
$stmt = $pdo->prepare('SELECT id, name, marks FROM students WHERE id = ?');
$stmt->execute([$id]);
$student = $stmt->fetch();

if (!$student) {
    http_response_code(404);
    exit('Student not found.');
}

// All rows - fine for a page-sized result
$rows = $pdo->query('SELECT name, marks FROM students ORDER BY marks DESC LIMIT 20')
            ->fetchAll();

// A single value
$stmt = $pdo->prepare('SELECT COUNT(*) FROM students WHERE branch = ?');
$stmt->execute(['CSE']);
$total = (int) $stmt->fetchColumn();

// Streaming a large result: only one row in memory at a time
$stmt = $pdo->query('SELECT roll_no, name FROM students');
foreach ($stmt as $row) {
    echo htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8') . '<br>';
}

// Shaped results
$names = $pdo->query('SELECT name FROM students')->fetchAll(PDO::FETCH_COLUMN);
// ['Ananya', 'Ravi', ...]

$options = $pdo->query('SELECT id, name FROM branches')->fetchAll(PDO::FETCH_KEY_PAIR);
// [1 => 'CSE', 2 => 'ECE']  - ready for a <select>

class Student { public int $id; public string $name; public int $marks; }

$stmt = $pdo->query('SELECT id, name, marks FROM students');
$objects = $stmt->fetchAll(PDO::FETCH_CLASS, Student::class);
echo $objects[0]->name;
Notes
  • Select the columns you need rather than writing SELECT *. It transfers less data, it does not break when someone adds a large column to the table, and — most usefully — it means a password hash or a private note cannot end up in a result set that gets dumped into a JSON response by accident.

Writing Data, and Reading What Happened

Inserts, updates and deletes follow the same prepare-and-execute pattern. Afterwards, two methods tell you the outcome. $pdo->lastInsertId() returns the auto-increment id generated by the last insert on that connection, and $stmt->rowCount() returns how many rows the statement changed.

lastInsertId() returns a string, so cast it if you are going to compare it strictly or store it in a typed property. It is per-connection, which means it is safe under concurrency — another request inserting at the same moment cannot give you their id.

A rowCount() of zero after an update is information, not an error. It means either that no row matched your WHERE clause or that the values were already identical. If you need to tell those apart, check for the row's existence first, or design the interface so it does not matter.

The same ownership discipline from the previous lesson applies here, and it is worth repeating because parameterising a query does nothing to enforce it. Every update and delete in a multi-user application should be scoped with AND user_id = ?, so that changing an id in a form cannot reach somebody else's record.

Finally, on inserting many rows: wrap the loop in a transaction. Each individual insert otherwise commits on its own, which means a separate round trip and a separate disk flush per row. Importing a few thousand attendance records goes from uncomfortably slow to instant with two extra lines.

Example 
<?php
// INSERT and use the new id
$stmt = $pdo->prepare(
    'INSERT INTO enquiries (user_id, subject, body, created_at)
     VALUES (:user_id, :subject, :body, NOW())'
);
$stmt->execute([
    'user_id' => $_SESSION['user_id'],
    'subject' => $subject,
    'body'    => $body,
]);

$newId = (int) $pdo->lastInsertId();

// UPDATE, scoped to the owner
$stmt = $pdo->prepare(
    'UPDATE enquiries SET subject = :subject, body = :body
     WHERE id = :id AND user_id = :user_id'
);
$stmt->execute([
    'subject' => $subject,
    'body'    => $body,
    'id'      => $id,
    'user_id' => $_SESSION['user_id'],
]);

if ($stmt->rowCount() === 0) {
    echo 'Nothing was updated.';
}

// DELETE, also scoped
$stmt = $pdo->prepare('DELETE FROM enquiries WHERE id = ? AND user_id = ?');
$stmt->execute([$id, $_SESSION['user_id']]);

// Bulk insert: prepare once, execute many, inside a transaction
$stmt = $pdo->prepare(
    'INSERT INTO attendance (student_id, on_date, present) VALUES (?, ?, ?)'
);

$pdo->beginTransaction();
foreach ($records as $r) {
    $stmt->execute([$r['student_id'], $r['date'], $r['present']]);
}
$pdo->commit();
Notes
  • Preparing the statement once outside the loop and calling execute() repeatedly is the point of prepared statements beyond safety: the database parses and plans the query a single time and then just runs it with new values.

Transactions: All of It, or None of It

Some operations only make sense as a unit. Placing an order writes a row to orders and several rows to order_items and reduces the stock count. If the script crashes after the first write, you have an order with no items and stock that was never adjusted — a mess that is hard to detect and harder to clean up.

A transaction makes several statements atomic. Call beginTransaction(), do the work, and call commit(). Until you commit, nothing you wrote is visible to anyone else, and calling rollBack() undoes all of it as though it never happened. The database guarantees you end up with either every change or none of them.

The natural way to write this in PHP is a try block that commits at the end and a catch that rolls back. Because PDO is configured to throw exceptions, any failure anywhere in the block jumps straight to the rollback — including failures in your own validation code, if you throw from there.

Two practical points. Rethrow or log the exception after rolling back; a catch that silently swallows the error leaves you with a transaction that quietly did nothing and a user who thinks it worked. And keep transactions short: they hold locks, and a transaction that stays open while you call an external payment API can block other requests for as long as that call takes.

One MySQL-specific caution. Statements that change the database structure — CREATE TABLE, ALTER TABLE, DROP — cause an implicit commit, so anything you did before them cannot be rolled back afterwards. Transactions are for data changes; keep schema changes out of them. Transactions also require a storage engine that supports them, which for MySQL means InnoDB — the default in modern versions, but worth checking on an old database.

Example 
<?php
function placeOrder(PDO $pdo, int $userId, array $items): int
{
    $pdo->beginTransaction();

    try {
        $pdo->prepare('INSERT INTO orders (user_id, created_at) VALUES (?, NOW())')
            ->execute([$userId]);

        $orderId = (int) $pdo->lastInsertId();

        $addItem = $pdo->prepare(
            'INSERT INTO order_items (order_id, sku, qty, price_paise)
             VALUES (?, ?, ?, ?)'
        );
        $reduce = $pdo->prepare(
            'UPDATE products SET stock = stock - ? WHERE sku = ? AND stock >= ?'
        );

        foreach ($items as $item) {
            $addItem->execute([$orderId, $item['sku'], $item['qty'], $item['price']]);

            $reduce->execute([$item['qty'], $item['sku'], $item['qty']]);
            if ($reduce->rowCount() === 0) {
                // not enough stock - abandon the whole order
                throw new RuntimeException("Out of stock: {$item['sku']}");
            }
        }

        $pdo->commit();
        return $orderId;

    } catch (Throwable $e) {
        $pdo->rollBack();
        error_log('Order failed: ' . $e->getMessage());
        throw $e;              // let the caller decide what to show
    }
}
Notes
  • Look at how the stock check works: the WHERE stock >= ? condition means the database itself refuses the update when stock is insufficient, and rowCount() tells us it refused. Reading the stock, checking it in PHP and then writing it back would leave a gap in which another request could take the last unit.

Keeping Database Code Out of Your Pages

Once you have more than a handful of pages, scattering SQL through your templates becomes a problem. The same query gets written slightly differently in three places, one of them forgets the ownership check, and changing a column name means searching the whole project.

The usual answer is a small class per table that owns every query about it — often called a repository. Each method is named after what it does in the language of your application, and the SQL lives inside. Your pages then read as a description of what happens rather than as a pile of statements.

Pass the PDO object into the constructor rather than creating it inside, or reaching for a global. This is dependency injection again, and the benefits are concrete: the class works with any connection, several classes share one connection instead of each opening their own, and a test can pass in a connection to a temporary SQLite database.

This is not a framework and does not need to be. Half a dozen small classes with clear method names is enough to make a project of twenty pages comfortable to work in, and it is the same shape that Laravel's Eloquent and Symfony's Doctrine formalise. Understanding it by hand first makes those frameworks much easier to learn later.

Note one more benefit visible in the example: because every query about students is in one place, the rule that a student belongs to a college and must be scoped by it is applied consistently. Rules enforced in one file stay enforced; rules repeated in twenty templates eventually get missed in one.

Example 
<?php
declare(strict_types=1);

final class StudentRepository
{
    public function __construct(private PDO $db) {}

    public function find(int $id): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT id, roll_no, name, email, marks FROM students WHERE id = ?'
        );
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    /** @return array<int, array<string, mixed>> */
    public function topScorers(int $limit = 10): array
    {
        $stmt = $this->db->prepare(
            'SELECT roll_no, name, marks FROM students ORDER BY marks DESC LIMIT :lim'
        );
        $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function create(string $name, string $email, int $roll): int
    {
        $stmt = $this->db->prepare(
            'INSERT INTO students (name, email, roll_no) VALUES (?, ?, ?)'
        );
        $stmt->execute([$name, $email, $roll]);
        return (int) $this->db->lastInsertId();
    }

    public function emailExists(string $email): bool
    {
        $stmt = $this->db->prepare('SELECT 1 FROM students WHERE email = ? LIMIT 1');
        $stmt->execute([$email]);
        return (bool) $stmt->fetchColumn();
    }
}

// In a page, the intent is what you read
$pdo      = require __DIR__ . '/../db.php';
$students = new StudentRepository($pdo);

if ($students->emailExists($email)) {
    $errors['email'] = 'That email is already registered.';
} else {
    $id = $students->create($name, $email, $roll);
}

foreach ($students->topScorers(5) as $row) {
    echo htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8');
}
Notes
  • SELECT 1 ... LIMIT 1 in emailExists() is deliberate: you only want to know whether a row exists, so there is no reason to transfer its contents. Small choices like this are what separate a query that stays fast on a large table from one that does not.
Ask AI