Session 5.6 – CRUD Operations in PHP

Introduction to CRUD

CRUD (Create, Read, Update, Delete) represents the four basic operations for persistent storage. These operations form the foundation of most database-driven applications.

Key Insight

Every interaction a user can have with stored data maps to exactly one of the four CRUD operations. A registration form is a Create; browsing a product catalogue is a Read; editing a profile is an Update; cancelling an account is a Delete. Understanding this mapping helps you plan the HTTP routes, SQL statements, and PHP handlers required for any feature.

CRUD and HTTP Methods

In modern RESTful web development, CRUD operations are mapped to standard HTTP methods:

CRUD	SQL	HTTP Method	Typical URL
Create	`INSERT INTO`	POST	`/users`
Read (list)	`SELECT`	GET	`/users`
Read (single)	`SELECT … WHERE id=?`	GET	`/users/42`
Update	`UPDATE … SET`	PUT / PATCH	`/users/42`
Delete	`DELETE FROM`	DELETE	`/users/42`

Traditional PHP applications with HTML forms use only GET and POST because browsers do not send PUT/DELETE natively. REST APIs (covered later) use all four HTTP methods.

CRUD Operations Overview

CREATE

INSERT INTO

READ

SELECT FROM

UPDATE

UPDATE SET

DELETE

DELETE FROM

Database Schema

For this session, we'll use a simple users table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE Operation

The Post/Redirect/Get (PRG) Pattern

A naive PHP form handler that inserts a row and then renders a success page has a critical usability flaw: if the user refreshes the browser, the browser re-submits the POST request and inserts a duplicate row.

The Post/Redirect/Get (PRG) pattern solves this:

User submits the form (POST).
The handler processes the data and sends a 302 Location redirect header.
The browser follows the redirect with a GET request.
The user sees the result page. Refreshing only re-issues the GET — harmless.

Server-Side Validation Is Mandatory

HTML attributes such as required and type="email" only prevent accidental mistakes — they can be bypassed trivially with browser developer tools or a raw HTTP request. Always re-validate every field in PHP using functions like empty(), filter_var(), and preg_match().

Create Form (create.php)

<!DOCTYPE html>
<html>
<head>
    <title>Add New User</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-5">
        <h2>Add New User</h2>

        <?php if (isset($_GET['success'])): ?>
            <div class="alert alert-success">User created successfully!</div>
        <?php endif; ?>

        <?php if (isset($_GET['error'])): ?>
            <div class="alert alert-danger"><?php echo htmlspecialchars($_GET['error']); ?></div>
        <?php endif; ?>

        <form action="store.php" method="POST">
            <div class="mb-3">
                <label class="form-label">Name</label>
                <input type="text" name="name" class="form-control" required>
            </div>

            <div class="mb-3">
                <label class="form-label">Email</label>
                <input type="email" name="email" class="form-control" required>
            </div>

            <div class="mb-3">
                <label class="form-label">Phone</label>
                <input type="tel" name="phone" class="form-control">
            </div>

            <button type="submit" class="btn btn-primary">Create User</button>
            <a href="index.php" class="btn btn-secondary">Cancel</a>
        </form>
    </div>
</body>
</html>

Passing Error Messages in the URL

The examples below pass short error messages via the query string (e.g. ?error=Email+already+exists). Always call urlencode() before embedding any message in a URL, and always call htmlspecialchars() when printing it back to the browser — otherwise an attacker can inject JavaScript by crafting a malicious URL (reflected XSS).

Store Handler (store.php)

<?php
// store.php
require_once 'config.php';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    try {
        // Get form data
        $name = trim($_POST['name']);
        $email = trim($_POST['email']);
        $phone = trim($_POST['phone']);

        // Validate input
        $errors = [];

        if (empty($name)) {
            $errors[] = "Name is required";
        }

        if (empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
            $errors[] = "Valid email is required";
        }

        if (!empty($errors)) {
            $errorMsg = implode(', ', $errors);
            header("Location: create.php?error=" . urlencode($errorMsg));
            exit;
        }

        // Connect to database
        $pdo = new PDO("mysql:host=localhost;dbname=mydb", "root", "");
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // Insert data
        $sql = "INSERT INTO users (name, email, phone) VALUES (:name, :email, :phone)";
        $stmt = $pdo->prepare($sql);

        $stmt->execute([
            ':name' => $name,
            ':email' => $email,
            ':phone' => $phone
        ]);

        // Redirect with success message
        header("Location: index.php?success=created");
        exit;

    } catch (PDOException $e) {
        // Check for duplicate email
        if ($e->getCode() == 23000) {
            header("Location: create.php?error=Email already exists");
        } else {
            header("Location: create.php?error=Database error occurred");
        }
        exit;
    }
} else {
    header("Location: create.php");
    exit;
}
?>

