Performing CRUD Queries

An admin page needs to list the last twenty orders. You already know how to send a query safely — Prepared Statements and Binding covered prepare() and execute(). The question this chapter answers is what you actually get back from a SELECT, and why that answer changes with one attribute you set per connection.

PDO's default fetch mode is PDO::FETCH_BOTH: every row arrives as an array carrying the column names and the 0-based numeric indexes, so each value is stored twice and your template has to pick a key convention on the spot. Fix that once, immediately after connecting:

$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

Now $row['email'] works everywhere, nothing is duplicated in memory, and the row's shape matches what you will echo into HTML. FETCH_ASSOC — associative keys only — is the mode interviewers expect you to name when they ask how rows come out of PDO.

Four calls consume a result set, and they answer different questions:

The canonical read loop:

$stmt = $pdo->prepare(
    'SELECT id, email, created_at FROM users ORDER BY created_at DESC LIMIT 20'
);
$stmt->execute();
while ($row = $stmt->fetch()) {
    printf("#%d %s joined %s\n", $row['id'], $row['email'], $row['created_at']);
}

fetch() returns false at the end of the rows, and false is falsy, so the while condition terminates the loop with no separate row-count bookkeeping. When the query matches nothing, the first fetch() returns false and the loop body never runs. That empty case is exactly where fetchAll() and fetch() diverge in type: fetchAll() gives you [], which count() reports as 0, while fetch() gives you false, which you must never hand to count() — on PHP 8 that is a TypeError.

One confusion worth clearing now: execute() on a SELECT returns true — it means the statement ran, not that rows exist. Execution and fetching are two separate phases; the loop above is where rows materialize. Calling fetch() after the loop has exhausted the statement simply returns false again, not an error.

The trap is result size. fetchAll() materializes the entire result set in PHP memory before you touch it: a runaway export returning 500,000 rows will allocate hundreds of megabytes before your first line of output. The fix is not a bigger memory limit — it is staying on fetch() (or paging with LIMIT/OFFSET and calling closeCursor() between batches) so only one row lives in memory at a time. For a twenty-row admin list, fetchAll() is the right simplification; for anything that can grow without a bound you control, the fetch() loop is the safe default.

One more thing rowCount() will not do for you: on most drivers it is not a trustworthy row count for SELECT. If you need a number — pagination totals, cart badges — write SELECT COUNT(*) and read it through fetchColumn(). The write-path chapters on Transactions and Concurrency and the join patterns ahead both build on these read habits, so make the fetch-mode decision automatic now.

A signup handler inserts a users row, then needs the generated id for three follow-ups: a second profiles row keyed by user_id, a welcome-email job carrying the id, and a redirect to /users/42. The insert is one prepared statement; getting the id back correctly is a second skill, and it is the single most common PDO question in real interviews.

$stmt = $pdo->prepare('INSERT INTO users (email, display_name) VALUES (?, ?)');
$stmt->execute([$email, $displayName]);
$userId = $pdo->lastInsertId();   // '42' as a string

$pdo->prepare('INSERT INTO profiles (user_id, bio) VALUES (?, ?)')
    ->execute([$userId, '']);

Read the semantics precisely, because every part of that snippet is load-bearing:

• lastInsertId() lives on the connection, not the statement. It returns the value of the AUTO_INCREMENT column generated by the most recent INSERT on that $pdo handle. If any other INSERT runs on the connection between yours and the call — a logging insert, a helper method, a queued flush — you get their id and silently write profile rows against the wrong user. Keep the INSERT and the lastInsertId() call adjacent, with nothing in between.
• It returns a string. '42' compares fine in PHP and binds fine as an integer parameter, but === 42 is false. Cast if you care: (int) $pdo->lastInsertId().
• The optional argument names the sequence. lastInsertId('users') is meaningful on databases that use named sequences; on MySQL with a single auto-increment column per table it is redundant.
• Driver differences are fair game in interviews. PostgreSQL has no AUTO_INCREMENT; the idiom there is INSERT ... RETURNING id executed with query(), then read with fetch(PDO::FETCH_ASSOC). SQLite and MySQL both support lastInsertId(). Knowing the RETURNING variant signals you have written PDO against more than one engine.

