PHP PDO — Complete Guide to Database Abstraction Layer
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) provides a database-agnostic interface for accessing multiple database systems with consistent API and secure prepared statements.
What You'll Learn
By the end of this tutorial, you'll connect to databases, use prepared statements, fetch data in various formats, handle errors, use transactions, and switch between MySQL, PostgreSQL, and SQLite seamlessly.
Why PDO Matters
Unlike MySQLi which only works with MySQL, PDO supports 12+ database drivers. You write the same code regardless of the database, making your application portable and future-proof.
Real-World Use
A SaaS application uses PDO to support both MySQL (customers) and SQLite (testing). Developers switch databases by changing one line in the config without touching query code.
PDO Learning Path
flowchart LR
A[MySQLi] --> B[PDO]
B --> C[MVC]
C --> D[REST API]
D --> E[JWT Auth]
A --> F{You Are Here}
style F fill:#f90,color:#fff
Connecting with PDO
<?php
// MySQL
$dsn = "mysql:host=localhost;dbname=myapp;charset=utf8mb4";
$user = "root";
$pass = "";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, $user, $pass, $options);
Multiple Database Support
<?php
// MySQL
$pdo = new PDO("mysql:host=localhost;dbname=myapp;charset=utf8mb4", "root", "");
// PostgreSQL
$pdo = new PDO("pgsql:host=localhost;dbname=myapp", "postgres", "");
// SQLite
$pdo = new PDO("sqlite:/path/to/database.sqlite");
// All use the same API after connection!
Prepared Statements
<?php
// INSERT
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->execute(["name" => "Alice", "email" => "alice@example.com"]);
$id = $pdo->lastInsertId();
// Positional placeholders
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ? AND active = ?");
$stmt->execute([1, true]);
$user = $stmt->fetch();
Fetch Modes
<?php
$stmt = $pdo->query("SELECT * FROM users");
// Fetch modes
$assoc = $stmt->fetch(PDO::FETCH_ASSOC); // Associative array
$obj = $stmt->fetch(PDO::FETCH_OBJ); // stdClass object
$num = $stmt->fetch(PDO::FETCH_NUM); // Indexed array
$all = $stmt->fetchAll(); // All rows
$column = $pdo->query("SELECT name FROM users")->fetchAll(PDO::FETCH_COLUMN);
Error Handling
<?php
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$pdo->exec("INSERT INTO users (name) VALUES ('Alice')");
} catch (PDOException $e) {
if ($e->getCode() === "23000") { // Integrity constraint violation
echo "Duplicate entry";
} else {
echo "Database error: " . $e->getMessage();
}
}
Transactions
<?php
try {
$pdo->beginTransaction();
$pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
$pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
$pdo->commit();
} catch (PDOException $e) {
$pdo->rollBack();
echo "Transaction failed: " . $e->getMessage();
}
Common Mistakes
1. Not Setting Error Mode to Exception
Default silent error mode hides failures. Always set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION.
2. Using emulated Prepares
Disable emulated prepares for real prepared statements: PDO::ATTR_EMULATE_PREPARES => false.
3. Fetching Without Checking
fetch() returns false when no more rows. Always check if ($row = $stmt->fetch()) before using.
4. Not Closing Cursors
PDO buffers query results. For large results, use $stmt->closeCursor() or fetch until exhausted.
5. Mixing Named and Positional Placeholders
Use either :name or ? consistently in a query. Mixing them causes errors.
Practice Questions
1. What is the main advantage of PDO over MySQLi?
PDO supports multiple database types with the same API. MySQLi is MySQL-only.
2. What are the three PDO error modes?
PDO::ERRMODE_SILENT (default, no error), ERRMODE_WARNING (E_WARNING), ERRMODE_EXCEPTION (throws PDOException).
3. How do you get the last inserted ID?
$pdo->lastInsertId() after an INSERT query within the same connection.
4. What is the difference between fetch() and fetchAll()?
fetch() returns one row (or false). fetchAll() returns all matching rows as an array.
5. Challenge: Create a database-agnostic query Builder using PDO.
<?php
class QueryBuilder {
private PDO $pdo;
public function __construct(PDO $pdo) { $this->pdo = $pdo; }
public function select(string $table, array $conditions = []): array {
$sql = "SELECT * FROM $table";
$params = [];
if (!empty($conditions)) {
$wheres = [];
foreach ($conditions as $col => $val) {
$wheres[] = "$col = ?";
$params[] = $val;
}
$sql .= " WHERE " . implode(" AND ", $wheres);
}
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
}
$qb = new QueryBuilder($pdo);
$users = $qb->select("users", ["active" => 1, "role" => "admin"]);
FAQ
Mini Project: Database Abstraction Layer
Build a reusable database abstraction layer with PDO supporting multiple backends.
<?php
class Database {
private PDO $pdo;
public function __construct(string $driver, string $host, string $dbname, string $user, string $pass) {
$dsn = match($driver) {
"mysql" => "mysql:host=$host;dbname=$dbname;charset=utf8mb4",
"pgsql" => "pgsql:host=$host;dbname=$dbname",
"sqlite" => "sqlite:$dbname",
default => throw new InvalidArgumentException("Unsupported driver")
};
$this->pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);
}
public function query(string $sql, array $params = []): array {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
public function execute(string $sql, array $params = []): int {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->rowCount();
}
}
$db = new Database("sqlite", "", "test.db", "", "");
$users = $db->query("SELECT * FROM users WHERE active = ?", [1]);
What's Next
PHP MVC Pattern PHP REST API PHP Testing PHPUnit
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro