Project: CRUD App with MySQL

JA JASWANT Updated 04 Nov 2026

9 min read ·Lesson 236 of 238

What You Will Build

An admin area for managing the shop's products: a searchable, paginated product list, forms to Create and Update products, product Reading, and safe Deletion — the four basic operations of almost every business application. The project combines PDO, prepared statements, validation, sticky forms, CSRF protection, pagination, and error handling into a clean structure that separates database access, validation, and pages.

Features

Feature	Technique	Lesson
All database access in one class	Repository pattern with PDO	PDO Prepared Statements
Validated, normalized input	Pure validation function	PHP Form Validation
Search and pagination	Escaped `LIKE`, `LIMIT`/`OFFSET`	MySQL Where, Pagination with PHP
Unique SKUs	`UNIQUE` index + error 1062	PDO Error Handling
Protected deletes	POST + CSRF + foreign key error 1451	MySQL Delete Data
One form for create and edit	Shared template	Sticky Forms

Project Structure

crud-app/
├── public/products/
│   ├── index.php        ← list, search, pagination
│   ├── create.php       ← new product
│   ├── edit.php         ← edit product (?id=)
│   └── delete.php       ← POST only
├── src/
│   ├── bootstrap.php    ← session, e(), csrf, flash(), pdo(), paginate()
│   ├── ProductRepository.php
│   └── product-validation.php
└── templates/
    └── product-form.php

The project uses the products table from MySQL Create Table. In a real application, these pages would also call requireLogin() from Project: Login & Registration System and check that the user is an administrator.

Step 1: Validation

<?php
// src/product-validation.php
const PRODUCT_CATEGORIES = ['stationery', 'electronics', 'bags', 'general'];
function validateProduct(array $input): array
{
    $get = fn(string $key): string => is_string($input[$key] ?? null) ? trim($input[$key]) : '';
    $data = [
        'sku' => strtoupper($get('sku')),
        'name' => preg_replace('/\s+/u', ' ', $get('name')),
        'price' => $get('price'),
        'stock' => $get('stock'),
        'category' => $get('category'),
        'is_active' => isset($input['is_active']),
    ];
    $errors = [];
    if (!preg_match('/^[A-Z0-9-]{3,30}$/', $data['sku'])) {
        $errors['sku'] = 'Use 3–30 letters, digits, or hyphens.';
    }
    if (mb_strlen($data['name']) < 2 || mb_strlen($data['name']) > 150) {
        $errors['name'] = 'Enter a name between 2 and 150 characters.';
    }
    if (!preg_match('/^\d{1,8}(\.\d{1,2})?$/', $data['price'])) {
        $errors['price'] = 'Enter a price such as 99 or 99.50.';
    } else {
        $data['price'] = number_format((float) $data['price'], 2, '.', '');
    }
    $stock = filter_var($data['stock'], FILTER_VALIDATE_INT, ['options' => ['min_range' => 0, 'max_range' => 1_000_000]]);
    if ($stock === false) {
        $errors['stock'] = 'Enter a whole number from 0 to 1,000,000.';
    } else {
        $data['stock'] = $stock;
    }
    if (!in_array($data['category'], PRODUCT_CATEGORIES, true)) {
        $errors['category'] = 'Choose a category.';
    }
    return ['data' => $data, 'errors' => $errors];
}

Testing the Validation

<?php
require __DIR__ . '/src/product-validation.php';
$invalid = validateProduct(['sku' => 'pen 01', 'name' => 'P', 'price' => '12.345', 'stock' => '-3', 'category' => 'toys']);
print_r($invalid['errors']);
$valid = validateProduct(['sku' => ' nb-a6 ', 'name' => 'Notebook  A6', 'price' => '45.5', 'stock' => '30', 'category' => 'stationery', 'is_active' => '1']);
echo json_encode($valid['data']) . "\n";

Output

