PHP PDO Database Access Cheat Sheet
Covers connecting with PDO, writing prepared statements, handling transactions, and key fetch and error mode configuration options.
1 PageIntermediateApr 12, 2026
Connecting with PDO
Opening a database connection with safe defaults.
php
try { $pdo = new PDO( "mysql:host=localhost;dbname=myapp;charset=utf8mb4", "dbuser", "dbpass", [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, ] );} catch (PDOException $e) { die("Connection failed: " . $e->getMessage());}
Prepared Statements
Safely binding parameters to queries.
php
// SELECT with named placeholders$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");$stmt->execute(['email' => '[email protected]']);$user = $stmt->fetch();// INSERT with positional placeholders$stmt = $pdo->prepare("INSERT INTO posts (title, body) VALUES (?, ?)");$stmt->execute([$title, $body]);$newId = $pdo->lastInsertId();// Fetching multiple rows$stmt = $pdo->query("SELECT id, name FROM users");foreach ($stmt->fetchAll() as $row) { echo $row['name'];}
Transactions
Grouping statements atomically with commit and rollback.
php
try { $pdo->beginTransaction(); $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?") ->execute([100, 1]); $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?") ->execute([100, 2]); $pdo->commit();} catch (PDOException $e) { $pdo->rollBack(); throw $e;}
PDO Concepts
Fetch modes, error handling, and binding methods.
- PDO::FETCH_ASSOC- Returns rows as associative arrays keyed by column name
- PDO::FETCH_OBJ- Returns rows as stdClass objects with column names as properties
- PDO::ERRMODE_EXCEPTION- Makes PDO throw a PDOException on errors instead of failing silently
- bindParam vs bindValue- bindParam binds by reference and reads the value at execute time; bindValue binds the value immediately
- lastInsertId()- Returns the auto-increment ID generated by the most recent INSERT
- Prepared statements- Separate SQL structure from data, automatically preventing SQL injection when placeholders are used
Bulk Inserts Inside a Transaction
Wrapping a loop of prepared-statement executions in a single transaction for far fewer round trips than autocommit-per-row.
php
$pdo->beginTransaction();$stmt = $pdo->prepare( "INSERT INTO events (user_id, type, payload) VALUES (:user_id, :type, :payload)");foreach ($events as $event) { $stmt->execute([ 'user_id' => $event['user_id'], 'type' => $event['type'], 'payload' => json_encode($event['payload']), ]);}$pdo->commit();// With autocommit on, each execute() would fsync a separate transaction;// batching thousands of inserts inside one beginTransaction()/commit()// pair is often 10-50x faster.
Advanced Fetch Modes
Hydrating rows directly into objects, key-value maps, and single-column arrays.
php
class UserDTO { public function __construct( public readonly int $id, public readonly string $name ) {}}$stmt = $pdo->prepare("SELECT id, name FROM users WHERE active = 1");$stmt->execute();// Hydrate rows straight into constructor-backed objects$stmt->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, UserDTO::class);$users = $stmt->fetchAll();// id => name associative map from a two-column result set$idToName = $pdo->query("SELECT id, name FROM users") ->fetchAll(PDO::FETCH_KEY_PAIR);// Flatten a single column across all rows$emails = $pdo->query("SELECT email FROM users") ->fetchAll(PDO::FETCH_COLUMN, 0);
Native vs Emulated Prepares
Disabling prepare emulation for correct scalar types and true server-side statement caching.
php
// The MySQL driver emulates prepares by default: values are escaped and// inlined client-side, and every result column comes back as a string.$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);$stmt = $pdo->prepare("SELECT COUNT(*) FROM orders WHERE status = ?");$stmt->execute(['shipped']);$count = $stmt->fetchColumn();var_dump($count); // int(42) instead of string(2) "42"// Native prepares also let the server cache and reuse the query plan// across repeated execute() calls with different bound values.
Savepoints & Upserts
Emulating nested transactions with savepoints and writing an idempotent upsert.
php
function transferFunds(PDO $pdo, int $from, int $to, float $amount): void { $pdo->beginTransaction(); try { $pdo->exec("SAVEPOINT before_transfer"); $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?") ->execute([$amount, $from]); $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?") ->execute([$amount, $to]); $pdo->exec("RELEASE SAVEPOINT before_transfer"); $pdo->commit(); } catch (PDOException $e) { $pdo->exec("ROLLBACK TO SAVEPOINT before_transfer"); $pdo->rollBack(); throw $e; }}// MySQL upsert: insert, or bump a counter if the row already exists$pdo->prepare( "INSERT INTO counters (name, hits) VALUES (:name, 1) ON DUPLICATE KEY UPDATE hits = hits + 1")->execute(['name' => 'homepage']);
Advanced PDO Concepts
Lesser-known PDO APIs and attributes for debugging, escaping, and multi-statement drivers.
- SAVEPOINT / RELEASE SAVEPOINT- SQL-level nested transactions; PDO has no native nested-transaction API, so savepoints emulate partial rollback inside one beginTransaction()
- debugDumpParams()- Dumps the parsed query plus bound parameter names, types, and values to stdout, invaluable for diagnosing binding bugs
- PDO::ATTR_PERSISTENT- Reuses a cached connection across requests under the same PHP process pool instead of reconnecting, at the cost of leaking transaction/session state if not reset
- quote()- Manually escapes and quotes a value for contexts placeholders can't reach, such as dynamic identifiers; never a substitute for prepared statements on data
- errorInfo() / errorCode()- Returns SQLSTATE diagnostics from the last operation; only useful when ATTR_ERRMODE is set to ERRMODE_SILENT or ERRMODE_WARNING
- PDOStatement::nextRowset()- Advances to the next result set returned by a multi-statement query or stored procedure call (driver-dependent, e.g. sqlsrv, mysql)
- rowCount()- Reliable for UPDATE/DELETE/INSERT affected-row counts, but not guaranteed accurate for SELECT on every driver — use COUNT(*) or fetchAll() length instead
- PDO::ATTR_EMULATE_PREPARES- Toggles client-side (emulated) vs server-side (native) prepared statement handling; native prepares preserve column types and enable server plan caching
Pro Tip
Always use prepared statements with bound parameters instead of string-interpolating values into SQL — it's the single most effective defense against SQL injection in PHP.
Was this cheat sheet helpful?
Explore Topics
#PHPPDODatabaseAccess#PHPPDODatabaseAccessCheatSheet#Programming#Intermediate#ConnectingWithPDO#PreparedStatements#Transactions#PDOConcepts#Databases#CheatSheet#SkillVeris
