CRUD Operations
CRUD — Create, Read, Update, Delete — describes the four basic operations almost every database-backed feature is built from. Whether it's a shopping cart, a blog, or a to-do list, the underlying database work is some combination of inserting rows, reading them back, changing them, and removing them. This page walks through all four against a single products table, using PDO with prepared statements throughout, plus two small but easy to forget details: reading back the id of a freshly inserted row, and checking how many rows an update or delete actually touched.
The examples below assume a products table shaped like this:
products table
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
in_stock TINYINT(1) NOT NULL DEFAULT 1
);Create — inserting a new row
An INSERT with prepared statements looks just like any other
parameterized query: placeholders for every value, followed by
->execute() with the actual values in order.
Inserting a product
<?php
$statement = $pdo->prepare(
'INSERT INTO products (name, price, in_stock) VALUES (?, ?, ?)'
);
$statement->execute(['Desk Lamp', 24.50, 1]);
$newId = $pdo->lastInsertId();
echo 'Created product with id ' . $newId;Created product with id 15
->lastInsertId() is called on the $pdo connection object itself,
not on the statement, and returns the auto-incremented id the
database just generated for the row you inserted. It's commonly
used right after an insert to immediately fetch, link to, or log the
new record without running a second lookup query.
Read — fetching one row or many
Reads follow the same prepare() / execute() / fetch pattern used
throughout this section. Use ->fetch() when you expect at most one
row back (for example, a lookup by primary key), and ->fetchAll()
when you expect a list.
Fetching a single product by id
<?php
$statement = $pdo->prepare('SELECT id, name, price, in_stock FROM products WHERE id = ?');
$statement->execute([$newId]);
$product = $statement->fetch(PDO::FETCH_ASSOC);
if ($product === false) {
echo 'No product found with that id.';
} else {
echo $product['name'] . ': $' . $product['price'];
}Desk Lamp: $24.50
Fetching every in-stock product
<?php
$statement = $pdo->prepare('SELECT id, name, price FROM products WHERE in_stock = ?');
$statement->execute([1]);
$products = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($products as $product) {
echo $product['name'] . ': $' . $product['price'] . PHP_EOL;
}Desk Lamp: $24.50 Wireless Mouse: $19.99 Mechanical Keyboard: $74.00
Update — changing existing rows
An UPDATE is another prepared statement, but the interesting part
is what happens after it runs: ->rowCount() on the statement tells
you how many rows the database actually changed, which is the only
reliable way to know whether the update did anything at all.
Updating a product's price
<?php
$statement = $pdo->prepare('UPDATE products SET price = ? WHERE id = ?');
$statement->execute([29.99, $newId]);
if ($statement->rowCount() === 0) {
echo 'No product was updated — check that the id exists.';
} else {
echo 'Updated ' . $statement->rowCount() . ' product(s).';
}Updated 1 product(s).
Delete — removing rows
DELETE follows the identical pattern, and ->rowCount() is just
as useful here for confirming that a row actually existed and was
removed.
Deleting a product
<?php
$statement = $pdo->prepare('DELETE FROM products WHERE id = ?');
$statement->execute([$newId]);
if ($statement->rowCount() === 0) {
echo 'No product found with that id — nothing was deleted.';
} else {
echo 'Deleted product ' . $newId . '.';
}Deleted product 15.
Putting it together as small reusable functions
In a real application, these four operations are usually wrapped in small functions (or methods on a repository-style class) rather than repeated inline everywhere they're needed.
A tiny products repository
<?php
function createProduct(PDO $pdo, string $name, float $price, bool $inStock): int
{
$statement = $pdo->prepare(
'INSERT INTO products (name, price, in_stock) VALUES (?, ?, ?)'
);
$statement->execute([$name, $price, $inStock ? 1 : 0]);
return (int) $pdo->lastInsertId();
}
function findProduct(PDO $pdo, int $id): ?array
{
$statement = $pdo->prepare('SELECT * FROM products WHERE id = ?');
$statement->execute([$id]);
$product = $statement->fetch(PDO::FETCH_ASSOC);
return $product === false ? null : $product;
}
function updateProductPrice(PDO $pdo, int $id, float $price): bool
{
$statement = $pdo->prepare('UPDATE products SET price = ? WHERE id = ?');
$statement->execute([$price, $id]);
return $statement->rowCount() > 0;
}
function deleteProduct(PDO $pdo, int $id): bool
{
$statement = $pdo->prepare('DELETE FROM products WHERE id = ?');
$statement->execute([$id]);
return $statement->rowCount() > 0;
}Create: prepare the
INSERT, execute with the values, then read$pdo->lastInsertId()if you need the new row's id.Read:
->fetch()for a single expected row,->fetchAll()for a list, always checking for afalse/empty result.Update: prepare the
UPDATE, execute with the values, then check->rowCount()to confirm a row actually changed.Delete: prepare the
DELETE, execute with the id, then check->rowCount()to confirm a row was actually removed.