PDO, PHP Data Objects, is the built-in database layer that works the same way whether the database is MySQL, PostgreSQL or SQLite. Its important feature is prepared statements: you write the SQL with placeholders and pass the values separately, which makes SQL injection impossible by construction. This is the complete working vocabulary — connect, query safely, read results, handle errors, use transactions — with code you can paste.
Connecting#
<?php
$dsn = 'mysql:host=localhost;dbname=shop;charset=utf8mb4';
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // throw on error, never fail silently
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // rows as associative arrays
PDO::ATTR_EMULATE_PREPARES => false, // real prepared statements
];
try {
$pdo = new PDO($dsn, 'shop_user', getenv('DB_PASSWORD'), $options);
} catch (PDOException $e) {
error_log('Database connection failed: ' . $e->getMessage());
exit('Service unavailable');
}
Those three options are not optional in practice. Without exception mode, failed queries return false and the code carries on; without the fetch mode, every fetch call needs it repeated; without disabling emulation, PDO builds the query in PHP instead of letting the database prepare it, which loses type safety.
The DSN changes per database and nothing else does:
'pgsql:host=localhost;dbname=shop'
'sqlite:/var/data/shop.db'
'sqlite::memory:'
Prepared statements#
Never build SQL by joining strings with user input. This is the vulnerability that has leaked more databases than any other:
// Vulnerable: the input becomes part of the SQL
$rows = $pdo->query("SELECT * FROM users WHERE email = '$email'"); // never do this
// Safe: SQL and data travel separately
$stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);
$user = $stmt->fetch();
The database receives the query shape first and the values second, so a value cannot change the query’s meaning whatever it contains. There are two placeholder styles; pick one and be consistent:
// Named
$stmt = $pdo->prepare('SELECT * FROM orders WHERE user_id = :id AND status = :status');
$stmt->execute(['id' => $userId, 'status' => 'paid']);
// Positional
$stmt = $pdo->prepare('SELECT * FROM orders WHERE user_id = ? AND status = ?');
$stmt->execute([$userId, 'paid']);
Fetching#
// One row, or false if none
$stmt = $pdo->prepare('SELECT * FROM products WHERE id = ?');
$stmt->execute([$id]);
$product = $stmt->fetch();
if ($product === false) { /* not found */ }
// All rows as an array of arrays
$stmt = $pdo->query('SELECT id, name FROM categories ORDER BY name');
$categories = $stmt->fetchAll();
// A single value
$stmt = $pdo->prepare('SELECT COUNT(*) FROM orders WHERE user_id = ?');
$stmt->execute([$userId]);
$count = $stmt->fetchColumn();
// One column as a flat list
$ids = $pdo->query('SELECT id FROM products')->fetchAll(PDO::FETCH_COLUMN);
// Keyed by the first column: [id => name]
$names = $pdo->query('SELECT id, name FROM products')->fetchAll(PDO::FETCH_KEY_PAIR);
// Into objects of a class
$stmt = $pdo->query('SELECT * FROM products');
$products = $stmt->fetchAll(PDO::FETCH_CLASS, Product::class);
For large result sets, do not fetchAll; loop and let PDO stream:
$stmt = $pdo->query('SELECT * FROM log_entries');
while ($row = $stmt->fetch()) {
process($row); // one row in memory at a time
}
query() is fine when there are no parameters at all. The moment a value comes from anywhere outside the code, use prepare.
Writing#
$stmt = $pdo->prepare('INSERT INTO products (name, price) VALUES (:name, :price)');
$stmt->execute(['name' => 'Notebook', 'price' => 4.50]);
$newId = (int) $pdo->lastInsertId();
$stmt = $pdo->prepare('UPDATE products SET price = ? WHERE id = ?');
$stmt->execute([5.00, $newId]);
$changed = $stmt->rowCount(); // rows affected
$stmt = $pdo->prepare('DELETE FROM products WHERE id = ?');
$stmt->execute([$newId]);
A prepared statement can be executed many times with different values, which is both faster and tidier for bulk inserts:
$stmt = $pdo->prepare('INSERT INTO tags (name) VALUES (?)');
foreach ($tags as $tag) {
$stmt->execute([$tag]);
}
Transactions#
When several changes must succeed or fail together — debit one account, credit another — wrap them:
$pdo->beginTransaction();
try {
$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->commit();
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}
Because the connection is in exception mode, any failure lands in the catch, the rollback undoes the partial change, and the exception continues upward to be logged.
Error handling#
Catch PDOException where you can do something useful — usually at the request boundary, to log and return a generic error. Do not catch it around every query; let it propagate. Never show the exception message to users; it contains table and column names.
try {
$result = $repository->findOrders($userId);
} catch (PDOException $e) {
error_log($e->getMessage()); // full detail to the log
http_response_code(500);
echo 'Something went wrong.'; // nothing useful to an attacker
}
Types#
With emulation off, PDO sends strings by default. Integers usually convert fine on the database side, but LIMIT ? on MySQL needs a real integer:
$stmt = $pdo->prepare('SELECT * FROM products LIMIT :n');
$stmt->bindValue(':n', $perPage, PDO::PARAM_INT);
$stmt->execute();
Booleans and nulls likewise: PDO::PARAM_BOOL, PDO::PARAM_NULL. For everything else, passing the array to execute is enough.
Questions people ask#
PDO or mysqli?
PDO. It works with every database, and prepared statements with named placeholders are cleaner. mysqli is fine but MySQL-only.
Do prepared statements slow queries down?
Preparing costs a round trip; executing a prepared statement is as fast as a plain query and faster when repeated. For web applications the difference is negligible and the safety is not.
Why does fetch() return false rather than null?
Historical API design. Check with === false; a row that is an empty array is still a row.
Should I use an ORM instead?
For larger applications, an ORM or query builder such as Doctrine or Laravel’s Eloquent saves a great deal of code. They all use PDO underneath, so this knowledge still applies when you need to drop down to raw SQL.
Where to go next#
- PHP and databases — schema design and the bigger picture.
- PHP security best practices — injection is one of several.
- Handling errors and exceptions in PHP — where PDOException fits.
