PHP PDO
PDO is PHP's database-agnostic API. One interface, the same code shape on MySQL, PostgreSQL, SQLite, SQL Server. Modern apps default to it.
Why PDO
- One API for many databases — port from MySQL to Postgres by changing the DSN.
- Prepared statements with both named and positional placeholders.
- Errors as exceptions — fits modern PHP.
- Built-in fetch modes for hydrating arrays and objects.
Connection (DSNs)
| Database | DSN |
|---|---|
| MySQL | 'mysql:host=localhost;dbname=shop;charset=utf8mb4' |
| PostgreSQL | 'pgsql:host=localhost;dbname=shop' |
| SQLite (file) | 'sqlite:/path/to/db.sqlite' |
| SQLite (memory) | 'sqlite::memory:' |
| SQL Server | 'sqlsrv:Server=localhost;Database=shop' |
Full quick-start
PHP
$pdo = new PDO('sqlite::memory:', null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$pdo->exec('CREATE TABLE customers (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)');
$stmt = $pdo->prepare('INSERT INTO customers (name) VALUES (?)');
foreach (['Ada', 'Grace', 'Linus'] as $name) {
$stmt->execute([$name]);
}
foreach ($pdo->query('SELECT * FROM customers') as $row) {
print_r($row);
}
Common methods
| Method | Use for |
|---|---|
$pdo->query($sql) | One-off statements; returns PDOStatement. |
$pdo->exec($sql) | Statements that don't return rows; returns row count. |
$pdo->prepare($sql) | With placeholders; reusable. |
$pdo->lastInsertId() | ID from the last INSERT. |
$pdo->beginTransaction() / commit() / rollBack() | Transactions. |
$stmt->fetch() / fetchAll() / fetchColumn() / fetchObject() | Reading. |
$stmt->rowCount() | Rows affected (after exec / non-select stmt). |
Errors
With ERRMODE_EXCEPTION, failed queries throw PDOException. Catch them at the right boundary — usually a controller or middleware.
Tip: For real apps, build a thin repository layer (or use an ORM) over PDO. Spreading raw SQL across controllers is fine for tutorials, painful at scale.
Example
Example
<?php
try {
$pdo = new PDO('sqlite::memory:', null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$pdo->exec('CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec("INSERT INTO t (name) VALUES ('Ada'), ('Linus')");
foreach ($pdo->query('SELECT * FROM t') as $row) {
print_r($row);
}
} catch (PDOException $e) {
echo $e->getMessage();
}
Try it Yourself »
Exercise
Error mode constant to throw exceptions.
PDO::
snake_case; 17 chars.
Discussion
Loading…