Databases with PDO · PHP

PDO (PHP Data Objects) is PHP's standard way to talk to a database. The same code works with MySQL, PostgreSQL, SQLite and others; only the connection string changes.

Connecting

PHP

<?php
$pdo = new PDO(
    "mysql:host=localhost;dbname=shop;charset=utf8mb4",   // the DSN: which database, where
    "shop_user",
    "secret-password",
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,       // throw an exception on errors
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,  // rows as associative arrays
    ]
);

Database	DSN
MySQL or MariaDB	`mysql:host=localhost;dbname=shop;charset=utf8mb4`
PostgreSQL	`pgsql:host=localhost;dbname=shop`
SQLite (a file)	`sqlite:/path/to/shop.db`
SQLite (in memory)	`sqlite::memory:`

Keep real passwords out of your code: read them from environment variables or a config file that isn't committed to version control.

Why prepared statements matter

This runs without a database, because it only builds the SQL text:

PHP

<?php
$name = "x' OR '1'='1";      // what an attacker might type into a login form

$unsafe = "SELECT * FROM users WHERE name = '" . $name . "'";
echo $unsafe, "\n";

Pasting input into SQL lets the input become SQL. Here the condition '1'='1' is always true, so this query would return every user. That's SQL injection, one of the most damaging and most common attacks on web applications. A prepared statement sends the SQL and the values separately, so a value can never change the query's meaning.

Querying with prepared statements

PHP

<?php
$stmt = $pdo->prepare("SELECT id, name, price FROM products WHERE category = ? AND price < ?");
$stmt->execute(["Accessories", 50]);
$products = $stmt->fetchAll();          // an array of rows

foreach ($products as $p) {
    echo "{$p['name']}: {$p['price']}\n";
}

// named placeholders read better when there are several values
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(["email" => $email]);
$user = $stmt->fetch();                 // one row, or false if there is none
if ($user === false) {
    echo "No such user\n";
}

prepare sends the SQL with placeholders: ? or :name.
execute sends the values, which the database treats purely as data.
fetchAll returns every row; fetch returns the next row, or false when there are no more.

A query with no user input at all can use $pdo->query("SELECT COUNT(*) FROM products") directly. Anything containing a value from outside must be prepared.

Inserting, updating and deleting

PHP

<?php
$stmt = $pdo->prepare("INSERT INTO customers (name, email) VALUES (:name, :email)");
$stmt->execute(["name" => "Zara Ali", "email" => "zara@example.com"]);
$newId = $pdo->lastInsertId();          // the id the database assigned

$stmt = $pdo->prepare("UPDATE products SET price = price * 0.9 WHERE category = ?");
$stmt->execute(["Office"]);
echo $stmt->rowCount(), " products discounted\n";

$stmt = $pdo->prepare("DELETE FROM sessions WHERE expires_at < ?");
$stmt->execute([date("Y-m-d H:i:s")]);

Transactions

PHP

<?php
try {
    $pdo->beginTransaction();

    $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?")->execute([500, 1]);
    $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?")->execute([500, 2]);

    $pdo->commit();                     // both changes, together
} catch (Throwable $e) {
    $pdo->rollBack();                   // neither change
    throw $e;
}

A transfer must never take money from one account without adding it to the other. Between beginTransaction and commit, either every change happens or, after rollBack, none of them does. The SQL lessons cover transactions in depth.

A complete example

PHP

<?php
$pdo = new PDO("sqlite::memory:", options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$pdo->exec("CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER DEFAULT 0)");

$add = $pdo->prepare("INSERT INTO tasks (title) VALUES (?)");
foreach (["Learn PDO", "Use prepared statements", "Ship the app"] as $title) {
    $add->execute([$title]);
}
$pdo->prepare("UPDATE tasks SET done = 1 WHERE id = ?")->execute([1]);

foreach ($pdo->query("SELECT * FROM tasks ORDER BY id", PDO::FETCH_ASSOC) as $task) {
    echo ($task["done"] ? "[x] " : "[ ] "), $task["title"], "\n";
}

Saved as tasks.php, this runs anywhere PHP and its SQLite driver are installed (php tasks.php), and prints the three tasks with the first one ticked. A prepared statement can be executed many times with different values, as the loop does.