Log in Sign up

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

MethodReturns
$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 rows

IN 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.