Databases fail for many reasons: the server is down, the SQL has a typo, a unique rule is broken, a table is missing. Your code must handle failures in a way that is safe and useful.
Turn on exceptions
Set this option when you connect:
<?php
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);Now every database problem throws a PDOException. Without it, PDO may only return false and keep going.
Catch, log, and show a safe message
<?php
$pdo = require __DIR__ . "/db.php";
try {
$pdo->query("SELECT * FROM table_that_does_not_exist");
} catch (PDOException $e) {
error_log("DB error: " . $e->getMessage()); // full detail goes to the log
echo "Sorry, something went wrong. Please try again later."; // the visitor sees this
}Sorry, something went wrong. Please try again later.The message from the database often contains table names, column names and parts of the SQL. Never show it to visitors. See Production Error Handling.
What a message looks like
For example (MySQL; the wording differs by version):
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'shop.nothing' doesn't exist
SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES)
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'ana@example.com' for key 'users.email'The first part, SQLSTATE[...], is a standard five-character code. $e->getCode() returns it.
React to a specific problem
A duplicate value is an expected situation, so handle it with a friendly message.
<?php
try {
$statement->execute(["name" => $name, "email" => $email]);
} catch (PDOException $e) {
if ($e->getCode() === "23000") {
$errors["email"] = "That email is already registered.";
} else {
throw $e; // an unexpected problem: let the global handler deal with it
}
}Rethrow anything you do not understand.
Transactions: all or nothing
When several changes belong together, put them in a transaction. For example, a bank transfer takes money from one account and adds it to another. If the second step fails, the first must be undone.
<?php
$pdo = require __DIR__ . "/db.php";
function buy(PDO $pdo, int $productId, int $qty): bool
{
$pdo->beginTransaction();
try {
$update = $pdo->prepare("UPDATE products SET stock = stock - :qty WHERE id = :id AND stock >= :needed");
$update->execute(["qty" => $qty, "id" => $productId, "needed" => $qty]);
if ($update->rowCount() !== 1) {
throw new RuntimeException("Not enough stock");
}
$pdo->prepare("INSERT INTO notes (title) VALUES (?)")->execute(["Sold $qty of product $productId"]);
$pdo->commit();
return true;
} catch (Throwable $e) {
$pdo->rollBack();
return false;
}
}
var_dump(buy($pdo, 1, 5)); // 25 in stock: works
var_dump(buy($pdo, 3, 1)); // 0 in stock: fails
echo $pdo->query("SELECT COUNT(*) FROM notes")->fetchColumn() . " note(s) written";bool(true)
bool(false)
1 note(s) writtenThe failed purchase wrote nothing, because rollBack() undid it. Only the successful one left a note.
InnoDB needed
Transactions work with the InnoDB storage engine (the default in modern MySQL). The old MyISAM engine ignores them.
Deadlocks and retries
On busy systems, two transactions can block each other, and MySQL cancels one with a “Deadlock found” error. A retry usually works. Frameworks often retry a few times.
Errors in SQL itself
A typo in the SQL (SELCT) throws an exception on every run, so you find it while developing. Read the message: MySQL tells you which part it did not understand.
Log with context
Include the query name or the user id in the log line, so you can trace a problem later. Never log passwords or full personal data.