What about verifying the insert itself? execute() returns a bool — false means the statement failed (with ERRMODE_EXCEPTION you get a PDOException instead, which is the configuration you want from Connecting with PDO). rowCount() for an INSERT should be 1, but the id handshake is the stronger proof: if lastInsertId() returns '0' immediately after a statement you believe succeeded, something is wrong with the table definition or the connection, and you want to find that out in the handler, not in the redirect.

The failure you will actually hit in a signup handler is the duplicate key. With a unique index on users.email, a second registration with the same address raises PDOException with driver code 1062 (MySQL) — SQLSTATE[23000]: Integrity constraint violation. That error is not a crash to swallow; it is the signal to branch: catch it and tell the user the address is already registered, or make the insert idempotent with INSERT ... ON DUPLICATE KEY UPDATE so a retry updates the timestamp instead of failing. Either way, distinguish "insert failed" from "insert succeeded twice" by reading the exception, never by assuming success.

The race condition is worth internalizing because it scales badly. This naive pattern breaks under any concurrency:

// BROKEN under concurrency: another request's insert can slip in between
$pdo->prepare('INSERT INTO users (email) VALUES (?)')->execute([$email]);
// ... an unrelated INSERT happens here on a reused connection ...
$id = $pdo->lastInsertId(); // not your row's id anymore

Call lastInsertId() on the same $pdo handle, immediately after the INSERT, before any other statement executes. Treat those two lines as one indivisible unit and you will never attach data to the wrong row again. With reads (SELECT) and inserts both solid, the remaining write operations — UPDATE and DELETE — carry their own reporting trap, which is the next chapter.

A profile-edit handler runs UPDATE users SET display_name = ? WHERE id = ?, then tells the user "saved". Weeks later a bug report says edits to a typo'd id "succeed" against nothing. Both failures come from the same habit: executing a write and never checking what it actually did. PDO reports that through rowCount().

$stmt = $pdo->prepare('UPDATE users SET display_name = ? WHERE id = ?');
$stmt->execute([$displayName, $userId]);

if ($stmt->rowCount() === 0) {
    // The row was already gone, or nothing changed. Do not report success.
}

rowCount() returns the number of rows the statement affected, which for UPDATE and DELETE means rows it matched and wrote. That number carries real information:

The subtlety that catches working developers is MySQL's counting rule. By default MySQL reports rows changed, not rows matched. Submit a display name identical to the stored one and rowCount() returns 0 even though the row exists and the statement was fine. When "did the row exist?" is the question you are asking, tell PDO to count matched rows instead:

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_FOUND_ROWS => true, // rowCount() now counts matched rows
]);

Two rules make UPDATE and DELETE safe. First, never issue either without a WHERE clause — a missing predicate turns a single-user edit into a full-table write that takes the lock for every row. Second, treat rowCount() === 0 as a branch you handle, not an error to swallow; it is how optimistic concurrency control works:

// Optimistic lock: only apply if nobody bumped the version since you read it
$stmt = $pdo->prepare(
    'UPDATE orders SET status = ?, version = version + 1
     WHERE id = ? AND version = ?'
);
$stmt->execute([$status, $orderId, $readVersion]);
if ($stmt->rowCount() === 0) {
    throw new RuntimeException('Order changed since you loaded it — reload and retry');
}

That pattern is how web apps avoid last-write-wins corruption without holding locks while users stare at forms. Note the arithmetic in the SET clause — version = version + 1 runs server-side, which is the same discipline Transactions and Concurrency enforces for balances and counters.

DELETE carries the same reporting duty, just in the other direction. A cleanup handler that removes stale sessions should read its own rowCount(): 0 means the filter matched nothing worth logging, 37 tells the audit log exactly how many rows left the table. Batching helps here — DELETE FROM sessions WHERE last_seen < ? is one statement for any number of rows, and rowCount() reports the total. The failure mode to never write is the loop of single-row DELETEs: N round trips where one statement would do, each one an independent chance to fail halfway with no transaction around it.

Reliability per statement type, compressed: rowCount() is meaningful for INSERT, UPDATE, and DELETE; it is not a row count for SELECT on most drivers — use SELECT COUNT(*) with fetchColumn() instead. Put the four habits together — associative fetch mode, the fetch() loop, the adjacent lastInsertId() call, and an checked rowCount() — and you have the full CRUD contract PDO expects you to honor on every write.