Building a Simple PHP CRUD App

product-manager/
├── db.php                  PDO connection (outside the web root if you can)
├── functions.php           h(), validateProduct(), paginate(), sortClause(), csrf helpers
├── ProductRepository.php   all SQL
├── index.php               the list page
├── product-new.php         create
├── product-edit.php        edit
└── product-delete.php      delete (POST only)

<?php

declare(strict_types=1);

function h(string $text): string
{
    return htmlspecialchars($text, ENT_QUOTES, "UTF-8");
}

function csrfToken(): string
{
    $_SESSION["csrf_token"] ??= bin2hex(random_bytes(32));

    return $_SESSION["csrf_token"];
}

function csrfCheck(): void
{
    if (!hash_equals($_SESSION["csrf_token"] ?? "", (string) ($_POST["csrf_token"] ?? ""))) {
        http_response_code(403);
        exit("Invalid form token.");
    }
}

function paginate(int $total, int $page, int $perPage): array
{
    $pages = max(1, (int) ceil($total / $perPage));
    $page = min(max(1, $page), $pages);

    return ["page" => $page, "pages" => $pages, "offset" => ($page - 1) * $perPage];
}

function sortClause(string $column, string $direction): string
{
    $columns = ["name" => "name", "price" => "price", "stock" => "stock"];
    $directions = ["asc" => "ASC", "desc" => "DESC"];

    return "ORDER BY " . ($columns[$column] ?? "name") . " " . ($directions[strtolower($direction)] ?? "ASC") . ", id ASC";
}

function validateProduct(array $input): array
{
    $errors = [];

    $name = trim((string) ($input["name"] ?? ""));
    $price = filter_var($input["price"] ?? "", FILTER_VALIDATE_FLOAT);
    $stock = filter_var($input["stock"] ?? "", FILTER_VALIDATE_INT);
    $category = trim((string) ($input["category"] ?? ""));

    if ($name === "" || mb_strlen($name) > 150) {
        $errors["name"] = "Enter a name (up to 150 characters).";
    }
    if ($price === false || $price < 0) {
        $errors["price"] = "Enter a price of 0 or more.";
    }
    if ($stock === false || $stock < 0) {
        $errors["stock"] = "Enter a whole number of 0 or more.";
    }
    if ($category === "" || mb_strlen($category) > 50) {
        $errors["category"] = "Enter a category (up to 50 characters).";
    }

    return $errors;
}

<?php

declare(strict_types=1);

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

    /** @return array{rows: array, total: int} */
    public function list(string $search, string $category, string $order, int $limit, int $offset): array
    {
        $where = [];
        $params = [];

        if ($search !== "") {
            $where[] = "name LIKE :search ESCAPE '!'";
            $params["search"] = "%" . str_replace(["!", "%", "_"], ["!!", "!%", "!_"], $search) . "%";
        }

        if ($category !== "") {
            $where[] = "category = :category";
            $params["category"] = $category;
        }

        $whereSql = $where ? " WHERE " . implode(" AND ", $where) : "";

        $count = $this->pdo->prepare("SELECT COUNT(*) FROM products" . $whereSql);
        $count->execute($params);
        $total = (int) $count->fetchColumn();

        $statement = $this->pdo->prepare(
            "SELECT id, name, price, stock, category FROM products" . $whereSql . " " . $order . " LIMIT :limit OFFSET :offset"
        );
        foreach ($params as $key => $value) {
            $statement->bindValue(":" . $key, $value);
        }
        $statement->bindValue(":limit", $limit, PDO::PARAM_INT);
        $statement->bindValue(":offset", $offset, PDO::PARAM_INT);
        $statement->execute();

        return ["rows" => $statement->fetchAll(), "total" => $total];
    }

    public function categories(): array
    {
        return $this->pdo->query("SELECT DISTINCT category FROM products ORDER BY category")->fetchAll(PDO::FETCH_COLUMN);
    }

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

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

    public function create(string $name, float $price, int $stock, string $category): int
    {
        $this->pdo->prepare("INSERT INTO products (name, price, stock, category) VALUES (:name, :price, :stock, :category)")
            ->execute(["name" => $name, "price" => $price, "stock" => $stock, "category" => $category]);

        return (int) $this->pdo->lastInsertId();
    }

    public function update(int $id, string $name, float $price, int $stock, string $category): void
    {
        $this->pdo->prepare("UPDATE products SET name = :name, price = :price, stock = :stock, category = :category WHERE id = :id")
            ->execute(["name" => $name, "price" => $price, "stock" => $stock, "category" => $category, "id" => $id]);
    }

    public function delete(int $id): bool
    {
        $statement = $this->pdo->prepare("DELETE FROM products WHERE id = :id");
        $statement->execute(["id" => $id]);

        return $statement->rowCount() === 1;
    }
}

