PHP CRUD Tutorial with MySQL: Complete Step-by-Step Guide (2025)

Introduction

CRUD operations form the backbone of most web applications. CRUD stands for Create, Read, Update, and Delete – the four fundamental operations you can perform on data. Whether you’re building a simple contact manager or a complex e-commerce platform, mastering PHP CRUD with MySQL is essential for any web developer.

In this comprehensive tutorial, we’ll build a complete student management system from scratch, covering everything from database setup to advanced error handling. By the end, you’ll have a solid foundation for creating robust web applications.

What You’ll Learn

Setting up a MySQL database and PHP environment
Creating a secure database connection
Implementing all four CRUD operations
Building a user-friendly web interface
Adding form validation and error handling
Securing your application against common vulnerabilities

Prerequisites

Before we dive in, let’s check what you’ll need:

Basic knowledge of PHP (variables, functions, arrays)
Understanding of HTML and CSS
Familiarity with MySQL basics
A local development environment (XAMPP, WAMP, or similar)

Step 1: Environment Setup

Database Configuration

First, let’s create our MySQL database. Open your MySQL client (phpMyAdmin or command line) and run:

CREATE DATABASE student_management;
USE student_management;

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15),
    course VARCHAR(100),
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Project Structure

Create the following folder structure:

student-crud/
├── config/
│   └── database.php
├── includes/
│   ├── header.php
│   └── footer.php
├── css/
│   └── style.css
├── index.php
├── create.php
├── read.php
├── update.php
└── delete.php

Step 2: Database Connection

Create config/database.php:

<?php
class Database {
    private $host = "localhost";
    private $db_name = "student_management";
    private $username = "root";
    private $password = "";
    private $conn;

    public function getConnection() {
        $this->conn = null;

        try {
            $this->conn = new PDO(
                "mysql:host=" . $this->host . ";dbname=" . $this->db_name,
                $this->username,
                $this->password
            );
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        } catch(PDOException $exception) {
            echo "Connection error: " . $exception->getMessage();
        }

        return $this->conn;
    }
}
?>

Step 3: Creating the User Interface

Header Template

Create 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 isset($page_title) ? $page_title : 'Student Management'; ?></title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="css/style.css">
</head>
<body>
    <nav class="navbar navbar-expand-lg navbar-dark bg-primary">
        <div class="container">
            <a class="navbar-brand" href="index.php">Student Management</a>
            <div class="navbar-nav ms-auto">
                <a class="nav-link" href="index.php">Home</a>
                <a class="nav-link" href="create.php">Add Student</a>
            </div>
        </div>
    </nav>
    <div class="container mt-4">

Footer Template

Create includes/footer.php:

    </div>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

Step 4: READ Operation – Displaying Data

Create index.php:

<?php
$page_title = "Student List";
include_once 'includes/header.php';
include_once 'config/database.php';

// Initialize database connection
$database = new Database();
$db = $database->getConnection();

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

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

if (!empty($search)) {
    $search_condition = "WHERE first_name LIKE :search OR last_name LIKE :search OR email LIKE :search";
    $params[':search'] = "%$search%";
}

