PHP PDO Database

PDO (PHP Data Objects) is the standard, driver-agnostic way to access databases in PHP. It supports MySQL, PostgreSQL, SQLite, and more.

1 - Connecting

$pdo = new PDO(
    dsn:      "mysql:host=localhost;dbname=myapp;charset=utf8mb4",
    username: "root",
    password: "",
    options:  [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]
);

2 - SELECT

// One row
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(["email" => $email]);
$user = $stmt->fetch(); // or null

// All rows
$stmt = $pdo->prepare("SELECT * FROM posts ORDER BY created_at DESC LIMIT :limit");
$stmt->bindValue("limit", 10, PDO::PARAM_INT);
$stmt->execute();
$posts = $stmt->fetchAll();

3 - INSERT / UPDATE / DELETE

// INSERT
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->execute(["name" => "Alice", "email" => "[email protected]"]);
$id = (int) $pdo->lastInsertId();

// UPDATE
$pdo->prepare("UPDATE users SET name = ? WHERE id = ?")->execute(["Bob", 1]);

// DELETE
$pdo->prepare("DELETE FROM users WHERE id = ?")->execute([1]);

4 - Transactions

try {
    $pdo->beginTransaction();
    $pdo->prepare("UPDATE wallets SET balance = balance - ? WHERE user_id = ?")->execute([100, 1]);
    $pdo->prepare("UPDATE wallets SET balance = balance + ? WHERE user_id = ?")->execute([100, 2]);
    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
    throw $e;
}