<?php
require __DIR__ . "/functions.php";
require __DIR__ . "/ProductRepository.php";

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

$perPage = 2;

// first call: find the total, so we can clamp the page
$first = $repo->list("", "", sortClause("price", "desc"), $perPage, 0);
$p = paginate($first["total"], 2, $perPage);

$result = $repo->list("", "", sortClause("price", "desc"), $perPage, $p["offset"]);

echo "Page {$p['page']} of {$p['pages']} (total {$result['total']})\n";
foreach ($result["rows"] as $row) {
    echo $row["name"] . "\n";
}

echo "---\n";

$writing = $repo->list("", "writing", sortClause("name", "asc"), 10, 0);
echo implode(", ", array_column($writing["rows"], "name")) . "\n";

$search = $repo->list("ar", "", sortClause("name", "asc"), 10, 0);
echo implode(", ", array_column($search["rows"], "name"));

Page 2 of 3 (total 5)
Notebook
Marker
---
Marker, Pen
Diary, Marker

<?php
declare(strict_types=1);

session_start();

require __DIR__ . "/functions.php";
require __DIR__ . "/ProductRepository.php";

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

$search = trim((string) ($_GET["q"] ?? ""));
$category = trim((string) ($_GET["category"] ?? ""));
$sort = (string) ($_GET["sort"] ?? "name");
$dir = (string) ($_GET["dir"] ?? "asc");
$perPage = 10;
$order = sortClause($sort, $dir);

$first = $repo->list($search, $category, $order, $perPage, 0);
$p = paginate($first["total"], (int) ($_GET["page"] ?? 1), $perPage);
$result = $p["offset"] === 0 ? $first : $repo->list($search, $category, $order, $perPage, $p["offset"]);

$flash = $_SESSION["flash"] ?? null;
unset($_SESSION["flash"]);
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Products</title>
</head>
<body>
  <h1>Products</h1>

  <?php if ($flash): ?><p role="status"><?= h($flash) ?></p><?php endif; ?>

  <form method="get" role="search">
    <label for="q">Search</label>
    <input type="search" id="q" name="q" value="<?= h($search) ?>">

    <label for="category">Category</label>
    <select id="category" name="category">
      <option value="">All</option>
      <?php foreach ($repo->categories() as $c): ?>
        <option value="<?= h($c) ?>" <?= $c === $category ? "selected" : "" ?>><?= h($c) ?></option>
      <?php endforeach; ?>
    </select>

    <button type="submit">Apply</button>
  </form>

  <p><a href="product-new.php">Add a product</a></p>

  <?php if ($result["rows"] === []): ?>
    <p>No products found.</p>
  <?php else: ?>
    <table>
      <thead>
        <tr>
          <th><a href="?<?= h(http_build_query([...$_GET, "sort" => "name", "dir" => $sort === "name" && $dir === "asc" ? "desc" : "asc"])) ?>">Name</a></th>
          <th><a href="?<?= h(http_build_query([...$_GET, "sort" => "price", "dir" => $sort === "price" && $dir === "asc" ? "desc" : "asc"])) ?>">Price</a></th>
          <th>Stock</th>
          <th><span class="visually-hidden">Actions</span></th>
        </tr>
      </thead>
      <tbody>
        <?php foreach ($result["rows"] as $row): ?>
          <tr>
            <td><?= h($row["name"]) ?></td>
            <td><?= number_format((float) $row["price"], 2) ?></td>
            <td><?= (int) $row["stock"] ?></td>
            <td>
              <a href="product-edit.php?id=<?= (int) $row["id"] ?>">Edit</a>
              <form method="post" action="product-delete.php" style="display:inline" onsubmit="return confirm('Delete this product?');">
                <input type="hidden" name="id" value="<?= (int) $row["id"] ?>">
                <input type="hidden" name="csrf_token" value="<?= h(csrfToken()) ?>">
                <button type="submit">Delete</button>
              </form>
            </td>
          </tr>
        <?php endforeach; ?>
      </tbody>
    </table>

    <nav aria-label="Pagination">
      <?php if ($p["page"] > 1): ?>
        <a href="?<?= h(http_build_query([...$_GET, "page" => $p["page"] - 1])) ?>">Previous</a>
      <?php endif; ?>
      Page <?= $p["page"] ?> of <?= $p["pages"] ?>
      <?php if ($p["page"] < $p["pages"]): ?>
        <a href="?<?= h(http_build_query([...$_GET, "page" => $p["page"] + 1])) ?>">Next</a>
      <?php endif; ?>
    </nav>
  <?php endif; ?>
</body>
</html>