PHP CRUD with PDO

CREATE TABLE users (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE,
    role        ENUM('admin', 'editor', 'user') DEFAULT 'user',
    active      TINYINT(1) DEFAULT 1,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

<?php
$stmt = $pdo->prepare(
    "INSERT INTO users (name, email, role) VALUES (:name, :email, :role)"
);

$stmt->execute([
    ":name"  => $data["name"],
    ":email" => $data["email"],
    ":role"  => $data["role"] ?? "user",
]);

$id = (int) $pdo->lastInsertId();
echo "Created user #$id";

<?php
// Single row by id
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);
$user = $stmt->fetch();
if ($user === false) {
    http_response_code(404);
    die("User not found");
}

// All rows
$users = $pdo->query("SELECT * FROM users ORDER BY name")->fetchAll();

// Filter
$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE role = :role AND active = 1"
);
$stmt->execute([":role" => "editor"]);
$editors = $stmt->fetchAll();

// Count
$total = (int) $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();

<?php
$stmt = $pdo->prepare(
    "UPDATE users
        SET name = :name, email = :email
      WHERE id = :id"
);

$stmt->execute([
    ":id"    => $id,
    ":name"  => $data["name"],
    ":email" => $data["email"],
]);

echo "Updated " . $stmt->rowCount() . " row(s)";

// Dynamic update - only changed fields
$allowed = ["name", "email", "role"];
$updates = [];
$params  = [":id" => $id];
foreach ($allowed as $field) {
    if (array_key_exists($field, $data)) {
        $updates[] = "$field = :$field";
        $params[":$field"] = $data[$field];
    }
}
if ($updates) {
    $sql = "UPDATE users SET " . implode(", ", $updates) . " WHERE id = :id";
    $pdo->prepare($sql)->execute($params);
}

<?php
// Hard delete
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([$id]);

if ($stmt->rowCount() === 0) {
    http_response_code(404);
    echo "User not found";
}

// Soft delete - safer for production
$pdo->prepare("UPDATE users SET deleted_at = NOW() WHERE id = ?")
    ->execute([$id]);

<?php
$perPage = 20;
$page    = max(1, (int) ($_GET["page"] ?? 1));
$offset  = ($page - 1) * $perPage;

// Page of users (cast to int - LIMIT does not accept strings)
$stmt = $pdo->prepare(
    "SELECT * FROM users
      ORDER BY id DESC
      LIMIT :limit OFFSET :offset"
);
$stmt->bindValue(":limit",  $perPage, PDO::PARAM_INT);
$stmt->bindValue(":offset", $offset,  PDO::PARAM_INT);
$stmt->execute();
$users = $stmt->fetchAll();

// Total count for "Page X of Y"
$total      = (int) $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
$totalPages = (int) ceil($total / $perPage);

<?php
$q = trim($_GET["q"] ?? "");

// Escape LIKE wildcards in user input
$like = "%" . str_replace(["%", "_"], ["\%", "\_"], $q) . "%";

$stmt = $pdo->prepare(
    "SELECT id, name, email FROM users
      WHERE name LIKE :q OR email LIKE :q
      ORDER BY name LIMIT 50"
);
$stmt->execute([":q" => $like]);
$results = $stmt->fetchAll();

<?php
class UserRepository {
    public function __construct(private PDO $db) {}

    public function find(int $id): ?array {
        $s = $this->db->prepare("SELECT * FROM users WHERE id = ?");
        $s->execute([$id]);
        return $s->fetch() ?: null;
    }

    public function create(array $data): int {
        $s = $this->db->prepare(
            "INSERT INTO users (name, email) VALUES (:name, :email)"
        );
        $s->execute([
            ":name"  => $data["name"],
            ":email" => $data["email"],
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function update(int $id, array $data): bool {
        $s = $this->db->prepare(
            "UPDATE users SET name = :name WHERE id = :id"
        );
        return $s->execute([":id" => $id, ":name" => $data["name"]]);
    }

    public function delete(int $id): bool {
        $s = $this->db->prepare("DELETE FROM users WHERE id = ?");
        return $s->execute([$id]);
    }
}

// Usage
$repo = new UserRepository($pdo);
$user = $repo->find(42);