Array
(
    [sku] => Use 3–30 letters, digits, or hyphens.
    [name] => Enter a name between 2 and 150 characters.
    [price] => Enter a price such as 99 or 99.50.
    [stock] => Enter a whole number from 0 to 1,000,000.
    [category] => Choose a category.
)
{"sku":"NB-A6","name":"Notebook A6","price":"45.50","stock":30,"category":"stationery","is_active":true}

The function normalizes values (uppercase SKU, collapsed spaces, price with exactly two decimals, integer stock) so the database always receives consistent data. Prices are validated as decimal strings with a regular expression, never parsed as floats for storage (see PHP Floats).

Step 2: The Repository

<?php
// src/ProductRepository.php
final class ProductInUseException extends RuntimeException {}
final class DuplicateSkuException extends RuntimeException {}
final class ProductRepository
{
    private const COLUMNS = 'id, sku, name, price, stock, category, is_active, updated_at';
    public function __construct(private PDO $pdo) {}
    public function search(string $term, int $limit, int $offset): array
    {
        [$where, $params] = $this->searchCondition($term);
        $stmt = $this->pdo->prepare('SELECT ' . self::COLUMNS . " FROM products $where ORDER BY name, id LIMIT :limit OFFSET :offset");
        foreach ($params as $name => $value) {
            $stmt->bindValue($name, $value);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }
    public function count(string $term): int
    {
        [$where, $params] = $this->searchCondition($term);
        $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM products $where");
        $stmt->execute($params);
        return (int) $stmt->fetchColumn();
    }
    public function find(int $id): ?array
    {
        $stmt = $this->pdo->prepare('SELECT ' . self::COLUMNS . ' FROM products WHERE id = ?');
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }
    public function create(array $data): int
    {
        $this->write(
            'INSERT INTO products (sku, name, price, stock, category, is_active)
             VALUES (:sku, :name, :price, :stock, :category, :is_active)',
            $data
        );
        return (int) $this->pdo->lastInsertId();
    }
    public function update(int $id, array $data): void
    {
        $this->write(
            'UPDATE products
             SET sku = :sku, name = :name, price = :price, stock = :stock, category = :category, is_active = :is_active
             WHERE id = :id',
            $data + ['id' => $id]
        );
    }
    public function delete(int $id): bool
    {
        try {
            $stmt = $this->pdo->prepare('DELETE FROM products WHERE id = ?');
            $stmt->execute([$id]);
            return $stmt->rowCount() === 1;
        } catch (PDOException $e) {
            if (($e->errorInfo[1] ?? null) === 1451) {
                throw new ProductInUseException('The product appears in orders.', 0, $e);
            }
            throw $e;
        }
    }
    private function write(string $sql, array $data): void
    {
        $data['is_active'] = (int) $data['is_active'];
        try {
            $this->pdo->prepare($sql)->execute($data);
        } catch (PDOException $e) {
            if (($e->errorInfo[1] ?? null) === 1062) {
                throw new DuplicateSkuException('SKU already exists.', 0, $e);
            }
            throw $e;
        }
    }
    private function searchCondition(string $term): array
    {
        $term = trim($term);
        if ($term === '') {
            return ['', []];
        }
        $pattern = '%' . addcslashes($term, '%_\\') . '%';
        return ['WHERE name LIKE :term1 OR sku LIKE :term2', [':term1' => $pattern, ':term2' => $pattern]];
    }
}

Why a Repository?

Every SQL statement for products lives in one class — easy to review, test, and change.
Pages call meaningful methods (find(), update()) instead of writing SQL.
Database error codes are translated into domain exceptions (DuplicateSkuException, ProductInUseException), so pages never inspect MySQL error numbers (see PDO Error Handling).
Search terms are escaped for LIKE and passed as parameters; LIMIT/OFFSET are bound as integers.

Step 3: The List Page

<?php
// public/products/index.php
require __DIR__ . '/../../src/bootstrap.php';
require __DIR__ . '/../../src/ProductRepository.php';
$products = new ProductRepository(pdo());
$term = is_string($_GET['q'] ?? null) ? trim($_GET['q']) : '';
$requestedPage = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT) ?: 1;
$p = paginate($products->count($term), 10, $requestedPage);
$rows = $products->search($term, $p['per_page'], $p['offset']);
$message = flash();
?>
<h1>Products</h1>
<?php if ($message !== null): ?><p class="info"><?= e($message) ?></p><?php endif; ?>
<form method="get">
    <input type="search" name="q" value="<?= e($term) ?>" placeholder="Search name or SKU">
    <button type="submit">Search</button>
    <a href="create.php">+ New product</a>
