PDO Best Practices

<?php
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

<?php
function orderBy(string $column, string $direction): string
{
    $columns = ["name", "price", "stock"];
    $directions = ["ASC", "DESC"];

    $column = in_array($column, $columns, true) ? $column : "name";
    $direction = in_array(strtoupper($direction), $directions, true) ? strtoupper($direction) : "ASC";

    return "ORDER BY $column $direction";
}

echo orderBy("price", "desc") . "\n";
echo orderBy("1; DROP TABLE x", "sideways");

ORDER BY price DESC
ORDER BY name ASC

<?php
$pdo = require __DIR__ . "/db.php";

$ids = [1, 3, 5];

$placeholders = implode(",", array_fill(0, count($ids), "?"));

$statement = $pdo->prepare("SELECT name FROM products WHERE id IN ($placeholders) ORDER BY id");
$statement->execute($ids);

echo implode(", ", $statement->fetchAll(PDO::FETCH_COLUMN));

Notebook, Bag, Diary

<?php
// Slow: one query per post (the "N+1" problem)
foreach ($posts as $post) {
    $author = $pdo->query("SELECT name FROM users WHERE id = " . (int) $post["user_id"])->fetch();
}

// Better: one query with a JOIN
$posts = $pdo->query("SELECT p.title, u.name AS author FROM posts p JOIN users u ON u.id = p.user_id")->fetchAll();

CREATE INDEX idx_products_category ON products (category);

<?php
final class ProductRepository
{
    public function __construct(private PDO $pdo) {}

    public function find(int $id): ?array
    {
        $statement = $this->pdo->prepare("SELECT id, name, stock FROM products WHERE id = :id");
        $statement->execute(["id" => $id]);

        return $statement->fetch() ?: null;
    }
}

$repo = new ProductRepository(require __DIR__ . "/db.php");

echo $repo->find(2)["name"] . " / ";
var_dump($repo->find(999));

Pen / NULL