READ Operation

Pagination Theory

Fetching all rows from a large table with SELECT * FROM users consumes excessive memory in PHP and transfers unnecessary data across the network. Pagination limits each query to a window of rows using LIMIT offset, count:

Page size ($perPage) — How many rows per page. Typical values: 10, 20, 50.
Offset — ($page - 1) × $perPage. For page 1: offset 0; for page 2: offset 10 (if perPage=10).
Total pages — ceil(totalRows / perPage). Requires a separate SELECT COUNT(*) query.

Search and LIKE Wildcards

The SQL LIKE operator matches patterns using two wildcards: % (any sequence of characters) and _ (any single character). To search for a term anywhere in a column you wrap it: %searchTerm%. Because % and _ are meaningful characters, you must escape them if users could input them literally — use PDO::quote() or add a custom escape step for the search value itself, while still passing it through a prepared-statement placeholder.

List All Users (index.php)

<?php
// index.php
require_once 'config.php';

try {
    $pdo = new PDO("mysql:host=localhost;dbname=mydb", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Pagination
    $page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
    $perPage = 10;
    $offset = ($page - 1) * $perPage;

    // Search functionality
    $search = isset($_GET['search']) ? trim($_GET['search']) : '';

    if (!empty($search)) {
        $sql = "SELECT * FROM users WHERE name LIKE :search OR email LIKE :search
                ORDER BY id DESC LIMIT :offset, :perPage";
        $stmt = $pdo->prepare($sql);
        $stmt->bindValue(':search', "%$search%", PDO::PARAM_STR);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->bindValue(':perPage', $perPage, PDO::PARAM_INT);
    } else {
        $sql = "SELECT * FROM users ORDER BY id DESC LIMIT :offset, :perPage";
        $stmt = $pdo->prepare($sql);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->bindValue(':perPage', $perPage, PDO::PARAM_INT);
    }

    $stmt->execute();
    $users = $stmt->fetchAll();

    // Get total count for pagination
    $countSql = "SELECT COUNT(*) FROM users";
    if (!empty($search)) {
        $countSql .= " WHERE name LIKE :search OR email LIKE :search";
        $countStmt = $pdo->prepare($countSql);
        $countStmt->execute([':search' => "%$search%"]);
    } else {
        $countStmt = $pdo->query($countSql);
    }
    $totalUsers = $countStmt->fetchColumn();
    $totalPages = ceil($totalUsers / $perPage);

} catch (PDOException $e) {
    die("Error: " . $e->getMessage());
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>User Management</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-5">
        <h2>User Management</h2>

        <?php if (isset($_GET['success'])): ?>
            <div class="alert alert-success">
                <?php
                if ($_GET['success'] === 'created') echo 'User created successfully!';
                if ($_GET['success'] === 'updated') echo 'User updated successfully!';
                if ($_GET['success'] === 'deleted') echo 'User deleted successfully!';
                ?>
            </div>
        <?php endif; ?>

        <div class="row mb-3">
            <div class="col-md-6">
                <a href="create.php" class="btn btn-primary">Add New User</a>
            </div>
            <div class="col-md-6">
                <form method="GET" class="d-flex">
                    <input type="text" name="search" class="form-control me-2"
                           placeholder="Search by name or email"
                           value="<?php echo htmlspecialchars($search); ?>">
                    <button type="submit" class="btn btn-secondary">Search</button>
                </form>
            </div>
        </div>

        <table class="table table-striped">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Phone</th>
                    <th>Created</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($users as $user): ?>
                    <tr>
                        <td><?php echo $user['id']; ?></td>
                        <td><?php echo htmlspecialchars($user['name']); ?></td>
                        <td><?php echo htmlspecialchars($user['email']); ?></td>
                        <td><?php echo htmlspecialchars($user['phone']); ?></td>
                        <td><?php echo date('Y-m-d', strtotime($user['created_at'])); ?></td>
                        <td>
                            <a href="view.php?id=<?php echo $user['id']; ?>" class="btn btn-sm btn-info">View</a>
                            <a href="edit.php?id=<?php echo $user['id']; ?>" class="btn btn-sm btn-warning">Edit</a>
                            <a href="delete.php?id=<?php echo $user['id']; ?>"
                               class="btn btn-sm btn-danger"
                               onclick="return confirm('Are you sure?')">Delete</a>
                        </td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>

        <!-- Pagination -->
        <?php if ($totalPages > 1): ?>
            <nav>
                <ul class="pagination">
                    <?php for ($i = 1; $i <= $totalPages; $i++): ?>
                        <li class="page-item <?php echo $i === $page ? 'active' : ''; ?>">
                            <a class="page-link" href="?page=<?php echo $i; ?>&search=<?php echo urlencode($search); ?>">
                                <?php echo $i; ?>
                            </a>
                        </li>
                    <?php endfor; ?>
                </ul>
            </nav>
        <?php endif; ?>
    </div>
</body>
</html>

View Single User (view.php)

<?php
// view.php
require_once 'config.php';

$id = isset($_GET['id']) ? (int)$_GET['id'] : 0;

try {
    $pdo = new PDO("mysql:host=localhost;dbname=mydb", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$id]);
    $user = $stmt->fetch();

    if (!$user) {
        header("Location: index.php?error=User not found");
        exit;
    }

} catch (PDOException $e) {
    die("Error: " . $e->getMessage());
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>View User</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-5">
        <h2>User Details</h2>

        <div class="card">
            <div class="card-body">
                <p><strong>ID:</strong> <?php echo $user['id']; ?></p>
                <p><strong>Name:</strong> <?php echo htmlspecialchars($user['name']); ?></p>
                <p><strong>Email:</strong> <?php echo htmlspecialchars($user['email']); ?></p>
                <p><strong>Phone:</strong> <?php echo htmlspecialchars($user['phone']); ?></p>
                <p><strong>Created:</strong> <?php echo $user['created_at']; ?></p>
                <p><strong>Updated:</strong> <?php echo $user['updated_at']; ?></p>
            </div>
        </div>

        <div class="mt-3">
            <a href="edit.php?id=<?php echo $user['id']; ?>" class="btn btn-warning">Edit</a>
            <a href="index.php" class="btn btn-secondary">Back to List</a>
        </div>
    </div>
</body>
</html>

UPDATE Operation

Loading Existing Data into a Form

An edit form is different from a create form in one important way: it must pre-populate its fields with the record's current values so the user only needs to change what they want. The standard pattern is:

Read the id from the query string and cast it to an integer: (int)$_GET['id'].
Fetch the row with a prepared SELECT … WHERE id = ?.
If no row is found, redirect to the list page with an error.
Otherwise render the form with value="<?php echo htmlspecialchars($user['field']); ?>" on every input.

Always Cast the ID to Integer

$_GET['id'] is a string. Always cast it with (int) before using it in a query. This prevents a class of attack where an attacker passes a non-numeric value designed to manipulate the SQL. Even with prepared statements, casting is a good defensive habit because it validates the expected data type.

Edit Form (edit.php)

<?php
// edit.php
require_once 'config.php';

$id = isset($_GET['id']) ? (int)$_GET['id'] : 0;

try {
    $pdo = new PDO("mysql:host=localhost;dbname=mydb", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$id]);
    $user = $stmt->fetch();

    if (!$user) {
        header("Location: index.php?error=User not found");
        exit;
    }

} catch (PDOException $e) {
    die("Error: " . $e->getMessage());
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Edit User</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-5">
        <h2>Edit User</h2>

        <?php if (isset($_GET['error'])): ?>
            <div class="alert alert-danger"><?php echo htmlspecialchars($_GET['error']); ?></div>
        <?php endif; ?>

        <form action="update.php" method="POST">
            <input type="hidden" name="id" value="<?php echo $user['id']; ?>">

            <div class="mb-3">
                <label class="form-label">Name</label>
                <input type="text" name="name" class="form-control"
                       value="<?php echo htmlspecialchars($user['name']); ?>" required>
            </div>

            <div class="mb-3">
                <label class="form-label">Email</label>
                <input type="email" name="email" class="form-control"
                       value="<?php echo htmlspecialchars($user['email']); ?>" required>
            </div>

            <div class="mb-3">
                <label class="form-label">Phone</label>
                <input type="tel" name="phone" class="form-control"
                       value="<?php echo htmlspecialchars($user['phone']); ?>">
            </div>

            <button type="submit" class="btn btn-primary">Update User</button>
            <a href="index.php" class="btn btn-secondary">Cancel</a>
        </form>
    </div>
</body>
</html>

Update Handler (update.php)

<?php
// update.php
require_once 'config.php';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    try {
        // Get form data
        $id = (int)$_POST['id'];
        $name = trim($_POST['name']);
        $email = trim($_POST['email']);
        $phone = trim($_POST['phone']);

        // Validate input
        $errors = [];

        if (empty($name)) {
            $errors[] = "Name is required";
        }

        if (empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
            $errors[] = "Valid email is required";
        }

        if (!empty($errors)) {
            $errorMsg = implode(', ', $errors);
            header("Location: edit.php?id=$id&error=" . urlencode($errorMsg));
            exit;
        }

        // Connect to database
        $pdo = new PDO("mysql:host=localhost;dbname=mydb", "root", "");
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // Update data
        $sql = "UPDATE users SET name = :name, email = :email, phone = :phone WHERE id = :id";
        $stmt = $pdo->prepare($sql);

        $stmt->execute([
            ':name' => $name,
            ':email' => $email,
            ':phone' => $phone,
            ':id' => $id
        ]);

        // Redirect with success message
        header("Location: index.php?success=updated");
        exit;

    } catch (PDOException $e) {
        // Check for duplicate email
        if ($e->getCode() == 23000) {
            header("Location: edit.php?id=$id&error=Email already exists");
        } else {
            header("Location: edit.php?id=$id&error=Database error occurred");
        }
        exit;
    }
} else {
    header("Location: index.php");
    exit;
}
?>

DELETE Operation

Hard Delete vs Soft Delete

There are two strategies for removing records:

Strategy	How It Works	Pros	Cons
Hard Delete	Executes `DELETE FROM … WHERE id=?`. Row is permanently removed.	Simple; storage is reclaimed	Data cannot be recovered; breaks foreign-key references
Soft Delete	Adds a `deleted_at TIMESTAMP` column. Sets it to `NOW()` instead of deleting the row.	Data is recoverable; audit trail preserved	Must add `WHERE deleted_at IS NULL` to every SELECT query

Never Delete via GET Request

A link like <a href="delete.php?id=5">Delete</a> triggers a destructive action via an HTTP GET. Web crawlers, browser prefetchers, and link-preview bots will follow GET links automatically — and accidentally delete your data. Destructive operations must always require a POST request (e.g. a confirmation form or an AJAX DELETE call with a CSRF token).

Delete Handler (delete.php)

<?php
// delete.php
require_once 'config.php';

$id = isset($_GET['id']) ? (int)$_GET['id'] : 0;

if ($id > 0) {
    try {
        $pdo = new PDO("mysql:host=localhost;dbname=mydb", "root", "");
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // Check if user exists
        $stmt = $pdo->prepare("SELECT id FROM users WHERE id = ?");
        $stmt->execute([$id]);

        if ($stmt->fetch()) {
            // Delete user
            $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
            $stmt->execute([$id]);

            header("Location: index.php?success=deleted");
        } else {
            header("Location: index.php?error=User not found");
        }

    } catch (PDOException $e) {
        header("Location: index.php?error=Error deleting user");
    }
} else {
    header("Location: index.php");
}
exit;
?>

Soft Delete (Alternative)

<?php
// Soft delete - add deleted_at column to users table
// ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;

// soft_delete.php
$id = (int)$_GET['id'];

try {
    $pdo = new PDO("mysql:host=localhost;dbname=mydb", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Soft delete
    $stmt = $pdo->prepare("UPDATE users SET deleted_at = NOW() WHERE id = ?");
    $stmt->execute([$id]);

    header("Location: index.php?success=deleted");

} catch (PDOException $e) {
    header("Location: index.php?error=Error deleting user");
}

// When querying, exclude deleted records
// SELECT * FROM users WHERE deleted_at IS NULL

// Restore deleted user
// UPDATE users SET deleted_at = NULL WHERE id = ?
?>

Complete CRUD Application

MVC Architecture Overview

As a CRUD application grows, mixing database calls, business logic, and HTML output in a single file becomes unmanageable. The Model-View-Controller (MVC) pattern separates these concerns:

Model — Encapsulates data access logic (SQL queries). Example: User.php
View — Renders HTML using data provided by the controller. Example: views/users/index.php
Controller — Receives HTTP requests, calls the model, passes data to the view. Example: UserController.php

The User class in the example below is a simple model. Even without a full MVC framework, extracting SQL into a class dramatically improves maintainability and testability.

Static Connections with a Singleton

The getConnection() function below uses a static $pdo = null variable to implement a basic singleton pattern. On the first call it creates the PDO object; on every subsequent call it returns the same object. This avoids opening multiple database connections per request, which would waste resources and slow your application.

Database Configuration (config.php)

<?php
// config.php
define('DB_HOST', 'localhost');
define('DB_NAME', 'mydb');
define('DB_USER', 'root');
define('DB_PASS', '');

function getConnection() {
    static $pdo = null;

    if ($pdo === null) {
        try {
            $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4";
            $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ];

            $pdo = new PDO($dsn, DB_USER, DB_PASS, $options);

        } catch (PDOException $e) {
            error_log("Database connection error: " . $e->getMessage());
            die("Database connection failed");
        }
    }

    return $pdo;
}
?>

User Model Class (User.php)

<?php
// User.php
class User {
    private $pdo;

    public function __construct($pdo) {
        $this->pdo = $pdo;
    }

    public function create($data) {
        $sql = "INSERT INTO users (name, email, phone) VALUES (:name, :email, :phone)";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($data);
    }

    public function getAll($page = 1, $perPage = 10, $search = '') {
        $offset = ($page - 1) * $perPage;

        if (!empty($search)) {
            $sql = "SELECT * FROM users WHERE name LIKE :search OR email LIKE :search
                    ORDER BY id DESC LIMIT :offset, :perPage";
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindValue(':search', "%$search%");
        } else {
            $sql = "SELECT * FROM users ORDER BY id DESC LIMIT :offset, :perPage";
            $stmt = $this->pdo->prepare($sql);
        }

        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->bindValue(':perPage', $perPage, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function getById($id) {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch();
    }

    public function update($id, $data) {
        $sql = "UPDATE users SET name = :name, email = :email, phone = :phone WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $data[':id'] = $id;
        return $stmt->execute($data);
    }

    public function delete($id) {
        $stmt = $this->pdo->prepare("DELETE FROM users WHERE id = ?");
        return $stmt->execute([$id]);
    }

    public function count($search = '') {
        if (!empty($search)) {
            $sql = "SELECT COUNT(*) FROM users WHERE name LIKE :search OR email LIKE :search";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([':search' => "%$search%"]);
        } else {
            $sql = "SELECT COUNT(*) FROM users";
            $stmt = $this->pdo->query($sql);
        }

        return $stmt->fetchColumn();
    }
}
?>

Session Summary

Key Points

CRUD operations form the foundation of database-driven applications
CREATE: INSERT new records with proper validation
READ: SELECT and display data with pagination and search
UPDATE: Modify existing records with validation
DELETE: Remove records (hard or soft delete)
Always use prepared statements to prevent SQL injection
Implement proper error handling and user feedback
Validate and sanitize all user input
Use MVC patterns for better code organization

Next Session Preview

In the next session, we will explore PHP XML Processing, learning how to read, parse, and generate XML documents in PHP applications.