PDO: prepared statements, fetching and transactions

Every PHP screening that touches a database asks about prepared statements, and the first thing a reviewer looks for in your code is whether user input ever reaches a query as text. PDO handles all of this with a small, consistent API that works across MySQL, PostgreSQL and SQLite.

Connecting with the right options

$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

ERRMODE_EXCEPTION makes every failed query throw a PDOException instead of returning false quietly. FETCH_ASSOC returns rows as arrays keyed by column name, which is what most code wants. Create one connection per request and pass it in; do not open a new one in each function.

Prepared statements

The query is sent with placeholders, and the values travel separately, so a quote or a comment in the input is just data.

$stmt = $pdo->prepare("INSERT INTO users (email, city) VALUES (:email, :city)");
$stmt->execute(['email' => 'asha@example.com', 'city' => 'Pune']);

$email = "asha@example.com' OR '1'='1";
$stmt = $pdo->prepare("SELECT id, email FROM users WHERE email = ?");
$stmt->execute([$email]);
$row = $stmt->fetch();         // false: no user has that exact email

$stmt = $pdo->prepare("SELECT id, email FROM users WHERE city = :city");
$stmt->execute(['city' => 'Pune']);
foreach ($stmt->fetchAll() as $r) {
    echo $r['id'], ' ', $r['email'], "\n";
}

Named placeholders (:city) and positional ones (?) both work; do not mix them in one query. fetch() returns one row or false, fetchAll() returns every row, and fetchColumn() returns a single value. query() without parameters is fine only for fixed SQL with no user input. Table and column names cannot be placeholders; validate them against a fixed list instead.

Transactions and affected rows

try {
    $pdo->beginTransaction();
    $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?")->execute([500, 1]);
    $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?")->execute([500, 2]);
    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
}

If either update fails the whole transfer is rolled back, which is the point of a transaction. Check rowCount() after an update or delete when the business rule says exactly one row should change.

Common mistakes and a practice habit

• Building SQL with string concatenation or interpolation and calling it "escaped".
• Leaving the default silent error mode, so a typo in a column name produces an empty page.
• Showing $e->getMessage() to the visitor. Log it and show a plain message.
• Using LIKE with a placeholder but forgetting that the % belongs in the bound value, not in the SQL.

Practice habit: Write a small script against an SQLite file (new PDO('sqlite:test.db')) that creates a table, inserts three rows with one prepared statement, reads them back, and wraps two updates in a transaction that you force to fail. It takes thirty lines and covers every PDO question a screening will ask.