</form>
<p>Showing <?= $p['from'] ?>–<?= $p['to'] ?> of <?= $p['total_items'] ?></p>
<table>
    <tr><th>SKU</th><th>Name</th><th>Price</th><th>Stock</th><th>Status</th><th></th></tr>
    <?php foreach ($rows as $row): ?>
        <tr>
            <td><?= e($row['sku']) ?></td>
            <td><?= e($row['name']) ?></td>
            <td>₹<?= e(number_format((float) $row['price'], 2)) ?></td>
            <td><?= (int) $row['stock'] ?></td>
            <td><?= $row['is_active'] ? 'Active' : 'Hidden' ?></td>
            <td>
                <a href="edit.php?id=<?= (int) $row['id'] ?>">Edit</a>
                <form method="post" action="delete.php" onsubmit="return confirm('Delete this product?');">
                    <input type="hidden" name="_csrf" value="<?= e(csrfToken()) ?>">
                    <input type="hidden" name="id" value="<?= (int) $row['id'] ?>">
                    <button type="submit">Delete</button>
                </form>
            </td>
        </tr>
    <?php endforeach; ?>
</table>
<?= paginationLinks($p, ['q' => $term]) ?>

paginate() and paginationLinks() are the functions from Pagination with PHP, placed in bootstrap.php. Search terms are kept in the pagination links.

Step 4: Create and Edit With One Form

`templates/product-form.php`

<form method="post">
    <input type="hidden" name="_csrf" value="<?= e(csrfToken()) ?>">
    <?php foreach (['sku' => 'SKU', 'name' => 'Name', 'price' => 'Price (₹)', 'stock' => 'Stock'] as $field => $label): ?>
        <label><?= e($label) ?>
            <input name="<?= $field ?>" value="<?= e((string) $data[$field]) ?>" required>
        </label>
        <?php if (isset($errors[$field])): ?><p class="error"><?= e($errors[$field]) ?></p><?php endif; ?>
    <?php endforeach; ?>
    <label>Category
        <select name="category">
            <?php foreach (PRODUCT_CATEGORIES as $category): ?>
                <option value="<?= e($category) ?>" <?= $data['category'] === $category ? 'selected' : '' ?>><?= e(ucfirst($category)) ?></option>
            <?php endforeach; ?>
        </select>
    </label>
    <?php if (isset($errors['category'])): ?><p class="error"><?= e($errors['category']) ?></p><?php endif; ?>
    <label><input type="checkbox" name="is_active" value="1" <?= $data['is_active'] ? 'checked' : '' ?>> Visible in the shop</label>
    <button type="submit"><?= e($submitLabel) ?></button>
    <a href="index.php">Cancel</a>
</form>

`public/products/edit.php`

<?php
require __DIR__ . '/../../src/bootstrap.php';
require __DIR__ . '/../../src/ProductRepository.php';
require __DIR__ . '/../../src/product-validation.php';
$products = new ProductRepository(pdo());
$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
$product = $id ? $products->find($id) : null;
if ($product === null) {
    http_response_code(404);
    exit('Product not found.');
}
$data = $product;
$errors = [];
if (($_SERVER['REQUEST_METHOD'] ?? 'GET') === 'POST') {
    if (!csrfValid($_POST['_csrf'] ?? null)) {
        $errors['form'] = 'Your session expired. Please try again.';
    } else {
        ['data' => $data, 'errors' => $errors] = validateProduct($_POST);
        if ($errors === []) {
            try {
                $products->update($id, $data);
                flash("Product {$data['sku']} was updated.");
                header('Location: index.php', true, 303);
                exit;
            } catch (DuplicateSkuException) {
                $errors['sku'] = 'This SKU is already used by another product.';
            }
        }
    }
}
$submitLabel = 'Save changes';
?>
<h1>Edit <?= e($product['name']) ?></h1>
<?php if (isset($errors['form'])): ?><p class="error"><?= e($errors['form']) ?></p><?php endif; ?>
<?php require __DIR__ . '/../../templates/product-form.php'; ?>

create.php is identical except that it starts with empty defaults (['sku' => '', 'name' => '', 'price' => '', 'stock' => '0', 'category' => 'general', 'is_active' => true]), calls $products->create($data), and uses the label "Create product". The form is shared, so both pages behave consistently.

Step 5: Deleting Safely

<?php
// public/products/delete.php
require __DIR__ . '/../../src/bootstrap.php';
require __DIR__ . '/../../src/ProductRepository.php';
if (($_SERVER['REQUEST_METHOD'] ?? 'GET') !== 'POST' || !csrfValid($_POST['_csrf'] ?? null)) {
    http_response_code(405);
    exit('Invalid request.');
}
$id = filter_input(INPUT_POST, 'id', FILTER_VALIDATE_INT);
try {
    $deleted = $id && (new ProductRepository(pdo()))->delete($id);
    flash($deleted ? 'The product was deleted.' : 'The product no longer exists.');
} catch (ProductInUseException) {
    flash('This product has been ordered and cannot be deleted. Hide it instead by unchecking "Visible in the shop".');
}
header('Location: index.php', true, 303);
exit;

Deletion requires POST and a CSRF token. The foreign key from order_items prevents deleting products that appear in orders; the user receives a helpful alternative instead of a database error.

Testing the Project

Test	Expected Result
Create a product with SKU `pen 01`	Error under SKU; other values kept
Create a product with an existing SKU	"This SKU is already used…"
Search for `lamp`	Only matching products; the term stays in the box and in page links
Search for `%`	No unexpected matches (wildcard escaped)
Edit a product and change the price to `99.5`	Saved as `99.50`
Delete a product that appears in an order	Flash message suggesting to hide it
Open `edit.php?id=99999`	404 "Product not found."
Send a delete request without the CSRF token	"Invalid request."

Common Mistakes

SQL Scattered Across Pages

Effect: Duplicated, inconsistent queries that are hard to secure and change. Fix: Centralize data access in a repository.

Trusting Hidden Fields for IDs Without Checks

Effect: Users edit or delete records they should not access. Fix: Check permissions for the ID on the server (ownership or admin role).

Deleting With GET Links

Effect: Crawlers or forged links delete data. Fix: Use POST forms with CSRF tokens and confirmations.

Storing Prices as Floats

Effect: Rounding errors. Fix: Validate decimal strings and store them in DECIMAL columns.

Best Practices

Separate validation, data access, and presentation.
Use prepared statements everywhere, with integer binding for LIMIT/OFFSET.
Translate database constraint errors into domain exceptions and friendly messages.
Share one form template between create and edit pages.
Protect every state change with POST, CSRF tokens, and authorization checks. Official reference: https://www.php.net/manual/en/pdo.prepared-statements.php

More in PHP

See all

Post image PHP

Project: REST API with PHP

What You Will Build A JSON REST API for the shop's products, usable by a mobile app, a JavaScript front…

JA JASWANT 04 Nov 2026

Post image PHP

Project: Shopping Cart with Sessions

What You Will Build A shopping cart for the shop: visitors add products, change quantities, remove item…

JA JASWANT 04 Nov 2026

Post image PHP

Project: Login & Registration System

What You Will Build A complete user account system: visitors register with name, email, and password; r…

JA JASWANT 04 Nov 2026

Post image PHP

Project: Contact Form with Email

What You Will Build A production-quality contact form for a website: visitors enter their name, email, …

JA JASWANT 04 Nov 2026

0 Comments

Reviewed before they appear

No comments yet.