try {
    // Count total records
    $count_query = "SELECT COUNT(*) as total FROM students $search_condition";
    $count_stmt = $db->prepare($count_query);
    foreach ($params as $key => $value) {
        $count_stmt->bindValue($key, $value);
    }
    $count_stmt->execute();
    $total_records = $count_stmt->fetch()['total'];
    $total_pages = ceil($total_records / $records_per_page);

    // Fetch students
    $query = "SELECT * FROM students $search_condition ORDER BY created_at DESC LIMIT :limit OFFSET :offset";
    $stmt = $db->prepare($query);

    foreach ($params as $key => $value) {
        $stmt->bindValue($key, $value);
    }
    $stmt->bindValue(':limit', $records_per_page, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    $students = $stmt->fetchAll();

} catch(PDOException $exception) {
    $error_message = "Error: " . $exception->getMessage();
}
?>

<div class="row">
    <div class="col-md-12">
        <h2>Student Management System</h2>

        <!-- Search Form -->
        <form method="GET" class="mb-3">
            <div class="row">
                <div class="col-md-6">
                    <input type="text" name="search" class="form-control" 
                           placeholder="Search students..." value="<?php echo htmlspecialchars($search); ?>">
                </div>
                <div class="col-md-2">
                    <button type="submit" class="btn btn-outline-primary">Search</button>
                </div>
                <div class="col-md-2">
                    <a href="index.php" class="btn btn-outline-secondary">Clear</a>
                </div>
                <div class="col-md-2">
                    <a href="create.php" class="btn btn-success">Add New Student</a>
                </div>
            </div>
        </form>

        <!-- Students Table -->
        <?php if (isset($error_message)): ?>
            <div class="alert alert-danger"><?php echo $error_message; ?></div>
        <?php elseif (empty($students)): ?>
            <div class="alert alert-info">No students found.</div>
        <?php else: ?>
            <div class="table-responsive">
                <table class="table table-striped table-hover">
                    <thead class="table-dark">
                        <tr>
                            <th>ID</th>
                            <th>Name</th>
                            <th>Email</th>
                            <th>Phone</th>
                            <th>Course</th>
                            <th>Enrollment Date</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($students as $student): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($student['id']); ?></td>
                                <td><?php echo htmlspecialchars($student['first_name'] . ' ' . $student['last_name']); ?></td>
                                <td><?php echo htmlspecialchars($student['email']); ?></td>
                                <td><?php echo htmlspecialchars($student['phone'] ?: 'N/A'); ?></td>
                                <td><?php echo htmlspecialchars($student['course'] ?: 'N/A'); ?></td>
                                <td><?php echo date('M d, Y', strtotime($student['enrollment_date'])); ?></td>
                                <td>
                                    <a href="read.php?id=<?php echo $student['id']; ?>" class="btn btn-info btn-sm">View</a>
                                    <a href="update.php?id=<?php echo $student['id']; ?>" class="btn btn-warning btn-sm">Edit</a>
                                    <a href="delete.php?id=<?php echo $student['id']; ?>" 
                                       class="btn btn-danger btn-sm"
                                       onclick="return confirm('Are you sure you want to delete this student?')">Delete</a>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>

            <!-- Pagination -->
            <?php if ($total_pages > 1): ?>
                <nav aria-label="Page navigation">
                    <ul class="pagination justify-content-center">
                        <?php for ($i = 1; $i <= $total_pages; $i++): ?>
                            <li class="page-item <?php echo ($i == $page) ? 'active' : ''; ?>">
                                <a class="page-link" href="?page=<?php echo $i; ?><?php echo !empty($search) ? '&search=' . urlencode($search) : ''; ?>">
                                    <?php echo $i; ?>
                                </a>
                            </li>
                        <?php endfor; ?>
                    </ul>
                </nav>
            <?php endif; ?>
        <?php endif; ?>
    </div>
</div>

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

Step 5: CREATE Operation – Adding New Records

Create create.php:

<?php
$page_title = "Add New Student";
include_once 'includes/header.php';
include_once 'config/database.php';

$database = new Database();
$db = $database->getConnection();

// Initialize variables
$first_name = $last_name = $email = $phone = $course = '';
$errors = [];
$success_message = '';

// Process form submission
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // Sanitize and validate input
    $first_name = trim($_POST['first_name']);
    $last_name = trim($_POST['last_name']);
    $email = trim($_POST['email']);
    $phone = trim($_POST['phone']);
    $course = trim($_POST['course']);

    // Validation
    if (empty($first_name)) {
        $errors['first_name'] = 'First name is required';
    }
    if (empty($last_name)) {
        $errors['last_name'] = 'Last name is required';
    }
    if (empty($email)) {
        $errors['email'] = 'Email is required';
    } elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $errors['email'] = 'Invalid email format';
    }
    if (!empty($phone) && !preg_match('/^[0-9+\-\s()]+$/', $phone)) {
        $errors['phone'] = 'Invalid phone number format';
    }

    // Check for duplicate email
    if (empty($errors['email'])) {
        try {
            $check_query = "SELECT id FROM students WHERE email = :email";
            $check_stmt = $db->prepare($check_query);
            $check_stmt->bindParam(':email', $email);
            $check_stmt->execute();

            if ($check_stmt->rowCount() > 0) {
                $errors['email'] = 'Email already exists';
            }
        } catch(PDOException $exception) {
            $errors['database'] = 'Database error: ' . $exception->getMessage();
        }
    }

    // Insert data if no errors
    if (empty($errors)) {
        try {
            $query = "INSERT INTO students (first_name, last_name, email, phone, course) 
                     VALUES (:first_name, :last_name, :email, :phone, :course)";

            $stmt = $db->prepare($query);
            $stmt->bindParam(':first_name', $first_name);
            $stmt->bindParam(':last_name', $last_name);
            $stmt->bindParam(':email', $email);
            $stmt->bindParam(':phone', $phone);
            $stmt->bindParam(':course', $course);

            if ($stmt->execute()) {
                $success_message = 'Student added successfully!';
                // Clear form data
                $first_name = $last_name = $email = $phone = $course = '';
            }
        } catch(PDOException $exception) {
            $errors['database'] = 'Error adding student: ' . $exception->getMessage();
        }
    }
}
?>

<div class="row justify-content-center">
    <div class="col-md-8">
        <div class="card">
            <div class="card-header">
                <h3>Add New Student</h3>
            </div>
            <div class="card-body">
                <?php if (!empty($success_message)): ?>
                    <div class="alert alert-success alert-dismissible fade show" role="alert">
                        <?php echo $success_message; ?>
                        <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
                    </div>
                <?php endif; ?>

                <?php if (!empty($errors['database'])): ?>
                    <div class="alert alert-danger"><?php echo $errors['database']; ?></div>
                <?php endif; ?>

                <form method="POST" action="" novalidate>
                    <div class="row">
                        <div class="col-md-6 mb-3">
                            <label for="first_name" class="form-label">First Name *</label>
                            <input type="text" class="form-control <?php echo isset($errors['first_name']) ? 'is-invalid' : ''; ?>" 
                                   id="first_name" name="first_name" value="<?php echo htmlspecialchars($first_name); ?>" required>
                            <?php if (isset($errors['first_name'])): ?>
                                <div class="invalid-feedback"><?php echo $errors['first_name']; ?></div>
                            <?php endif; ?>
                        </div>

                        <div class="col-md-6 mb-3">
                            <label for="last_name" class="form-label">Last Name *</label>
                            <input type="text" class="form-control <?php echo isset($errors['last_name']) ? 'is-invalid' : ''; ?>" 
                                   id="last_name" name="last_name" value="<?php echo htmlspecialchars($last_name); ?>" required>
                            <?php if (isset($errors['last_name'])): ?>
                                <div class="invalid-feedback"><?php echo $errors['last_name']; ?></div>
                            <?php endif; ?>
                        </div>
                    </div>

                    <div class="mb-3">
                        <label for="email" class="form-label">Email Address *</label>
                        <input type="email" class="form-control <?php echo isset($errors['email']) ? 'is-invalid' : ''; ?>" 
                               id="email" name="email" value="<?php echo htmlspecialchars($email); ?>" required>
                        <?php if (isset($errors['email'])): ?>
                            <div class="invalid-feedback"><?php echo $errors['email']; ?></div>
                        <?php endif; ?>
                    </div>

                    <div class="mb-3">
                        <label for="phone" class="form-label">Phone Number</label>
                        <input type="text" class="form-control <?php echo isset($errors['phone']) ? 'is-invalid' : ''; ?>" 
                               id="phone" name="phone" value="<?php echo htmlspecialchars($phone); ?>" 
                               placeholder="+1-234-567-8900">
                        <?php if (isset($errors['phone'])): ?>
                            <div class="invalid-feedback"><?php echo $errors['phone']; ?></div>
                        <?php endif; ?>
                    </div>

                    <div class="mb-3">
                        <label for="course" class="form-label">Course</label>
                        <select class="form-control" id="course" name="course">
                            <option value="">Select a course</option>
                            <option value="Computer Science" <?php echo ($course == 'Computer Science') ? 'selected' : ''; ?>>Computer Science</option>
                            <option value="Information Technology" <?php echo ($course == 'Information Technology') ? 'selected' : ''; ?>>Information Technology</option>
                            <option value="Software Engineering" <?php echo ($course == 'Software Engineering') ? 'selected' : ''; ?>>Software Engineering</option>
                            <option value="Data Science" <?php echo ($course == 'Data Science') ? 'selected' : ''; ?>>Data Science</option>
                            <option value="Cybersecurity" <?php echo ($course == 'Cybersecurity') ? 'selected' : ''; ?>>Cybersecurity</option>
                        </select>
                    </div>

                    <div class="d-grid gap-2 d-md-flex justify-content-md-end">
                        <a href="index.php" class="btn btn-secondary me-md-2">Cancel</a>
                        <button type="submit" class="btn btn-primary">Add Student</button>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>

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

Step 6: UPDATE Operation – Editing Records

Create update.php:

<?php
$page_title = "Edit Student";
include_once 'includes/header.php';
include_once 'config/database.php';

$database = new Database();
$db = $database->getConnection();

// Get student ID from URL
$student_id = isset($_GET['id']) ? (int)$_GET['id'] : 0;

if ($student_id <= 0) {
    echo "<div class='alert alert-danger'>Invalid student ID</div>";
    include_once 'includes/footer.php';
    exit;
}

// Initialize variables
$student = [];
$errors = [];
$success_message = '';

// Fetch student data
try {
    $query = "SELECT * FROM students WHERE id = :id";
    $stmt = $db->prepare($query);
    $stmt->bindParam(':id', $student_id);
    $stmt->execute();

    if ($stmt->rowCount() == 0) {
        echo "<div class='alert alert-danger'>Student not found</div>";
        include_once 'includes/footer.php';
        exit;
    }

    $student = $stmt->fetch();
} catch(PDOException $exception) {
    echo "<div class='alert alert-danger'>Error: " . $exception->getMessage() . "</div>";
    include_once 'includes/footer.php';
    exit;
}

// Process form submission
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // Get form data
    $first_name = trim($_POST['first_name']);
    $last_name = trim($_POST['last_name']);
    $email = trim($_POST['email']);
    $phone = trim($_POST['phone']);
    $course = trim($_POST['course']);

    // Validation
    if (empty($first_name)) {
        $errors['first_name'] = 'First name is required';
    }
    if (empty($last_name)) {
        $errors['last_name'] = 'Last name is required';
    }
    if (empty($email)) {
        $errors['email'] = 'Email is required';
    } elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $errors['email'] = 'Invalid email format';
    }
    if (!empty($phone) && !preg_match('/^[0-9+\-\s()]+$/', $phone)) {
        $errors['phone'] = 'Invalid phone number format';
    }

    // Check for duplicate email (excluding current student)
    if (empty($errors['email'])) {
        try {
            $check_query = "SELECT id FROM students WHERE email = :email AND id != :current_id";
            $check_stmt = $db->prepare($check_query);
            $check_stmt->bindParam(':email', $email);
            $check_stmt->bindParam(':current_id', $student_id);
            $check_stmt->execute();

            if ($check_stmt->rowCount() > 0) {
                $errors['email'] = 'Email already exists';
            }
        } catch(PDOException $exception) {
            $errors['database'] = 'Database error: ' . $exception->getMessage();
        }
    }

    // Update data if no errors
    if (empty($errors)) {
        try {
            $query = "UPDATE students SET 
                     first_name = :first_name, 
                     last_name = :last_name, 
                     email = :email, 
                     phone = :phone, 
                     course = :course,
                     updated_at = CURRENT_TIMESTAMP
                     WHERE id = :id";

            $stmt = $db->prepare($query);
            $stmt->bindParam(':first_name', $first_name);
            $stmt->bindParam(':last_name', $last_name);
            $stmt->bindParam(':email', $email);
            $stmt->bindParam(':phone', $phone);
            $stmt->bindParam(':course', $course);
            $stmt->bindParam(':id', $student_id);

            if ($stmt->execute()) {
                $success_message = 'Student updated successfully!';
                // Refresh student data
                $student['first_name'] = $first_name;
                $student['last_name'] = $last_name;
                $student['email'] = $email;
                $student['phone'] = $phone;
                $student['course'] = $course;
            }
        } catch(PDOException $exception) {
            $errors['database'] = 'Error updating student: ' . $exception->getMessage();
        }
    }
} else {
    // Use existing student data for form
    $first_name = $student['first_name'];
    $last_name = $student['last_name'];
    $email = $student['email'];
    $phone = $student['phone'];
    $course = $student['course'];
}
?>

