Connecting
$dsn = 'mysql:host=localhost;dbname=shop;charset=utf8mb4';
// PostgreSQL: 'pgsql:host=localhost;dbname=shop'
// SQLite: 'sqlite:' . __DIR__ . '/data.db'
$pdo = new PDO($dsn, 'user', 'password', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // throw on errors
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // rows as ['col' => value]
PDO::ATTR_EMULATE_PREPARES => false, // real prepared statements
]);Keep credentials out of the code (config file outside the web root or environment variables).
Prepared statements (always for user input)
// named placeholders
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND active = :active');
$stmt->execute(['email' => $email, 'active' => 1]);
// positional placeholders
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([$id]);Never build SQL by joining user input into the string – that is SQL injection. Placeholders only work for values; table or column names must come from a whitelist.
Fetching results
| Method | Returns |
|---|---|
$stmt->fetch() | next row or false |
$stmt->fetchAll() | all rows as array |
$stmt->fetchColumn() | first column of the next row |
$stmt->fetchAll(PDO::FETCH_COLUMN) | one column as flat list |
$stmt->fetchAll(PDO::FETCH_KEY_PAIR) | [col1 => col2] |
$stmt->fetchObject() | row as object |
$stmt->rowCount() | rows changed by INSERT/UPDATE/DELETE |
$user = $stmt->fetch(); // one row
if ($user === false) { /* not found */ }
$count = $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();
foreach ($pdo->query('SELECT id, name FROM users') as $row) { // no user input → query() is fine
echo $row['name'];
}Insert, update, delete
$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)');
$stmt->execute([$name, $email]);
$newId = $pdo->lastInsertId();
$stmt = $pdo->prepare('UPDATE users SET name = ? WHERE id = ?');
$stmt->execute([$name, $id]);
echo $stmt->rowCount() . ' row(s) updated';
$pdo->prepare('DELETE FROM users WHERE id = ?')->execute([$id]);
$pdo->exec('TRUNCATE TABLE log'); // exec(): for SQL without parameters, returns affected rowsIN lists
$ids = [3, 7, 9];
$in = implode(',', array_fill(0, count($ids), '?')); // "?,?,?"
$stmt = $pdo->prepare("SELECT * FROM users WHERE id IN ($in)");
$stmt->execute($ids);Transactions
try {
$pdo->beginTransaction();
$pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([50, 1]);
$pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([50, 2]);
$pdo->commit();
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}Handling errors
With ERRMODE_EXCEPTION, failures throw PDOException. $e->getCode() gives the SQLSTATE (e.g. 23000 = duplicate key / constraint). Log the message, but don't show it to visitors – see PHP Errors, JSON and Debugging.
Previous: PHP Forms, Sessions, Cookies and Security · Next: PHP Errors, JSON and Debugging
Put together with AI as a quick reference I keep for my own work. Entries like this are usually not reviewed in detail by a person.