Almost every web application is, at its heart, a set of CRUD screens: Create, Read, Update and Delete. A blog creates, lists, edits and removes posts. A shop does the same with products, a school with students, a to-do app with tasks. Once you can build one CRUD feature properly, you can build the core of most websites.
You already know the pieces: forms, validation and PDO with MySQL. This lesson puts them together into a small, complete products manager, the way a professional would structure it: a shared database connection, prepared statements everywhere, validated input, escaped output and a redirect after every change.
By the end you'll have a pattern you can copy for any table: a list page, a create form, an edit form, a delete button, and simple pagination for when the list grows long.
The plan
The app uses one table:
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(8,2) NOT NULL,
stock INT NOT NULL DEFAULT 0
);And a handful of files:
| File | Job | SQL |
|---|---|---|
db.php | Connects once, shared by every page | |
index.php | Lists products | SELECT |
create.php | Shows and handles the "new product" form | INSERT |
edit.php?id=3 | Shows and handles the edit form | SELECT, UPDATE |
delete.php | Handles the delete button | DELETE |
The rules every page follows
- Prepared statements for every query with user data, so SQL injection is impossible.
- Validate all input on the server, even if the form has HTML checks.
- Escape every value you print with
htmlspecialchars, so stored text can't inject HTML or scripts. - Change data only with POST, never with a link. Search engines and browser prefetching follow links, and a GET request that deletes would be triggered by accident.
- Redirect after a successful POST (the Post/Redirect/Get pattern), so refreshing the page doesn't submit the form twice.
Flash messages and pagination
After a redirect, how do you show "Product saved"? Store a flash message in the session, show it on the next page, then delete it.
When the table grows, show it a page at a time with LIMIT and OFFSET:
$perPage = 10;
$page = max(1, (int)($_GET['page'] ?? 1));
$offset = ($page - 1) * $perPage;
$stmt = $pdo->prepare('SELECT * FROM products ORDER BY name LIMIT :limit OFFSET :offset');
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);Count the total with SELECT COUNT(*) to work out how many pages there are.
PHP CRUD Syntax
CRUD queries with PDO
$pdo->query('SELECT id, name, price FROM products ORDER BY name')->fetchAll();
$stmt = $pdo->prepare('SELECT * FROM products WHERE id = ?');
$stmt->execute([$id]); $row = $stmt->fetch();
$pdo->prepare('INSERT INTO products (name, price, stock) VALUES (?, ?, ?)')
->execute([$name, $price, $stock]);
$pdo->prepare('UPDATE products SET name = ?, price = ? WHERE id = ?')
->execute([$name, $price, $id]);
$pdo->prepare('DELETE FROM products WHERE id = ?')->execute([$id]);
header('Location: index.php'); exit;PHP CRUD Summary
- CRUD means Create, Read, Update and Delete, the core of most web apps.
- Share one PDO connection, and use prepared statements for every query with user data.
- Validate input on the server and escape all output with htmlspecialchars.
- Change data only with POST forms, and redirect after success (Post/Redirect/Get).
- Use session flash messages for feedback and LIMIT/OFFSET for pagination.
PHP CRUD Examples
Each example shows the code, the output it produces and what each part does.
db.php and the list page
<?php
// db.php
$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', 'shop_app', 'secret', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
session_start();
function e(string $s): string { return htmlspecialchars($s, ENT_QUOTES, 'UTF-8'); }<?php
// index.php
require __DIR__ . '/db.php';
$products = $pdo->query('SELECT id, name, price, stock FROM products ORDER BY name')->fetchAll();
$flash = $_SESSION['flash'] ?? null;
unset($_SESSION['flash']);
?>
<?php if ($flash): ?><p class="notice"><?= e($flash) ?></p><?php endif; ?>
<a href="create.php">Add a product</a>
<table>
<?php foreach ($products as $p): ?>
<tr>
<td><?= e($p['name']) ?></td>
<td>£<?= number_format($p['price'], 2) ?></td>
<td><?= $p['stock'] > 0 ? (int)$p['stock'] : 'Out of stock' ?></td>
<td><a href="edit.php?id=<?= (int)$p['id'] ?>">Edit</a></td>
</tr>
<?php endforeach; ?>
</table>Product saved.
Add a product
Backpack £34.99 15 Edit
Lunch box £8.75 Out of stock Edit
Notebook £3.50 120 EditLine-by-line explanation
- Lines 3 to 6In db.php, exceptions make database errors visible, and rows come back as associative arrays.
- Line 7Every page that includes db.php can use sessions for flash messages.
- Line 8A tiny helper so escaping every value is quick: e($text).
- Lines 5 to 6In index.php, read the flash message once, then delete it so it doesn't appear again.
Creating with validation and a redirect
<?php
// create.php
require __DIR__ . '/db.php';
$errors = [];
$name = trim($_POST['name'] ?? '');
$price = $_POST['price'] ?? '';
$stock = $_POST['stock'] ?? '0';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if ($name === '' || mb_strlen($name) > 100) $errors['name'] = 'Enter a name up to 100 characters.';
if (filter_var($price, FILTER_VALIDATE_FLOAT) === false || $price < 0) $errors['price'] = 'Enter a valid price.';
if (filter_var($stock, FILTER_VALIDATE_INT, ['options' => ['min_range' => 0]]) === false) $errors['stock'] = 'Stock must be 0 or more.';
if (!$errors) {
$stmt = $pdo->prepare('INSERT INTO products (name, price, stock) VALUES (?, ?, ?)');
$stmt->execute([$name, $price, $stock]);
$_SESSION['flash'] = 'Product saved.';
header('Location: index.php');
exit;
}
}
?>
<form method="post">
<input name="name" value="<?= e($name) ?>"> <?= e($errors['name'] ?? '') ?>
<input name="price" value="<?= e($price) ?>"> <?= e($errors['price'] ?? '') ?>
<input name="stock" value="<?= e($stock) ?>"> <?= e($errors['stock'] ?? '') ?>
<button>Save</button>
</form>Submitting name "", price "abc", stock "5" shows the form again with:
Enter a name up to 100 characters.
Enter a valid price.
and stock keeps the value 5.
Submitting "Water bottle", "12", "40" saves the row and redirects
to index.php, which shows "Product saved."Line-by-line explanation
- Line 9The same file shows the form (GET) and handles it (POST).
- Lines 10 to 12Server-side validation, collecting errors in an array.
- Lines 14 to 16Only when there are no errors: a prepared INSERT.
- Lines 17 to 19Set a flash message, redirect, and stop the script with exit.
- Lines 24 to 26Each field is refilled with the user's input, escaped.
Editing and deleting
<?php
// edit.php
require __DIR__ . '/db.php';
$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
$stmt = $pdo->prepare('SELECT * FROM products WHERE id = ?');
$stmt->execute([$id]);
$product = $stmt->fetch();
if (!$product) {
http_response_code(404);
exit('Product not found');
}
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
// validate as in create.php, then:
$pdo->prepare('UPDATE products SET name = ?, price = ?, stock = ? WHERE id = ?')
->execute([trim($_POST['name']), $_POST['price'], $_POST['stock'], $id]);
$_SESSION['flash'] = 'Product updated.';
header('Location: index.php');
exit;
}
?>
<form method="post"><!-- fields filled from $product --></form>
<form method="post" action="delete.php" onsubmit="return confirm('Delete this product?')">
<input type="hidden" name="id" value="<?= (int)$product['id'] ?>">
<button>Delete</button>
</form>edit.php?id=3 shows the form filled with the backpack's details
edit.php?id=999 shows "Product not found" with a 404 status
Pressing Delete sends a POST to delete.php, which runs
DELETE FROM products WHERE id = ? and redirects back.Line-by-line explanation
- Line 4filter_input turns the id into an integer, or false if it isn't one.
- Lines 8 to 11A missing product gets a proper 404 instead of an error.
- Lines 15 to 16The UPDATE uses a WHERE on the id, so only this row changes.
- Lines 24 to 27Deleting uses a POST form with the id in a hidden field, never a plain link. A real app also adds a CSRF token, as in the security lesson.
Common PHP CRUD Mistakes
Deleting with a link
<a href="delete.php?id=3">Delete</a> <!-- a crawler or prefetch can delete data --><form method="post" action="delete.php">
<input type="hidden" name="id" value="3"><button>Delete</button>
</form>Not redirecting after POST
$stmt->execute([...]);
echo "Saved!"; // refreshing the page inserts the product again$stmt->execute([...]);
header('Location: index.php');
exit;Printing database values unescaped
<td><?= $p['name'] ?></td> <!-- a name like <script> runs in the browser --><td><?= htmlspecialchars($p['name'], ENT_QUOTES, 'UTF-8') ?></td>PHP CRUD in Real Projects
CRUD in real projects
Admin panels, content management systems, booking systems and internal business tools are mostly CRUD screens. Frameworks like Laravel generate much of this for you with resource controllers and form request validation, but the same steps happen underneath: validate, run a prepared query, redirect with a flash message. Understanding the plain PHP version makes the framework version far less magical.
PHP CRUD FAQ
Should I delete records or mark them as deleted?
For many business records, such as orders and customers, a "soft delete" is safer: add a deleted_at column, set it instead of deleting the row, and leave those rows out of normal queries. It allows undo and keeps history. Truly delete data when it's no longer needed or when privacy rules require it.
How do I stop two people overwriting each other's edits?
Use optimistic locking: add a version column, include its value in the edit form, and update with WHERE id = ? AND version = ? while increasing the version. If the update changes no rows, someone else saved first, so reload the record and ask the user to review the changes.
How do I make sure a value, such as a product code, is unique?
Add a UNIQUE index in the database, then catch the duplicate-key error when inserting or updating, SQLSTATE 23000 in MySQL, and show a friendly message. Checking with a SELECT first isn't enough on its own, because two requests can pass the check at the same moment.
Is OFFSET pagination good enough?
For small and medium tables, yes. With very large tables, high page numbers become slow, because the database still reads all the skipped rows. Keyset pagination, WHERE id > ? ORDER BY id LIMIT 20, stays fast, as the pagination and search lesson explains.
Should I use a framework to build CRUD screens?
Building CRUD by hand once is the best way to understand what happens. For real projects, frameworks such as Laravel and Symfony provide routing, validation, CSRF protection, database tools and form handling, which saves time and avoids missing a protection on one page.
PHP CRUD Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Complete the line that sends the browser back to the list after saving.
('Location: index.php');
exit;What does CRUD stand for?
Why redirect after a successful POST?
Why should deleting use a POST form instead of a link?
With 10 products per page, what OFFSET does page 3 use?
PHP CRUD Quiz
5 questions to check what you learned.
Which SQL statement does the edit page run when the form is saved?
- INSERT
- UPDATE
- SELECT
- DELETE
What is a flash message?
- A pop-up advert
- An error in the log
- A cookie that never expires
- A message stored in the session, shown once on the next page, then removed
Why escape database values when printing them?
- To make them shorter
- Because MySQL requires it
- So stored text containing HTML or scripts is shown as text, not run
- To translate them
What should the edit page do if the id doesn't exist?
- Show a blank form
- Return a 404 "not found" response
- Create a new product
- Redirect to Google
Why call exit right after header('Location: ...')?
- header only sends a header; without exit the rest of the script keeps running
- To close the database
- exit saves the session
- It's needed for HTTPS