PHP PDO — Database Access with Prepared Statements, Transactions, and Error Handling
In this tutorial, you will learn about PHP PDO. We cover key concepts, practical examples, and best practices to help you master this topic.
PHP PDO (PHP Data Objects) is a database access abstraction layer that provides a consistent interface for connecting to MySQL, PostgreSQL, SQLite, and other databases using prepared statements that prevent SQL injection attacks.
Why It Matters
Almost every PHP application stores and retrieves data from a database. PDO provides a secure, consistent way to interact with databases regardless of the vendor. Its prepared statement API prevents SQL injection, the most common database vulnerability. Understanding PDO means you can switch from MySQL to PostgreSQL without rewriting your database code.
Real-World Use
Laravel's Eloquent ORM uses PDO under the hood. WordPress uses a custom database class, but modern plugins use PDO with prepared statements. E-commerce platforms process transactions within PDO transactions for atomicity. DodaTech's user management system uses PDO with named parameters for all database queries, ensuring injection-safe interaction with PostgreSQL.
What You Will Learn
- Connecting to databases with PDO
- Writing prepared statements with positional and named parameters
- Fetching results: fetch, fetchAll, fetchColumn
- Insert, update, and delete operations
- Transactions with commit and rollback
- Error modes: silent, warning, exception
- Using different database drivers
Learning Path
flowchart LR A[Namespaces] --> B[PDO
You are here] B --> C[MVC Pattern] C --> D[DI Container] D --> E[Testing] style B fill:#f90,color:#fff
Connecting to a Database
PDO uses a DSN (Data Source Name) string to identify the database:
<?php
// MySQL
$dsn = 'mysql:host=localhost;dbname=app;charset=utf8mb4';
$user = 'app_user';
$password = 'secret123';
// PostgreSQL
$dsn = 'pgsql:host=localhost;dbname=app;port=5432';
// SQLite
$dsn = 'sqlite:' . __DIR__ . '/database.sqlite';
// With options
$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, $user, $password, $options);
echo "Connected successfully\n";
} catch (PDOException $e) {
die("Connection failed: " . $e->getMessage());
}
Connection Options Explained
| Option | Value | Description |
|---|---|---|
| ATTR_ERRMODE | ERRMODE_EXCEPTION | Throw exceptions on errors |
| ATTR_DEFAULT_FETCH_MODE | FETCH_ASSOC | Return associative arrays |
| ATTR_EMULATE_PREPARES | false | Use real prepared statements |
| ATTR_DEFAULT_FETCH_MODE | FETCH_OBJ | Return objects |
Prepared Statements
Prepared statements separate SQL logic from data, preventing SQL injection:
<?php
// Positional parameters (?)
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ? AND active = ?');
$stmt->execute(['alice@example.com', 1]);
// Named parameters (:param)
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND active = :active');
$stmt->execute([
':email' => 'alice@example.com',
':active' => 1,
]);
Why prepared statements prevent SQL injection: The SQL statement is sent to the database server first, without data. The database parses and compiles the query structure. Then the data is sent separately. Even if data contains malicious SQL, the database treats it as data, not as part of the query structure.
Fetching Results
Fetch One Row
<?php
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([1]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if ($user) {
echo $user['name'] . ' - ' . $user['email'];
} else {
echo 'User not found';
}
Fetch All Rows
<?php
$stmt = $pdo->query('SELECT id, name, email FROM users WHERE active = 1');
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
echo "{$user['id']}: {$user['name']} ({$user['email']})\n";
}
Fetch Single Column
<?php
$stmt = $pdo->query('SELECT email FROM users');
$emails = $stmt->fetchAll(PDO::FETCH_COLUMN);
// ['alice@example.com', 'bob@example.com', ...]
// Fetch single value
$stmt = $pdo->query('SELECT COUNT(*) FROM users');
$count = $stmt->fetchColumn();
echo "Total users: $count";
Fetch as Object
<?php
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([1]);
$user = $stmt->fetch(PDO::FETCH_OBJ);
echo $user->name;
echo $user->email;
Insert, Update, Delete
INSERT
<?php
$stmt = $pdo->prepare(
'INSERT INTO users (name, email, password, created_at)
VALUES (:name, :email, :password, NOW())'
);
$stmt->execute([
':name' => 'Alice Johnson',
':email' => 'alice@example.com',
':password' => password_hash('secret', PASSWORD_DEFAULT),
]);
$newId = $pdo->lastInsertId();
echo "Created user with ID: $newId";
UPDATE
<?php
$stmt = $pdo->prepare(
'UPDATE users SET name = :name, email = :email WHERE id = :id'
);
$stmt->execute([
':name' => 'Alice Smith',
':email' => 'alice.smith@example.com',
':id' => 1,
]);
$affected = $stmt->rowCount();
echo "Updated $affected row(s)";
DELETE
<?php
$stmt = $pdo->prepare('DELETE FROM users WHERE id = ?');
$stmt->execute([$userId]);
$count = $stmt->rowCount();
echo "Deleted $count user(s)";
Transactions
Transactions ensure multiple operations succeed or fail together:
<?php
try {
$pdo->beginTransaction();
// Deduct from sender
$stmt = $pdo->prepare(
'UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?'
);
$stmt->execute([100, 1, 100]);
if ($stmt->rowCount() === 0) {
throw new \RuntimeException('Insufficient funds');
}
// Add to recipient
$stmt = $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?');
$stmt->execute([100, 2]);
// Log the transaction
$stmt = $pdo->prepare(
'INSERT INTO transfers (from_account, to_account, amount) VALUES (?, ?, ?)'
);
$stmt->execute([1, 2, 100]);
$pdo->commit();
echo "Transfer successful\n";
} catch (\Throwable $e) {
$pdo->rollBack();
echo "Transfer failed: " . $e->getMessage() . "\n";
}
Error Modes
PDO has three error modes:
<?php
// Mode 1: Silent (default) -- returns false on error
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$stmt = $pdo->query('INVALID SQL'); // Returns false, no error output
// Mode 2: Warning -- emits E_WARNING
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$stmt = $pdo->query('INVALID SQL'); // Warning issued
// Mode 3: Exception -- throws PDOException
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->query('INVALID SQL'); // PDOException thrown
Always use ERRMODE_EXCEPTION in production. It forces you to handle errors with try-catch.
Database Configuration Pattern
Use a configuration class for database setup:
<?php
class Database {
private static ?PDO $instance = null;
public static function getInstance(): PDO {
if (self::$instance === null) {
$dbConfig = [
'driver' => $_ENV['DB_DRIVER'] ?? 'mysql',
'host' => $_ENV['DB_HOST'] ?? 'localhost',
'port' => $_ENV['DB_PORT'] ?? '3306',
'dbname' => $_ENV['DB_NAME'] ?? 'app',
'user' => $_ENV['DB_USER'] ?? 'root',
'password' => $_ENV['DB_PASSWORD'] ?? '',
'charset' => 'utf8mb4',
];
$dsn = sprintf(
'%s:host=%s;port=%s;dbname=%s;charset=%s',
$dbConfig['driver'],
$dbConfig['host'],
$dbConfig['port'],
$dbConfig['dbname'],
$dbConfig['charset']
);
self::$instance = new PDO($dsn, $dbConfig['user'], $dbConfig['password'], [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
}
return self::$instance;
}
public static function reset(): void {
self::$instance = null;
}
}
// Usage
$pdo = Database::getInstance();
$users = $pdo->query('SELECT * FROM users')->fetchAll();
Common Mistakes
1. String Interpolation in Queries
$sql = "SELECT * FROM users WHERE id = $_GET[id]"; // SQL injection!
Always use prepared statements. Never interpolate user input into SQL strings.
2. Not Checking rowCount for Updates
$stmt = $pdo->prepare('UPDATE users SET active = ? WHERE id = ?');
$stmt->execute([1, 999]); // No error, but row may not exist
Check $stmt->rowCount() to verify rows were affected.
3. Forgetting to Begin Transaction
$pdo->query('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
$pdo->query('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
// If the second query fails, the first is still committed
Always wrap related writes in beginTransaction/commit/rollback.
4. Using ERRMODE_SILENT
Errors return false without any notification. You must check return values manually. Use ERRMODE_EXCEPTION.
5. Not Setting Fetch Mode
$stmt = $pdo->query('SELECT ...');
$row = $stmt->fetch(); // Returns mixed array by default
Set DEFAULT_FETCH_MODE to FETCH_ASSOC or FETCH_OBJ for consistent results.
Practice Questions
Why do prepared statements prevent SQL injection? The SQL structure is sent and parsed before data is sent. The database treats data as literal values, not executable SQL.
What is the difference between positional (?) and named (:param) parameters? Positional parameters are ordered. Named parameters are explicit. Use named for readability when a query has many parameters.
What does PDO::lastInsertId() return? The auto-generated ID from the last INSERT statement. Call it immediately after execute.
What is a transaction and why use it? A transaction groups multiple operations into an atomic unit. Either all succeed (commit) or all fail (rollback). Critical for data integrity.
Challenge: Build a complete user management system with PDO including register (with hashed password), login verification, profile update, and list all users.
Mini Project: User Repository
Build a complete data access layer with PDO:
<?php
class UserRepository {
public function __construct(
private readonly PDO $pdo,
) {}
public function findById(int $id): ?array {
$stmt = $this->pdo->prepare(
'SELECT id, name, email, active, created_at FROM users WHERE id = ?'
);
$stmt->execute([$id]);
return $stmt->fetch() ?: null;
}
public function findByEmail(string $email): ?array {
$stmt = $this->pdo->prepare(
'SELECT * FROM users WHERE email = ?'
);
$stmt->execute([$email]);
return $stmt->fetch() ?: null;
}
public function create(array $data): int {
$stmt = $this->pdo->prepare(
'INSERT INTO users (name, email, password, active)
VALUES (:name, :email, :password, :active)'
);
$stmt->execute([
':name' => $data['name'],
':email' => $data['email'],
':password' => password_hash($data['password'], PASSWORD_DEFAULT),
':active' => $data['active'] ?? true,
]);
return (int)$this->pdo->lastInsertId();
}
public function update(int $id, array $data): bool {
$fields = [];
foreach ($data as $key => $value) {
$fields[] = "$key = :$key";
}
$fields[] = 'updated_at = NOW()';
$sql = 'UPDATE users SET ' . implode(', ', $fields) . ' WHERE id = :id';
$stmt = $this->pdo->prepare($sql);
$data['id'] = $id;
return $stmt->execute($data);
}
public function delete(int $id): bool {
$stmt = $this->pdo->prepare('DELETE FROM users WHERE id = ?');
return $stmt->execute([$id]);
}
public function findAll(int $page = 1, int $perPage = 20): array {
$offset = ($page - 1) * $perPage;
$stmt = $this->pdo->prepare(
'SELECT id, name, email, active FROM users
ORDER BY created_at DESC LIMIT ? OFFSET ?'
);
$stmt->execute([$perPage, $offset]);
return $stmt->fetchAll();
}
}
FAQ
What is Next
Now that you understand PDO, proceed to MVC Pattern to learn the architectual pattern behind Laravel and Symfony. Then read Dependency Injection to write testable, maintainable code.
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro