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.

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

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>

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

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

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

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

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.