<div class="row justify-content-center">
    <div class="col-md-8">
        <div class="card">
            <div class="card-header">
                <h3>Edit Student</h3>
            </div>
            <div class="card-body">
                <?php if (!empty($success_message)): ?>
                    <div class="alert alert-success alert-dismissible fade show" role="alert">
                        <?php echo $success_message; ?>
                        <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
                    </div>
                <?php endif; ?>

                <?php if (!empty($errors['database'])): ?>
                    <div class="alert alert-danger"><?php echo $errors['database']; ?></div>
                <?php endif; ?>

                <form method="POST" action="" novalidate>
                    <div class="row">
                        <div class="col-md-6 mb-3">
                            <label for="first_name" class="form-label">First Name *</label>
                            <input type="text" class="form-control <?php echo isset($errors['first_name']) ? 'is-invalid' : ''; ?>" 
                                   id="first_name" name="first_name" value="<?php echo htmlspecialchars($first_name); ?>" required>
                            <?php if (isset($errors['first_name'])): ?>
                                <div class="invalid-feedback"><?php echo $errors['first_name']; ?></div>
                            <?php endif; ?>
                        </div>

                        <div class="col-md-6 mb-3">
                            <label for="last_name" class="form-label">Last Name *</label>
                            <input type="text" class="form-control <?php echo isset($errors['last_name']) ? 'is-invalid' : ''; ?>" 
                                   id="last_name" name="last_name" value="<?php echo htmlspecialchars($last_name); ?>" required>
                            <?php if (isset($errors['last_name'])): ?>
                                <div class="invalid-feedback"><?php echo $errors['last_name']; ?></div>
                            <?php endif; ?>
                        </div>
                    </div>

                    <div class="mb-3">
                        <label for="email" class="form-label">Email Address *</label>
                        <input type="email" class="form-control <?php echo isset($errors['email']) ? 'is-invalid' : ''; ?>" 
                               id="email" name="email" value="<?php echo htmlspecialchars($email); ?>" required>
                        <?php if (isset($errors['email'])): ?>
                            <div class="invalid-feedback"><?php echo $errors['email']; ?></div>
                        <?php endif; ?>
                    </div>

                    <div class="mb-3">
                        <label for="phone" class="form-label">Phone Number</label>
                        <input type="text" class="form-control <?php echo isset($errors['phone']) ? 'is-invalid' : ''; ?>" 
                               id="phone" name="phone" value="<?php echo htmlspecialchars($phone); ?>" 
                               placeholder="+1-234-567-8900">
                        <?php if (isset($errors['phone'])): ?>
                            <div class="invalid-feedback"><?php echo $errors['phone']; ?></div>
                        <?php endif; ?>
                    </div>

                    <div class="mb-3">
                        <label for="course" class="form-label">Course</label>
                        <select class="form-control" id="course" name="course">
                            <option value="">Select a course</option>
                            <option value="Computer Science" <?php echo ($course == 'Computer Science') ? 'selected' : ''; ?>>Computer Science</option>
                            <option value="Information Technology" <?php echo ($course == 'Information Technology') ? 'selected' : ''; ?>>Information Technology</option>
                            <option value="Software Engineering" <?php echo ($course == 'Software Engineering') ? 'selected' : ''; ?>>Software Engineering</option>
                            <option value="Data Science" <?php echo ($course == 'Data Science') ? 'selected' : ''; ?>>Data Science</option>
                            <option value="Cybersecurity" <?php echo ($course == 'Cybersecurity') ? 'selected' : ''; ?>>Cybersecurity</option>
                        </select>
                    </div>

                    <div class="d-grid gap-2 d-md-flex justify-content-md-end">
                        <a href="index.php" class="btn btn-secondary me-md-2">Cancel</a>
                        <a href="read.php?id=<?php echo $student_id; ?>" class="btn btn-info me-md-2">View</a>
                        <button type="submit" class="btn btn-primary">Update Student</button>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>

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

Step 7: DELETE Operation – Removing Records

Create delete.php:

<?php
$page_title = "Delete Student";
include_once 'includes/header.php';
include_once 'config/database.php';

$database = new Database();
$db = $database->getConnection();

// Get student ID from URL
$student_id = isset($_GET['id']) ? (int)$_GET['id'] : 0;

if ($student_id <= 0) {
    echo "<div class='alert alert-danger'>Invalid student ID</div>";
    include_once 'includes/footer.php';
    exit;
}

// Initialize variables
$student = [];
$error_message = '';
$success_message = '';

// Fetch student data
try {
    $query = "SELECT * FROM students WHERE id = :id";
    $stmt = $db->prepare($query);
    $stmt->bindParam(':id', $student_id);
    $stmt->execute();

    if ($stmt->rowCount() == 0) {
        echo "<div class='alert alert-danger'>Student not found</div>";
        include_once 'includes/footer.php';
        exit;
    }

    $student = $stmt->fetch();
} catch(PDOException $exception) {
    $error_message = "Error: " . $exception->getMessage();
}

// Process deletion
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['confirm_delete'])) {
    try {
        $delete_query = "DELETE FROM students WHERE id = :id";
        $delete_stmt = $db->prepare($delete_query);
        $delete_stmt->bindParam(':id', $student_id);

        if ($delete_stmt->execute()) {
            $success_message = 'Student deleted successfully!';
        }
    } catch(PDOException $exception) {
        $error_message = 'Error deleting student: ' . $exception->getMessage();
    }
}
?>

<div class="row justify-content-center">
    <div class="col-md-8">
        <div class="card">
            <div class="card-header bg-danger text-white">
                <h3>Delete Student</h3>
            </div>
            <div class="card-body">
                <?php if (!empty($error_message)): ?>
                    <div class="alert alert-danger"><?php echo $error_message; ?></div>
                    <a href="index.php" class="btn btn-primary">Back to List</a>
                <?php elseif (!empty($success_message)): ?>
                    <div class="alert alert-success"><?php echo $success_message; ?></div>
                    <a href="index.php" class="btn btn-primary">Back to List</a>
                <?php else: ?>
                    <div class="alert alert-warning">
                        <strong>Warning:</strong> You are about to delete this student record. This action cannot be undone.
                    </div>

                    <div class="card mb-3">
                        <div class="card-body">
                            <h5 class="card-title"><?php echo htmlspecialchars($student['first_name'] . ' ' . $student['last_name']); ?></h5>
                            <div class="row">
                                <div class="col-md-6">
                                    <p><strong>Email:</strong> <?php echo htmlspecialchars($student['email']); ?></p>
                                    <p><strong>Phone:</strong> <?php echo htmlspecialchars($student['phone'] ?: 'N/A'); ?></p>
                                </div>
                                <div class="col-md-6">
                                    <p><strong>Course:</strong> <?php echo htmlspecialchars($student['course'] ?: 'N/A'); ?></p>
                                    <p><strong>Enrollment Date:</strong> <?php echo date('M d, Y', strtotime($student['enrollment_date'])); ?></p>
                                </div>
                            </div>
                        </div>
                    </div>

                    <form method="POST" action="">
                        <div class="d-grid gap-2 d-md-flex justify-content-md-end">
                            <a href="index.php" class="btn btn-secondary me-md-2">Cancel</a>
                            <a href="read.php?id=<?php echo $student_id; ?>" class="btn btn-info me-md-2">View Details</a>
                            <button type="submit" name="confirm_delete" class="btn btn-danger" 
                                    onclick="return confirm('Are you absolutely sure you want to delete this student?')">
                                Delete Student
                            </button>
                        </div>
                    </form>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

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

Step 8: Individual Record View

Create read.php:

<?php
$page_title = "Student Details";
include_once 'includes/header.php';
include_once 'config/database.php';

$database = new Database();
$db = $database->getConnection();

// Get student ID from URL
$student_id = isset($_GET['id']) ? (int)$_GET['id'] : 0;

if ($student_id <= 0) {
    echo "<div class='alert alert-danger'>Invalid student ID</div>";
    include_once 'includes/footer.php';
    exit;
}

// Initialize variables
$student = [];
$error_message = '';

// Fetch student data
try {
    $query = "SELECT * FROM students WHERE id = :id";
    $stmt = $db->prepare($query);
    $stmt->bindParam(':id', $student_id);
    $stmt->execute();

    if ($stmt->rowCount() == 0) {
        echo "<div class='alert alert-danger'>Student not found</div>";
        include_once 'includes/footer.php';
        exit;
    }

    $student = $stmt->fetch();
} catch(PDOException $exception) {
    $error_message = "Error: " . $exception->getMessage();
}
?>

<div class="row justify-content-center">
    <div class="col-md-8">
        <?php if (!empty($error_message)): ?>
            <div class="alert alert-danger"><?php echo $error_message; ?></div>
        <?php else: ?>
            <div class="card">
                <div class="card-header">
                    <h3>Student Details</h3>
                </div>
                <div class="card-body">
                    <div class="row">
                        <div class="col-md-6">
                            <h4><?php echo htmlspecialchars($student['first_name'] . ' ' . $student['last_name']); ?></h4>
                            <hr>
                            <p><strong>Student ID:</strong> #<?php echo $student['id']; ?></p>
                            <p><strong>Email:</strong> 
                                <a href="mailto:<?php echo htmlspecialchars($student['email']); ?>">
                                    <?php echo htmlspecialchars($student['email']); ?>
                                </a>
                            </p>
                            <p><strong>Phone:</strong> 
                                <?php if (!empty($student['phone'])): ?>
                                    <a href="tel:<?php echo htmlspecialchars($student['phone']); ?>">
                                        <?php echo htmlspecialchars($student['phone']); ?>
                                    </a>
                                <?php else: ?>
                                    <span class="text-muted">Not provided</span>
                                <?php endif; ?>
                            </p>
                        </div>
                        <div class="col-md-6">
                            <p><strong>Course:</strong> 
                                <?php echo !empty($student['course']) ? htmlspecialchars($student['course']) : '<span class="text-muted">Not assigned</span>'; ?>
                            </p>
                            <p><strong>Enrollment Date:</strong> <?php echo date('F d, Y', strtotime($student['enrollment_date'])); ?></p>
                            <p><strong>Record Created:</strong> <?php echo date('F d, Y g:i A', strtotime($student['created_at'])); ?></p>
                            <p><strong>Last Updated:</strong> <?php echo date('F d, Y g:i A', strtotime($student['updated_at'])); ?></p>
                        </div>
                    </div>

                    <hr>

                    <div class="d-grid gap-2 d-md-flex justify-content-md-end">
                        <a href="index.php" class="btn btn-secondary me-md-2">Back to List</a>
                        <a href="update.php?id=<?php echo $student['id']; ?>" class="btn btn-warning me-md-2">Edit Student</a>
                        <a href="delete.php?id=<?php echo $student['id']; ?>" class="btn btn-danger" 
                           onclick="return confirm('Are you sure you want to delete this student?')">Delete Student</a>
                    </div>
                </div>
            </div>
        <?php endif; ?>
    </div>
</div>

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

Step 9: Adding Custom Styling

Create css/style.css:

/* Custom styles for Student Management System */

:root {
    --primary-color: #0d6efd;
    --secondary-color: #6c757d;
    --success-color: #198754;
    --danger-color: #dc3545;
    --warning-color: #ffc107;
    --info-color: #0dcaf0;
}

body {
    background-color: #f8f9fa;
    font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
}

.navbar-brand {
    font-weight: bold;
    font-size: 1.5rem;
}

.card {
    box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
    border: 1px solid rgba(0, 0, 0, 0.125);
    border-radius: 0.375rem;
}

.card-header {
    background-color: #fff;
    border-bottom: 1px solid rgba(0, 0, 0, 0.125);
    font-weight: 600;
}

.table {
    background-color: #fff;
}

.table th {
    border-top: none;
    font-weight: 600;
    text-transform: uppercase;
    font-size: 0.875rem;
    letter-spacing: 0.5px;
}

.btn {
    border-radius: 0.375rem;
    font-weight: 500;
    transition: all 0.15s ease-in-out;
}

.btn:hover {
    transform: translateY(-1px);
    box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.15);
}

.btn-sm {
    font-size: 0.8125rem;
    padding: 0.25rem 0.5rem;
}

.alert {
    border-radius: 0.375rem;
    border: none;
}

.form-control, .form-select {
    border-radius: 0.375rem;
    border: 1px solid #ced4da;
    transition: border-color 0.15s ease-in-out, box-shadow 0.15s ease-in-out;
}

.form-control:focus, .form-select:focus {
    border-color: var(--primary-color);
    box-shadow: 0 0 0 0.2rem rgba(13, 110, 253, 0.25);
}

.pagination .page-link {
    color: var(--primary-color);
    border-radius: 0.375rem;
    margin: 0 0.125rem;
    border: 1px solid #dee2e6;
}

.pagination .page-item.active .page-link {
    background-color: var(--primary-color);
    border-color: var(--primary-color);
}

.table-responsive {
    border-radius: 0.375rem;
    box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
}

/* Responsive improvements */
@media (max-width: 768px) {
    .card {
        margin: 0 10px;
    }

    .table-responsive {
        font-size: 0.875rem;
    }

    .btn-sm {
        font-size: 0.75rem;
        padding: 0.2rem 0.4rem;
        margin: 0.1rem;
    }

    .d-md-flex .btn {
        margin-bottom: 0.5rem;
        width: 100%;
    }
}

/* Loading and animation effects */
.fade-in {
    animation: fadeIn 0.5s ease-in;
}

@keyframes fadeIn {
    from { opacity: 0; transform: translateY(20px); }
    to { opacity: 1; transform: translateY(0); }
}

/* Search form styling */
.search-form {
    background-color: #fff;
    padding: 1.5rem;
    border-radius: 0.375rem;
    box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
    margin-bottom: 1.5rem;
}

/* Status badges */
.status-badge {
    padding: 0.25rem 0.5rem;
    border-radius: 0.375rem;
    font-size: 0.75rem;
    font-weight: 600;
    text-transform: uppercase;
}

.status-active {
    background-color: #d1e7dd;
    color: #0f5132;
}

.status-inactive {
    background-color: #f8d7da;
    color: #842029;
}

Step 10: Security Best Practices

Input Validation and Sanitization

Throughout our application, we’ve implemented several security measures:

Prepared Statements: All database queries use PDO prepared statements to prevent SQL injection
Input Validation: Server-side validation for all form inputs
XSS Prevention: Using htmlspecialchars() to escape output
CSRF Protection: Consider adding CSRF tokens for production use

Additional Security Enhancements

Create a security helper file includes/security.php:

<?php
class Security {

    // Generate CSRF token
    public static function generateCSRFToken() {
        if (!isset($_SESSION['csrf_token'])) {
            $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
        }
        return $_SESSION['csrf_token'];
    }

    // Verify CSRF token
    public static function verifyCSRFToken($token) {
        return isset($_SESSION['csrf_token']) && hash_equals($_SESSION['csrf_token'], $token);
    }

    // Sanitize input data
    public static function sanitizeInput($input) {
        return htmlspecialchars(trim($input), ENT_QUOTES, 'UTF-8');
    }

    // Validate email
    public static function validateEmail($email) {
        return filter_var($email, FILTER_VALIDATE_EMAIL) !== false;
    }

    // Validate phone number
    public static function validatePhone($phone) {
        return preg_match('/^[0-9+\-\s()]+$/', $phone);
    }
}
?>

Step 11: Error Handling and Logging

Create includes/error_handler.php:

<?php
class ErrorHandler {

    private static $logFile = 'logs/error.log';

    public static function logError($message, $file = '', $line = '') {
        $timestamp = date('Y-m-d H:i:s');
        $logMessage = "[$timestamp] Error: $message";

        if ($file && $line) {
            $logMessage .= " in $file on line $line";
        }

        $logMessage .= PHP_EOL;

        // Ensure logs directory exists
        if (!file_exists('logs')) {
            mkdir('logs', 0755, true);
        }

        file_put_contents(self::$logFile, $logMessage, FILE_APPEND | LOCK_EX);
    }

    public static function displayUserError($message) {
        return "<div class='alert alert-danger' role='alert'>$message</div>";
    }

    public static function displaySuccess($message) {
        return "<div class='alert alert-success alert-dismissible fade show' role='alert'>
                    $message
                    <button type='button' class='btn-close' data-bs-dismiss='alert'></button>
                </div>";
    }
}
?>

Step 12: Advanced Features

Export Functionality

Add to index.php after the search form:

<!-- Export Options -->
<div class="row mb-3">
    <div class="col-md-12 text-end">
        <a href="export.php?format=csv" class="btn btn-outline-success btn-sm">Export CSV</a>
        <a href="export.php?format=excel" class="btn btn-outline-primary btn-sm">Export Excel</a>
    </div>
</div>

Create export.php:

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

$database = new Database();
$db = $database->getConnection();

$format = isset($_GET['format']) ? $_GET['format'] : 'csv';

try {
    $query = "SELECT id, first_name, last_name, email, phone, course, enrollment_date FROM students ORDER BY created_at DESC";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $students = $stmt->fetchAll();

    if ($format === 'csv') {
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="students_' . date('Y-m-d') . '.csv"');

        $output = fopen('php://output', 'w');

        // Headers
        fputcsv($output, ['ID', 'First Name', 'Last Name', 'Email', 'Phone', 'Course', 'Enrollment Date']);

        // Data
        foreach ($students as $student) {
            fputcsv($output, [
                $student['id'],
                $student['first_name'],
                $student['last_name'],
                $student['email'],
                $student['phone'],
                $student['course'],
                date('Y-m-d', strtotime($student['enrollment_date']))
            ]);
        }

        fclose($output);
    }

} catch(PDOException $exception) {
    die('Export error: ' . $exception->getMessage());
}
?>

Bulk Operations

Add bulk delete functionality to index.php:

<!-- Add this JavaScript before closing body tag -->
<script>
function toggleSelectAll() {
    const selectAll = document.getElementById('selectAll');
    const checkboxes = document.querySelectorAll('.student-checkbox');

    checkboxes.forEach(checkbox => {
        checkbox.checked = selectAll.checked;
    });

    toggleBulkActions();
}

function toggleBulkActions() {
    const checkedBoxes = document.querySelectorAll('.student-checkbox:checked');
    const bulkActions = document.getElementById('bulkActions');

    if (checkedBoxes.length > 0) {
        bulkActions.style.display = 'block';
    } else {
        bulkActions.style.display = 'none';
    }
}

function bulkDelete() {
    const checkedBoxes = document.querySelectorAll('.student-checkbox:checked');
    const ids = Array.from(checkedBoxes).map(cb => cb.value);

    if (confirm(`Are you sure you want to delete ${ids.length} students?`)) {
        const form = document.createElement('form');
        form.method = 'POST';
        form.action = 'bulk_operations.php';

        const actionInput = document.createElement('input');
        actionInput.type = 'hidden';
        actionInput.name = 'action';
        actionInput.value = 'delete';
        form.appendChild(actionInput);

        const idsInput = document.createElement('input');
        idsInput.type = 'hidden';
        idsInput.name = 'ids';
        idsInput.value = JSON.stringify(ids);
        form.appendChild(idsInput);

        document.body.appendChild(form);
        form.submit();
    }
}
</script>

Performance Optimization Tips

Database Optimization

Add Indexes: Create indexes on frequently searched columns

ALTER TABLE students ADD INDEX idx_email (email);
ALTER TABLE students ADD INDEX idx_name (first_name, last_name);
ALTER TABLE students ADD INDEX idx_course (course);

Query Optimization: Use LIMIT and proper WHERE clauses
Connection Pooling: Consider implementing connection pooling for high-traffic applications

Caching Strategies

Query Result Caching: Cache frequently accessed data
Page Caching: Implement full page caching for static content
Object Caching: Use Redis or Memcached for session storage

Testing Your Application

Manual Testing Checklist

Create Operation:

Test with valid data
Test with invalid email formats
Test with duplicate emails
Test with missing required fields

Read Operation:

Test pagination with different page sizes
Test search functionality
Test with empty database

Update Operation:

Test updating all fields
Test partial updates
Test email uniqueness validation

Delete Operation:

Test single record deletion
Test with non-existent IDs
Test bulk deletion

Security Testing

SQL Injection: Try malicious input in forms
XSS: Test with script tags in input fields
CSRF: Test form submissions without proper tokens

Deployment Considerations

Production Checklist

Environment Configuration:

Use environment variables for database credentials
Enable error logging, disable display_errors
Set appropriate file permissions

Security Headers:

// Add to the top of header.php
header('X-Content-Type-Options: nosniff');
header('X-Frame-Options: DENY');
header('X-XSS-Protection: 1; mode=block');

SSL Configuration: Ensure HTTPS is enabled
Database Security:

Use strong passwords
Limit database user permissions
Enable query logging

Troubleshooting Common Issues

Database Connection Problems

Check credentials: Verify database username, password, and host
PHP Extensions: Ensure PDO MySQL extension is installed
Firewall: Check if database port is accessible

Form Validation Issues

JavaScript Disabled: Always implement server-side validation
Character Encoding: Use UTF-8 consistently
File Uploads: Set proper file size limits and validation

Performance Issues

Query Optimization: Use EXPLAIN to analyze slow queries
Image Optimization: Compress images and use appropriate formats
Code Profiling: Use Xdebug or similar tools to identify bottlenecks

Conclusion

Congratulations! You’ve successfully built a complete PHP CRUD application with MySQL. This tutorial covered:

Setting up a secure database connection using PDO
Implementing all four CRUD operations with proper error handling
Creating a responsive user interface with Bootstrap
Adding search and pagination functionality
Implementing security best practices
Performance optimization techniques

Next Steps

To further enhance your application, consider adding:

User Authentication: Implement login/logout functionality
Role-Based Access: Different permissions for different users
File Uploads: Allow students to upload profile pictures
API Development: Create RESTful APIs for mobile apps
Advanced Reporting: Generate detailed reports and analytics

Key Takeaways

Always use prepared statements for database queries
Implement proper input validation and sanitization
Follow the MVC pattern for better code organization
Use responsive design for better user experience
Implement comprehensive error handling and logging

This solid foundation will help you build more complex web applications with confidence. Remember to keep learning and stay updated with the latest PHP and security best practices!

PHP CRUD Tutorial with MySQL: A Complete Step-by-Step Guide

Introduction

What You’ll Learn

Prerequisites

Step 1: Environment Setup

Database Configuration

Project Structure

Step 2: Database Connection

Step 3: Creating the User Interface

Header Template

Footer Template

Step 4: READ Operation – Displaying Data

Step 5: CREATE Operation – Adding New Records

Step 6: UPDATE Operation – Editing Records

Step 7: DELETE Operation – Removing Records

Step 8: Individual Record View

Step 9: Adding Custom Styling

Step 10: Security Best Practices

Input Validation and Sanitization

Additional Security Enhancements

Step 11: Error Handling and Logging

Step 12: Advanced Features

Export Functionality

Bulk Operations

Performance Optimization Tips

Database Optimization

Caching Strategies

Testing Your Application

Manual Testing Checklist

Security Testing

Deployment Considerations

Production Checklist

Troubleshooting Common Issues

Database Connection Problems

Form Validation Issues

Performance Issues

Conclusion

Next Steps

Key Takeaways

Leave a Comment Cancel Reply

Introduction

What You’ll Learn

Prerequisites

Step 1: Environment Setup

Database Configuration

Project Structure

Step 2: Database Connection

Step 3: Creating the User Interface

Header Template

Footer Template

Step 4: READ Operation – Displaying Data

Step 5: CREATE Operation – Adding New Records

Step 6: UPDATE Operation – Editing Records

Step 7: DELETE Operation – Removing Records

Step 8: Individual Record View

Step 9: Adding Custom Styling

Step 10: Security Best Practices

Input Validation and Sanitization

Additional Security Enhancements

Step 11: Error Handling and Logging

Step 12: Advanced Features

Export Functionality

Bulk Operations

Performance Optimization Tips

Database Optimization

Caching Strategies

Testing Your Application

Manual Testing Checklist

Security Testing

Deployment Considerations

Production Checklist

Troubleshooting Common Issues

Database Connection Problems

Form Validation Issues

Performance Issues

Conclusion

Next Steps

Key Takeaways

Leave a Comment Cancel Reply

Related Posts