PDO Database Basics
PDO (PHP Data Objects) is a database access abstraction layer built into PHP. Rather than using database-specific extensions like the old mysql_* or mysqli_* functions, PDO gives you a single, consistent object-oriented API that works across MySQL, PostgreSQL, SQLite, SQL Server, and other supported drivers. Switching database engines mostly means changing the connection string (the DSN) rather than rewriting query code, and PDO provides first-class support for prepared statements, transactions, and configurable error handling.
Cricket analogy: PDO is like a universal umpiring signal system usable across Test, ODI, and T20 instead of format-specific hand signals, so switching formats mostly means changing the rulebook reference, not relearning every signal.
Connecting with a DSN
A PDO connection is created by instantiating the PDO class with a Data Source Name (DSN) string identifying the driver and connection details, plus optional username, password, and an options array. The DSN format varies by driver: mysql:host=localhost;dbname=app;charset=utf8mb4 for MySQL, or sqlite:/path/to/db.sqlite for SQLite. Setting the charset in the DSN itself (not via a separate query) avoids a historical class of encoding-related SQL injection issues in older MySQL drivers.
Cricket analogy: Setting up a match connection needs specifics like 'Eden Gardens, day-night, pink ball' versus 'Lord's, red ball' specified upfront, and locking the ball type in the pre-match agreement avoids disputes that plagued older, looser arrangements.
<?php
declare(strict_types=1);
$dsn = 'mysql:host=127.0.0.1;dbname=app;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, 'app_user', 'secret', $options);
} catch (PDOException $e) {
throw new RuntimeException('Database connection failed', previous: $e);
}
$stmt = $pdo->prepare('SELECT id, email, created_at FROM users WHERE active = :active');
$stmt->execute(['active' => 1]);
foreach ($stmt as $row) {
printf("%d: %s (%s)\n", $row['id'], $row['email'], $row['created_at']);
}Error handling modes
PDO supports three error-reporting modes via PDO::ATTR_ERRMODE: ERRMODE_SILENT (the historical default, where you must manually check errorCode() after every call), ERRMODE_WARNING (emits an E_WARNING), and ERRMODE_EXCEPTION, which throws a PDOException on failure. Nearly all modern PHP code should set ERRMODE_EXCEPTION explicitly, since it lets database errors propagate naturally through your normal exception-handling flow instead of requiring manual checks scattered everywhere.
Cricket analogy: A team could ignore a failed review silently and just grumble about it, announce it over the PA as a warning, or formally lodge it with the match referee for automatic escalation — professional teams always choose the formal escalation path.
PDO::ATTR_EMULATE_PREPARES defaults to true for some drivers, meaning PDO builds the final SQL string client-side rather than sending the query and parameters separately to the server. Setting it to false makes PDO use genuine server-side prepared statements, which is both slightly more secure against certain edge-case injection vectors and gives more accurate type handling from the driver.
Fetching results
After executing a SELECT statement, you retrieve rows with fetch() (one row at a time) or fetchAll() (every row as an array). The fetch mode — controlled by PDO::FETCH_ASSOC, FETCH_OBJ, FETCH_CLASS, and others — determines the shape of each row: an associative array, a stdClass object, or an instance of a class you specify. A PDOStatement is also directly iterable in a foreach loop, which reads rows lazily without loading the entire result set into memory at once, unlike fetchAll().
Cricket analogy: Reading a scorecard one ball at a time as it happens is like fetch(), while waiting for the full match summary at the end is like fetchAll(), and the shape you receive, a plain scoresheet, a player object, or a specific stats-class, mirrors PDO's fetch modes.
fetchAll() loads the entire result set into PHP's memory at once. For large tables or unbounded queries, iterate the statement directly with foreach or use fetch() in a loop instead, to avoid exhausting memory on large result sets.
Transactions
PDO exposes beginTransaction(), commit(), and rollBack() to group multiple statements into a single atomic unit of work. Wrapping related writes — for example, debiting one account and crediting another — in a transaction ensures that either all statements succeed or none do, which is essential for maintaining data integrity in multi-step operations.
Cricket analogy: A DRS review process is atomic — the on-field call, ball-tracking data, and final decision all commit together, or if the system fails mid-review, everything rolls back to the original on-field call rather than a half-applied decision.
- PDO provides one consistent API across many database engines via driver-specific DSN strings.
- Always set PDO::ATTR_ERRMODE to ERRMODE_EXCEPTION so failures surface as catchable PDOExceptions.
- Disable PDO::ATTR_EMULATE_PREPARES to use genuine server-side prepared statements.
- fetch() and iterating a PDOStatement stream rows; fetchAll() loads everything into memory at once.
- Wrap related multi-statement writes in beginTransaction()/commit()/rollBack() for atomicity.
- Set the charset in the DSN itself rather than via a follow-up query.
Practice what you learned
1. What does the DSN string passed to the PDO constructor specify?
2. Which PDO error mode causes database errors to be thrown as catchable exceptions?
3. What is the main difference between fetchAll() and iterating a PDOStatement directly with foreach?
4. What does setting PDO::ATTR_EMULATE_PREPARES to false accomplish?
5. Which set of methods lets you group multiple SQL statements into an atomic unit of work in PDO?
Was this page helpful?
You May Also Like
Prepared Statements and SQL Injection
Understand how SQL injection attacks work and how parameterized prepared statements in PDO eliminate them by separating SQL structure from user data.
PHP Security Basics
Core security practices every PHP developer must apply — input validation, output escaping, safe database access, and secure session handling.
Working with JSON in PHP
Learn how to encode PHP data to JSON and decode JSON back into PHP values using json_encode and json_decode, plus common pitfalls and error handling.
Exceptions and Error Handling
Learn how PHP represents runtime failures as throwable objects, how try/catch/finally blocks control the flow around them, and how to design a clean exception hierarchy for your application.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics