PDO: Connecting and Querying Safely
Connect to a database from PHP using PDO — building a DSN, setting the connection charset, configuring the error mode — and use prepared statements with bound parameters, fetch modes, and transactions to read and write data safely.
PDO (PHP Data Objects) is PHP's standard, database-agnostic interface for talking to relational databases. Rather than learning a separate extension for MySQL, PostgreSQL, and SQLite, PDO gives you one consistent API — the same prepare()/execute()/fetch() calls work against all of them, with only the connection string changing. This lesson builds a PDO connection from the ground up: constructing a DSN, connecting, configuring the charset and error mode, writing prepared statements with both named and positional placeholders, binding parameters explicitly, choosing a fetch mode for the shape of data you want back, and wrapping multi-step writes in a transaction.
The DSN and Connecting
A DSN (Data Source Name) is a string that tells PDO which driver to use and how to reach the database — the driver name, then a colon, then driver-specific key=value pairs. For MySQL/MariaDB: mysql:host=localhost;port=3306;dbname=shop;charset=utf8mb4. For PostgreSQL: pgsql:host=localhost;port=5432;dbname=shop. For SQLite, the DSN is just a file path (or :memory: for an in-memory database): sqlite:/var/data/shop.sqlite.
You connect by constructing a new PDO($dsn, $username, $password, $options). Username and password are passed separately from the DSN (and are irrelevant/omittable for SQLite, which has no login). The fourth argument, $options, is an associative array of PDO::ATTR_* constants you can set at connection time — the error mode and a few driver-specific flags are commonly set here rather than after the fact.
If the connection fails — wrong host, wrong credentials, database doesn't exist — the PDO constructor throws a PDOException. It's important not to let that exception's message reach end users in production: it can include the database host, username, or even parts of the DSN, which is information you don't want leaking in an error page. Catch it, log the details server-side, and show the user a generic failure message.
• DSN = driver:key=value;key=value;... — tells PDO which database and how to reach it. • MySQL/MariaDB: mysql:host=...;dbname=...;charset=utf8mb4 • PostgreSQL: pgsql:host=...;dbname=... • SQLite: sqlite:/path/to/file.sqlite (or sqlite::memory: for an in-memory DB) • new PDO($dsn, $user, $pass, $options) — connects; throws PDOException on failure.
Charset and Error Mode
For MySQL/MariaDB specifically, always set the connection charset to utf8mb4 (the full 4-byte UTF-8, which supports emoji and the full Unicode range — plain utf8 in MySQL is actually a 3-byte-max legacy encoding and can silently mangle or reject some characters). The safest place to set it is directly in the DSN (charset=utf8mb4), because that's applied before the connection is fully established; setting it afterward via a SET NAMES query works but leaves a brief window where the wrong charset is active.
PDO's error mode controls what happens when a database operation fails. There are three modes: PDO::ERRMODE_SILENT (the default if you don't set it — errors are swallowed, and you have to manually check for them after every call, which is exactly the kind of thing that gets forgotten), PDO::ERRMODE_WARNING (raises a PHP warning but keeps executing), and PDO::ERRMODE_EXCEPTION (throws a PDOException, letting normal try/catch and your framework's error handling take over). Virtually every modern PHP codebase sets PDO::ERRMODE_EXCEPTION immediately after connecting — it's one of the first things to configure, and forgetting it is a classic source of silently-ignored database errors.
• utf8mb4 = full UTF-8 (emoji included); set it in the DSN for MySQL/MariaDB. • ERRMODE_SILENT (default): errors are swallowed — you must check manually. Avoid this. • ERRMODE_WARNING: raises a warning, keeps running. • ERRMODE_EXCEPTION: throws PDOException — the standard choice for modern PHP code.
Prepared Statements and Parameter Binding
A prepared statement separates SQL structure from the data plugged into it. You call $pdo->prepare($sql) with placeholders instead of literal values, then supply the actual values separately via execute() or explicit binding — the database driver sends the query and the data as distinct pieces, so user-supplied values are never interpreted as SQL syntax. This is the standard, correct way to include dynamic values in a query (SQL injection prevention is covered in depth in a later lesson, but this is where it starts).
PDO supports two placeholder styles. Positional placeholders use ? and are matched to values by order: $pdo->prepare('SELECT * FROM users WHERE id = ? AND active = ?') then execute([$id, true]). Named placeholders use :name and are matched by key, which is often clearer for queries with several parameters: $pdo->prepare('SELECT * FROM users WHERE id = :id AND active = :active') then execute(['id' => $id, 'active' => true]). You can't mix both styles in the same query.
For simple cases, passing an array straight to execute() is enough. For more control, bindValue($param, $value, $type) binds a specific value immediately (the value is fixed at the moment of the call), while bindParam($param, $variable, $type) binds a PHP variable by reference — its value is read at the moment execute() actually runs, which matters if the variable changes in a loop between binding and executing. The optional $type argument (PDO::PARAM_INT, PDO::PARAM_STR, PDO::PARAM_BOOL, PDO::PARAM_NULL) tells the driver how to treat the value; PDO does a reasonable job of guessing from PHP's own type otherwise, but being explicit avoids subtle bugs, especially with booleans and NULL.
• Positional: WHERE id = ? — execute([$id]) — matched by order. • Named: WHERE id = :id — execute(['id' => $id]) — matched by key; cannot mix with positional. • bindValue($param, $value, $type) — binds a fixed value now. • bindParam($param, &$variable, $type) — binds a variable by reference, read at execute() time. • PDO::PARAM_INT / PARAM_STR / PARAM_BOOL / PARAM_NULL — explicit typing avoids ambiguity.
Fetch Modes
After executing a SELECT, you pull rows back with fetch() (one row) or fetchAll() (every remaining row), and the fetch mode controls what shape each row comes back as. PDO::FETCH_ASSOC returns each row as an associative array keyed by column name — the most commonly used mode, and a sensible default. PDO::FETCH_OBJ returns each row as a plain stdClass object with one property per column, which reads nicely when you want $row->name instead of $row['name']. PDO::FETCH_CLASS goes further and hydrates each row directly into an instance of a class you specify, mapping column names to matching property names — handy for turning query results straight into simple data/model objects.
You can set a default fetch mode for the whole connection with PDO::ATTR_DEFAULT_FETCH_MODE in the connection options, or pass the mode explicitly per call to fetch()/fetchAll(), which overrides the default just for that call. Passing it explicitly at the call site is generally clearer to read, since the fetch shape is visible right where the data is consumed.
• FETCH_ASSOC — associative array, keyed by column name. Most common default. • FETCH_OBJ — stdClass object, one property per column. • FETCH_CLASS — hydrates rows into instances of a given class by matching property names. • fetch() — one row (or false); fetchAll() — every remaining row as an array.
Transactions in PDO
PDO exposes transactions with three methods: beginTransaction() starts one (and turns off autocommit until it ends), commit() makes every change since then permanent, and rollBack() discards every change since beginTransaction() was called. The standard pattern wraps the work in a try/catch: begin the transaction, run the statements, commit if everything succeeded, and roll back in the catch block if anything threw — which, with ERRMODE_EXCEPTION set, includes any database error along the way, not just application-level checks.
A transaction should be as short as practical: hold it open only for the statements that genuinely need to succeed or fail together, and avoid doing slow, unrelated work (an HTTP call, heavy computation) in the middle of one, since it can hold locks on the affected rows for the whole time it's open, blocking other requests that touch the same data.
• beginTransaction() — starts a transaction. • commit() — makes all changes since beginTransaction() permanent. • rollBack() — discards all changes since beginTransaction(). • Keep transactions short — don't hold one open across slow, unrelated work.
A Standard PDO Connection
PDO::ATTR_EMULATE_PREPARES => false makes PDO use the database's real native prepared statements instead of PHP-side emulation, which is both safer and lets numeric types round-trip correctly. Never echo $e->getMessage() from a connection failure straight to the browser in production — it can leak host/credential details. Setting ERRMODE_EXCEPTION in $options means it's active from the very first statement, including the connection attempt itself.
<?php
declare(strict_types=1);
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4';
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
try {
$pdo = new PDO($dsn, 'shop_user', 'secret-password', $options);
} catch (PDOException $e) {
error_log('DB connection failed: ' . $e->getMessage());
throw new RuntimeException('Could not connect to the database.');
}Code Examples
The SQL is prepared once with a named placeholder, and the actual value is supplied through execute() as an associative array — the driver keeps the query structure and the data separate, so:max_price can never be interpreted as SQL. Expected result: Wireless Mouse: $24.99 USB Cable: $8.5 $stmt->execute(['max_price' => 50.00]);
bindValue() locks in the value the moment it's called, while bindParam() binds the variable itself — execute() reads whatever that variable holds at that moment, which is exactly what lets one prepared statement be reused efficiently across a loop.
$insert->bindParam(':msg', $message);
FETCH_OBJ gives back a generic stdClass with one property per column; FETCH_CLASS, set via setFetchMode() with the target class, hydrates the same row directly into a typed ProductDTO instance, matching columns to public properties by name.
$stmt->setFetchMode(PDO::FETCH_CLASS, ProductDTO::class);
Two UPDATE statements must both succeed for the stock transfer to be correct; ERRMODE_EXCEPTION means any database-level failure throws automatically, and the explicit RuntimeException covers the business-rule failure (not enough stock) — either path lands in the catch block, which rolls back cleanly.
$pdo->beginTransaction(); ... $pdo->commit(); ... catch { $pdo->rollBack(); }
<?php
declare(strict_types=1);
$stmt = $pdo->prepare(
'SELECT id, name, price FROM products WHERE price <= :max_price AND stock > 0'
);
$stmt->execute(['max_price' => 50.00]);
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($products as $product) {
echo "{$product['name']}: \${$product['price']}" . PHP_EOL;
}<?php
declare(strict_types=1);
// Positional placeholders with bindValue (fixed value at bind time)
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ? AND email = ?');
$stmt->bindValue(1, 42, PDO::PARAM_INT);
$stmt->bindValue(2, '[email protected]', PDO::PARAM_STR);
$stmt->execute();
$user = $stmt->fetch(PDO::FETCH_OBJ);
// bindParam binds a variable by reference — useful when reusing one
// statement in a loop with a variable that changes between calls.
$insert = $pdo->prepare('INSERT INTO log_entries (message) VALUES (:msg)');
$insert->bindParam(':msg', $message);
foreach (['Started import', 'Row 1 processed', 'Import complete'] as $message) {
$insert->execute(); // reads the CURRENT value of $message each time
}<?php
declare(strict_types=1);
class ProductDTO
{
public int $id;
public string $name;
public float $price;
}
$stmt = $pdo->prepare('SELECT id, name, price FROM products WHERE id = :id');
$stmt->execute(['id' => 12]);
$asObject = $stmt->fetch(PDO::FETCH_OBJ);
echo $asObject->name . PHP_EOL; // stdClass, dynamic property access
$stmt->execute(['id' => 12]); // re-run to fetch again
$stmt->setFetchMode(PDO::FETCH_CLASS, ProductDTO::class);
$asDto = $stmt->fetch();
echo $asDto->name . ' costs $' . $asDto->price . PHP_EOL; // typed ProductDTO instance<?php
declare(strict_types=1);
function transferStock(PDO $pdo, int $productId, int $fromWarehouse, int $toWarehouse, int $qty): void
{
$pdo->beginTransaction();
try {
$debit = $pdo->prepare(
'UPDATE warehouse_stock SET qty = qty - :qty
WHERE product_id = :pid AND warehouse_id = :wid AND qty >= :qty'
);
$debit->execute(['qty' => $qty, 'pid' => $productId, 'wid' => $fromWarehouse]);
if ($debit->rowCount() === 0) {
throw new RuntimeException('Not enough stock to transfer.');
}
$credit = $pdo->prepare(
'UPDATE warehouse_stock SET qty = qty + :qty
WHERE product_id = :pid AND warehouse_id = :wid'
);
$credit->execute(['qty' => $qty, 'pid' => $productId, 'wid' => $toWarehouse]);
$pdo->commit();
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}
}Important Rules
Set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION immediately on connecting.
Always use a prepared statement with bound parameters for any value that isn't a hardcoded literal.
For MySQL/MariaDB, set charset=utf8mb4 in the DSN.
Choose FETCH_ASSOC/FETCH_OBJ/FETCH_CLASS based on how the calling code will consume the row, not by habit.
Wrap multi-statement writes in beginTransaction()/commit(), with rollBack() in a catch block.
Never echo raw PDOException messages to end users in production.
Where This Shows Up
Every framework's database layer (Laravel's query builder/Eloquent, Symfony's Doctrine DBAL) is built on top of PDO under the hood for MySQL/PostgreSQL/SQLite support. Knowing PDO directly means you can drop down to raw, optimized queries when an ORM's generated SQL isn't efficient enough, and it's exactly what small scripts, CLI tools, and legacy codebases without a framework use directly.
$pdo = new PDO($dsn, $user, $pass); // no options — default error mode is ERRMODE_SILENT
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
Without explicitly setting ERRMODE_EXCEPTION, a failed query fails silently — execute() just returns false and the bug can go unnoticed for a long time. Set it as one of the very first things after connecting.
$stmt->bindValue(':price', $price, PDO::PARAM_STR); // storing money as a float bound as a string is fragile
Store money as DECIMAL in the schema and keep it as a string or integer-cents in PHP; avoid float arithmetic on currency entirely.
Floating-point numbers can't represent most decimal fractions exactly, so repeated float arithmetic on prices accumulates rounding errors. Use DECIMAL columns and either string-based/BCMath arithmetic or integer cents in application code, never plain float math for money.
What is PDO's default error mode if you never set PDO::ATTR_ERRMODE? A. PDO::ERRMODE_EXCEPTION B. PDO::ERRMODE_WARNING C. PDO::ERRMODE_SILENT D. PDO has no error mode concept
C. PDO::ERRMODE_SILENT — PDO defaults to ERRMODE_SILENT, where errors are swallowed and must be checked manually — which is why setting ERRMODE_EXCEPTION explicitly is standard practice.
What is the key difference between bindValue() and bindParam()? A. bindValue only works with integers; bindParam only works with strings B. bindValue fixes the value at the moment it's called; bindParam binds a variable by reference, read when execute() runs C. They are exactly the same, just different names D. bindParam requires ERRMODE_EXCEPTION to be set
B. bindValue fixes the value at the moment it's called; bindParam binds a variable by reference, read when execute() runs — bindValue() captures the value immediately; bindParam() binds the variable itself, so its current value at execute() time is what gets used — important when reusing a statement in a loop.
Why should MySQL/MariaDB connections in PDO use charset=utf8mb4 rather than plain utf8? A. utf8mb4 is faster to parse B. MySQL's plain 'utf8' is actually a legacy 3-byte-max encoding that can't represent the full Unicode range (e.g. emoji); utf8mb4 is true full UTF-8 C. utf8mb4 is required for prepared statements to work at all D. There is no difference; utf8mb4 is just an alias for utf8
B. MySQL's plain 'utf8' is actually a legacy 3-byte-max encoding that can't represent the full Unicode range (e.g. emoji); utf8mb4 is true full UTF-8 — MySQL's historical 'utf8' charset only supports up to 3 bytes per character, missing characters outside the Basic Multilingual Plane (like most emoji). utf8mb4 supports the full 4-byte UTF-8 range.
Write a PHP function findUserByEmail(PDO $pdo, string $email): ?array that uses a prepared statement with a named placeholder to look up a user by email, returns the row as an associative array via FETCH_ASSOC, and returns null if no user matches.
function findUserByEmail(PDO $pdo, string $email): ?array { $stmt = $pdo->prepare('SELECT * FROM users WHERE email =:email'); $stmt->execute(['email' => $email]); $row = $stmt->fetch(PDO::FETCH_ASSOC); return $row === false ? null: $row; } fetch() returns false (not null) when there's no matching row — you'll need to convert that.