PHP Fundamentals

Building a CRUD Application

13 min Lesson 39 of 45

What is CRUD?

CRUD stands for Create, Read, Update, and Delete - the four basic operations of persistent storage. In this lesson, we'll build a complete task management application demonstrating all CRUD operations with proper security.

Project Structure

task-manager/
├── config/
│   └── database.php      # Database configuration
├── includes/
│   ├── header.php        # Common header
│   └── footer.php        # Common footer
├── css/
│   └── style.css         # Styles
├── index.php             # List tasks (READ)
├── create.php            # Add new task (CREATE)
├── edit.php              # Edit task (UPDATE)
├── delete.php            # Delete task (DELETE)
└── view.php              # View single task (READ)

Step 1: Database Setup

First, create the database and tasks table:

CREATE DATABASE task_manager;
USE task_manager;

CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    status ENUM('pending', 'in_progress', 'completed') DEFAULT 'pending',
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
    due_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Step 2: Database Configuration (config/database.php)

<?php
// Database configuration
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'task_manager');

// Enable error reporting (disable in production)
ini_set('display_errors', 1);
error_reporting(E_ALL);

// Enable MySQLi exception mode
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Create connection
try {
    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    $conn->set_charset("utf8mb4");
} catch (mysqli_sql_exception $e) {
    die("Database connection failed: " . $e->getMessage());
}
?>

Step 3: Header Template (includes/header.php)

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><?php echo $page_title ?? 'Task Manager'; ?></title>
    <link rel="stylesheet" href="css/style.css">
</head>
<body>
    <nav>
        <div class="container">
            <h1>Task Manager</h1>
            <ul>
                <li><a href="index.php">All Tasks</a></li>
                <li><a href="create.php">Add New Task</a></li>
            </ul>
        </div>
    </nav>
    <main class="container">

Step 4: Footer Template (includes/footer.php)

    </main>
    <footer>
        <div class="container">
            <p>&copy; <?php echo date('Y'); ?> Task Manager</p>
        </div>
    </footer>
</body>
</html>

Step 5: READ - List All Tasks (index.php)

<?php
require_once 'config/database.php';
$page_title = 'All Tasks';
include 'includes/header.php';

// Get filter from URL
$filter = $_GET['status'] ?? 'all';

// Build query based on filter
if ($filter === 'all') {
    $sql = "SELECT * FROM tasks ORDER BY created_at DESC";
    $stmt = $conn->prepare($sql);
} else {
    $sql = "SELECT * FROM tasks WHERE status = ? ORDER BY created_at DESC";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("s", $filter);
}

$stmt->execute();
$result = $stmt->get_result();
?>

<div class="page-header">
    <h2>All Tasks</h2>
    <a href="create.php" class="btn btn-primary">Add New Task</a>
</div>

<!-- Filter buttons -->
<div class="filters">
    <a href="?status=all" class="btn <?php echo $filter === 'all' ? 'active' : ''; ?>">All</a>
    <a href="?status=pending" class="btn <?php echo $filter === 'pending' ? 'active' : ''; ?>">Pending</a>
    <a href="?status=in_progress" class="btn <?php echo $filter === 'in_progress' ? 'active' : ''; ?>">In Progress</a>
    <a href="?status=completed" class="btn <?php echo $filter === 'completed' ? 'active' : ''; ?>">Completed</a>
</div>

<?php if ($result->num_rows > 0): ?>
    <table class="task-table">
        <thead>
            <tr>
                <th>Title</th>
                <th>Status</th>
                <th>Priority</th>
                <th>Due Date</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
            <?php while ($task = $result->fetch_assoc()): ?>
                <tr>
                    <td>
                        <a href="view.php?id=<?php echo $task['id']; ?>">
                            <?php echo htmlspecialchars($task['title']); ?>
                        </a>
                    </td>
                    <td>
                        <span class="badge badge-<?php echo $task['status']; ?>">
                            <?php echo ucfirst(str_replace('_', ' ', $task['status'])); ?>
                        </span>
                    </td>
                    <td>
                        <span class="badge badge-<?php echo $task['priority']; ?>">
                            <?php echo ucfirst($task['priority']); ?>
                        </span>
                    </td>
                    <td><?php echo $task['due_date'] ? date('M d, Y', strtotime($task['due_date'])) : 'N/A'; ?></td>
                    <td>
                        <a href="edit.php?id=<?php echo $task['id']; ?>" class="btn btn-sm">Edit</a>
                        <a href="delete.php?id=<?php echo $task['id']; ?>" class="btn btn-sm btn-danger" onclick="return confirm('Delete this task?')">Delete</a>
                    </td>
                </tr>
            <?php endwhile; ?>
        </tbody>
    </table>
<?php else: ?>
    <p class="no-tasks">No tasks found. <a href="create.php">Create one now</a></p>
<?php endif; ?>

<?php
$stmt->close();
include 'includes/footer.php';
?>

Step 6: CREATE - Add New Task (create.php)

<?php
require_once 'config/database.php';
$page_title = 'Add New Task';

$errors = [];
$success = false;

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Validate inputs
    $title = trim($_POST['title'] ?? '');
    $description = trim($_POST['description'] ?? '');
    $status = $_POST['status'] ?? 'pending';
    $priority = $_POST['priority'] ?? 'medium';
    $due_date = $_POST['due_date'] ?? null;

    // Validation
    if (empty($title)) {
        $errors[] = "Title is required";
    } elseif (strlen($title) > 200) {
        $errors[] = "Title must not exceed 200 characters";
    }

    if (!in_array($status, ['pending', 'in_progress', 'completed'])) {
        $errors[] = "Invalid status";
    }

    if (!in_array($priority, ['low', 'medium', 'high'])) {
        $errors[] = "Invalid priority";
    }

    // If no errors, insert task
    if (empty($errors)) {
        $sql = "INSERT INTO tasks (title, description, status, priority, due_date)
                VALUES (?, ?, ?, ?, ?)";

        $stmt = $conn->prepare($sql);
        $stmt->bind_param("sssss", $title, $description, $status, $priority, $due_date);

        if ($stmt->execute()) {
            $success = true;
            $task_id = $stmt->insert_id;
            header("Location: view.php?id=$task_id&created=1");
            exit;
        } else {
            $errors[] = "Failed to create task";
        }

        $stmt->close();
    }
}

include 'includes/header.php';
?>

<h2>Add New Task</h2>

<?php if (!empty($errors)): ?>
    <div class="alert alert-danger">
        <ul>
            <?php foreach ($errors as $error): ?>
                <li><?php echo htmlspecialchars($error); ?></li>
            <?php endforeach; ?>
        </ul>
    </div>
<?php endif; ?>

<form method="POST" action="" class="task-form">
    <div class="form-group">
        <label for="title">Title *</label>
        <input type="text" id="title" name="title" required
               value="<?php echo htmlspecialchars($_POST['title'] ?? ''); ?>">
    </div>

    <div class="form-group">
        <label for="description">Description</label>
        <textarea id="description" name="description" rows="5"><?php echo htmlspecialchars($_POST['description'] ?? ''); ?></textarea>
    </div>

    <div class="form-row">
        <div class="form-group">
            <label for="status">Status</label>
            <select id="status" name="status">
                <option value="pending" <?php echo ($_POST['status'] ?? '') === 'pending' ? 'selected' : ''; ?>>Pending</option>
                <option value="in_progress" <?php echo ($_POST['status'] ?? '') === 'in_progress' ? 'selected' : ''; ?>>In Progress</option>
                <option value="completed" <?php echo ($_POST['status'] ?? '') === 'completed' ? 'selected' : ''; ?>>Completed</option>
            </select>
        </div>

        <div class="form-group">
            <label for="priority">Priority</label>
            <select id="priority" name="priority">
                <option value="low" <?php echo ($_POST['priority'] ?? '') === 'low' ? 'selected' : ''; ?>>Low</option>
                <option value="medium" <?php echo ($_POST['priority'] ?? 'medium') === 'medium' ? 'selected' : ''; ?>>Medium</option>
                <option value="high" <?php echo ($_POST['priority'] ?? '') === 'high' ? 'selected' : ''; ?>>High</option>
            </select>
        </div>

        <div class="form-group">
            <label for="due_date">Due Date</label>
            <input type="date" id="due_date" name="due_date"
                   value="<?php echo htmlspecialchars($_POST['due_date'] ?? ''); ?>">
        </div>
    </div>

    <div class="form-actions">
        <button type="submit" class="btn btn-primary">Create Task</button>
        <a href="index.php" class="btn">Cancel</a>
    </div>
</form>

<?php include 'includes/footer.php'; ?>

Step 7: READ - View Single Task (view.php)

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

// Get task ID
$task_id = $_GET['id'] ?? 0;

// Fetch task
$stmt = $conn->prepare("SELECT * FROM tasks WHERE id = ?");
$stmt->bind_param("i", $task_id);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows === 0) {
    header("Location: index.php");
    exit;
}

$task = $result->fetch_assoc();
$page_title = htmlspecialchars($task['title']);

include 'includes/header.php';
?>

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

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

<div class="task-detail">
    <div class="task-header">
        <h2><?php echo htmlspecialchars($task['title']); ?></h2>
        <div class="task-actions">
            <a href="edit.php?id=<?php echo $task['id']; ?>" class="btn">Edit</a>
            <a href="delete.php?id=<?php echo $task['id']; ?>" class="btn btn-danger"
               onclick="return confirm('Delete this task?')">Delete</a>
        </div>
    </div>

    <div class="task-meta">
        <div class="meta-item">
            <strong>Status:</strong>
            <span class="badge badge-<?php echo $task['status']; ?>">
                <?php echo ucfirst(str_replace('_', ' ', $task['status'])); ?>
            </span>
        </div>
        <div class="meta-item">
            <strong>Priority:</strong>
            <span class="badge badge-<?php echo $task['priority']; ?>">
                <?php echo ucfirst($task['priority']); ?>
            </span>
        </div>
        <div class="meta-item">
            <strong>Due Date:</strong>
            <?php echo $task['due_date'] ? date('F d, Y', strtotime($task['due_date'])) : 'Not set'; ?>
        </div>
        <div class="meta-item">
            <strong>Created:</strong>
            <?php echo date('F d, Y g:i A', strtotime($task['created_at'])); ?>
        </div>
    </div>

    <div class="task-description">
        <h3>Description</h3>
        <p><?php echo nl2br(htmlspecialchars($task['description'] ?: 'No description provided.')); ?></p>
    </div>

    <div class="task-footer">
        <a href="index.php" class="btn">&larr; Back to All Tasks</a>
    </div>
</div>

<?php
$stmt->close();
include 'includes/footer.php';
?>

Step 8: UPDATE - Edit Task (edit.php)

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

// Get task ID
$task_id = $_GET['id'] ?? 0;

// Fetch current task
$stmt = $conn->prepare("SELECT * FROM tasks WHERE id = ?");
$stmt->bind_param("i", $task_id);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows === 0) {
    header("Location: index.php");
    exit;
}

$task = $result->fetch_assoc();
$page_title = 'Edit Task';
$errors = [];

// Process form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $title = trim($_POST['title'] ?? '');
    $description = trim($_POST['description'] ?? '');
    $status = $_POST['status'] ?? 'pending';
    $priority = $_POST['priority'] ?? 'medium';
    $due_date = $_POST['due_date'] ?? null;

    // Validation
    if (empty($title)) {
        $errors[] = "Title is required";
    } elseif (strlen($title) > 200) {
        $errors[] = "Title must not exceed 200 characters";
    }

    if (!in_array($status, ['pending', 'in_progress', 'completed'])) {
        $errors[] = "Invalid status";
    }

    if (!in_array($priority, ['low', 'medium', 'high'])) {
        $errors[] = "Invalid priority";
    }

    // If no errors, update task
    if (empty($errors)) {
        $sql = "UPDATE tasks
                SET title = ?, description = ?, status = ?, priority = ?, due_date = ?
                WHERE id = ?";

        $stmt = $conn->prepare($sql);
        $stmt->bind_param("sssssi", $title, $description, $status, $priority, $due_date, $task_id);

        if ($stmt->execute()) {
            header("Location: view.php?id=$task_id&updated=1");
            exit;
        } else {
            $errors[] = "Failed to update task";
        }

        $stmt->close();
    }
} else {
    // Pre-fill form with current values
    $_POST = $task;
}

include 'includes/header.php';
?>

<h2>Edit Task</h2>

<?php if (!empty($errors)): ?>
    <div class="alert alert-danger">
        <ul>
            <?php foreach ($errors as $error): ?>
                <li><?php echo htmlspecialchars($error); ?></li>
            <?php endforeach; ?>
        </ul>
    </div>
<?php endif; ?>

<!-- Form is identical to create.php -->
<form method="POST" action="" class="task-form">
    <!-- Same form fields as create.php -->
    <!-- ... (omitted for brevity) ... -->

    <div class="form-actions">
        <button type="submit" class="btn btn-primary">Update Task</button>
        <a href="view.php?id=<?php echo $task_id; ?>" class="btn">Cancel</a>
    </div>
</form>

<?php include 'includes/footer.php'; ?>

Step 9: DELETE - Delete Task (delete.php)

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

// Get task ID
$task_id = $_GET['id'] ?? 0;

// Verify task exists
$stmt = $conn->prepare("SELECT id FROM tasks WHERE id = ?");
$stmt->bind_param("i", $task_id);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows === 0) {
    header("Location: index.php");
    exit;
}

// Delete task
$stmt = $conn->prepare("DELETE FROM tasks WHERE id = ?");
$stmt->bind_param("i", $task_id);

if ($stmt->execute()) {
    header("Location: index.php?deleted=1");
} else {
    header("Location: index.php?error=1");
}

$stmt->close();
exit;
?>

Tip: For production applications, implement "soft deletes" by adding a deleted_at column instead of permanently removing records.

Security Notes:

All user inputs are validated and sanitized
Prepared statements prevent SQL injection
htmlspecialchars() prevents XSS attacks
HTTP POST method for data modification (create, update, delete)
Confirmation prompts before destructive actions

Exercise: Enhance the Task Manager

Add pagination to the task list (10 tasks per page)
Implement search functionality (search by title or description)
Add sorting options (by date, priority, status)
Create a "Mark as Completed" quick action button
Add task categories or tags
Implement user authentication (multiple users with their own tasks)
Add file attachments to tasks
Create a dashboard with task statistics
Add email notifications for due dates
Implement an API endpoint to export tasks as JSON

Best Practices for CRUD Applications

Validation: Always validate user input on the server side
Sanitization: Escape output with htmlspecialchars()
Prepared Statements: Never concatenate user input in SQL queries
Error Handling: Display user-friendly error messages
Redirects: Use POST-Redirect-GET pattern to prevent duplicate submissions
Confirmation: Ask for confirmation before deleting data
Authorization: Verify users can only modify their own data
Logging: Log important actions for auditing

Summary

CRUD applications perform Create, Read, Update, and Delete operations
Use prepared statements for all database operations
Validate and sanitize all user input
Implement proper error handling and user feedback
Use the POST-Redirect-GET pattern for form submissions
Always escape output to prevent XSS attacks
Structure your code with reusable components (headers, footers, config)
Test all CRUD operations thoroughly