PHP MySQL CRUD with PDO, Transactions, and Validation

CRUD Transaction Boundary

A PHP CRUD endpoint should validate input, use PDO prepared statements, enforce authorization, and return a deliberate response after each create, read, update, or delete operation. The database schema remains the final authority for uniqueness, nullability, and relationships.

Create and Read

Insert a User

<?php
$insert = $pdo->prepare(
    'INSERT INTO users (name, email) VALUES (:name, :email)'
);
$insert->execute([
    'name' => $name,
    'email' => $email,
]);

$userId = (int) $pdo->lastInsertId();

Read Recent Users

<?php
$select = $pdo->prepare(
    'SELECT id, name, email FROM users ORDER BY id DESC LIMIT :limit'
);
$select->bindValue('limit', 10, PDO::PARAM_INT);
$select->execute();
$users = $select->fetchAll();

Update and Delete

Update One Email

<?php
$update = $pdo->prepare(
    'UPDATE users SET email = :email WHERE id = :id'
);
$update->execute(['email' => $email, 'id' => $userId]);

if ($update->rowCount() === 0) {
    // The row was missing or the value was unchanged.
}

Delete One Record

<?php
$delete = $pdo->prepare('DELETE FROM users WHERE id = :id');
$delete->execute(['id' => $userId]);

$deleted = $delete->rowCount() === 1;

Transaction Boundary

Create an Order and Item

<?php
$pdo->beginTransaction();

try {
    $order->execute(['user_id' => $userId]);
    $orderId = (int) $pdo->lastInsertId();
    $item->execute(['order_id' => $orderId, 'product_id' => $productId]);
    $pdo->commit();
} catch (Throwable $exception) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $exception;
}

CRUD Failure Signals

Validate required values before executing SQL.
Handle unique-constraint failures as conflicts, not generic success.
Do not assume rowCount() distinguishes unchanged from missing on every driver without testing.
Authorize the operation before selecting or changing a record.
Paginate list queries instead of returning an unbounded table.

Safe Writes

Group dependent writes in one transaction and commit only after every statement succeeds. Roll back in a catch block, log a server-side correlation ID, and show the user a controlled message. Do not hold a transaction open while waiting for another HTTP service or browser action.

For updates and deletes, include the record identity in a bound parameter and verify affected rows. A zero count can mean the record was absent or the submitted values already matched; define that behavior in the application instead of reporting success blindly.

Conflicts and Concurrency

A prepared statement prevents SQL injection in values; it does not validate business rules or authorize a record. Check ownership in the same operation, and let UNIQUE, FOREIGN KEY, CHECK, and NOT NULL constraints protect races that application checks cannot prevent.

For an editable record, include a version number or previous updated_at value in the UPDATE predicate. If no row changes, reload and report a conflict instead of silently overwriting another user's edit. Retry deadlocks only when the whole transaction is safe to repeat.

Map duplicate-key failures to a controlled conflict response.
Return a not-found result without revealing records the user cannot access.
Use keyset or offset pagination with a deterministic ORDER BY.
Keep generated identifiers and audit fields server controlled.

CRUD Safety Check

0 of 2 checked

Q1. Where should request values appear in SQL?

As prepared parameters Concatenated into the query Inside the table name

Q2. When is a transaction required?

When related writes must succeed together For every echo Only when reading one constant

CRUD Boundary Failures

Prepared SQL treated as authorization
Check the current user can act on the selected record.
Lost update
Use optimistic version checking or an appropriate transaction lock.
Constraint error exposed raw
Log diagnostics and return a stable application error.

Try this next

Complete a Safe Record Flow

0 of 2 completed

Create and redisplay
Validate a user, insert it, redirect, and fetch only the columns required by the confirmation page.
Protect an update
Require both record ownership and a prepared id before updating an email.

Previous Next