How to CRUD using php8 with MySQL Database

phatvloger
11 Jun, 2025


0

 I'll create a complete CRUD application for managing students using PHP 8 and MySQL, based on the context of your previous requests. The application will use PDO for database interactions, Bootstrap 5 for the frontend, and follow object-oriented principles with strict typing. It will include a responsive UI, client-side and server-side validation, and secure API endpoints.







The artifacts will include:

  • index.php: The main frontend file with the UI and client-side logic.
  • api/students.php: The backend API handling CRUD operations.
  • database.sql: The database schema.

    • <?php
    require_once 'api/students.php';

    function fetchStudents(): array {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, 'http://' . $_SERVER['HTTP_HOST'] . '/api/students.php');
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        $response = curl_exec($ch);
        curl_close($ch);
        return json_decode($response, true) ?: [];
    }
    ?>

    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Student Management System</title>
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
        <style>
            .toast-container {
                position: fixed;
                bottom: 1rem;
                right: 1rem;
            }
            .modal-backdrop {
                z-index: 1040 !important;
            }
            .modal {
                z-index: 1050 !important;
            }
            .table-responsive {
                overflow-x: auto;
            }
        </style>
    </head>
    <body class="bg-light">
        <!-- Main Container -->
        <div class="container py-5">
            <!-- Header -->
            <div class="d-flex justify-content-between align-items-center mb-4">
                <h1 class="h3 fw-bold">Student Management</h1>
                <button class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#studentModal" onclick="openCreateModal()">Add Student</button>
            </div>

            <!-- Students Table -->
            <div class="card shadow-sm">
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-hover">
                            <thead>
                                <tr>
                                    <th>ID</th>
                                    <th>Name</th>
                                    <th>Email</th>
                                    <th>Phone</th>
                                    <th>Date of Birth</th>
                                    <th>Address</th>
                                    <th>Actions</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                $response = fetchStudents();
                                if ($response['success'] && is_array($response['data']) && !empty($response['data'])):
                                    foreach ($response['data'] as $student):
                                ?>
                                    <tr>
                                        <td><?php echo htmlspecialchars((string)$student['id'], ENT_QUOTES, 'UTF-8'); ?></td>
                                        <td><?php echo htmlspecialchars($student['first_name'] . ' ' . $student['last_name'], ENT_QUOTES, 'UTF-8'); ?></td>
                                        <td><?php echo htmlspecialchars($student['email'], ENT_QUOTES, 'UTF-8'); ?></td>
                                        <td><?php echo htmlspecialchars($student['phone'], ENT_QUOTES, 'UTF-8'); ?></td>
                                        <td><?php echo htmlspecialchars($student['date_of_birth'], ENT_QUOTES, 'UTF-8'); ?></td>
                                        <td><?php echo htmlspecialchars(substr($student['address'], 0, 30), ENT_QUOTES, 'UTF-8') . (strlen($student['address']) > 30 ? '...' : ''); ?></td>
                                        <td>
                                            <button class="btn btn-sm btn-warning me-1" onclick='openEditModal(<?php echo json_encode($student, JSON_HEX_APOS | JSON_HEX_QUOT); ?>)'>Edit</button>
                                            <button class="btn btn-sm btn-danger" data-bs-toggle="modal" data-bs-target="#deleteModal" onclick="setDeleteId(<?php echo (int)$student['id']; ?>)">Delete</button>
                                        </td>
                                    </tr>
                                <?php
                                    endforeach;
                                else:
                                ?>
                                    <tr><td colspan="7" class="text-center">No students found</td></tr>
                                <?php endif; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>

        <!-- Create/Edit Modal -->
        <div class="modal fade" id="studentModal" tabindex="-1" aria-labelledby="studentModalLabel" aria-hidden="true">
            <div class="modal-dialog">
                <div class="modal-content">
                    <div class="modal-header">
                        <h5 class="modal-title" id="studentModalLabel">Add Student</h5>
                        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                    </div>
                    <form id="studentForm" method="POST">
                        <div class="modal-body">
                            <input type="hidden" name="id" id="studentId">
                            <div class="mb-3">
                                <label for="first_name" class="form-label">First Name</label>
                                <input type="text" class="form-control" id="first_name" name="first_name" required maxlength="50">
                            </div>
                            <div class="mb-3">
                                <label for="last_name" class="form-label">Last Name</label>
                                <input type="text" class="form-control" id="last_name" name="last_name" required maxlength="50">
                            </div>
                            <div class="mb-3">
                                <label for="email" class="form-label">Email</label>
                                <input type="email" class="form-control" id="email" name="email" required>
                            </div>
                            <div class="mb-3">
                                <label for="phone" class="form-label">Phone</label>
                                <input type="tel" class="form-control" id="phone" name="phone" required pattern="\+?[1-9]\d{1,14}">
                            </div>
                            <div class="mb-3">
                                <label for="date_of_birth" class="form-label">Date of Birth</label>
                                <input type="date" class="form-control" id="date_of_birth" name="date_of_birth" required>
                            </div>
                            <div class="mb-3">
                                <label for="address" class="form-label">Address</label>
                                <textarea class="form-control" id="address" name="address" required maxlength="255"></textarea>
                            </div>
                            <div id="formError" class="text-danger d-none"></div>
                        </div>
                        <div class="modal-footer">
                            <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                            <button type="submit" class="btn btn-primary" id="submitButton">Create</button>
                        </div>
                    </form>
                </div>
            </div>
        </div>

        <!-- Delete Confirmation Modal -->
        <div class="modal fade" id="deleteModal" tabindex="-1" aria-labelledby="deleteModalLabel" aria-hidden="true">
            <div class="modal-dialog">
                <div class="modal-content">
                    <div class="modal-header">
                        <h5 class="modal-title" id="deleteModalLabel">Confirm Deletion</h5>
                        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                    </div>
                    <div class="modal-body">
                        Are you sure you want to delete this student?
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                        <button type="button" class="btn btn-danger" onclick="deleteStudent()">Delete</button>
                    </div>
                </div>
            </div>
        </div>

        <!-- Toast Container -->
        <div class="toast-container">
            <div id="toast" class="toast align-items-center text-white" role="alert" aria-live="assertive" aria-atomic="true">
                <div class="d-flex">
                    <div class="toast-body"></div>
                    <button type="button" class="btn-close btn-close-white me-2 m-auto" data-bs-dismiss="toast" aria-label="Close"></button>
                </div>
            </div>
        </div>

        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
        <script>
            let isEdit = false;
            let deleteId = null;
            const studentModal = new bootstrap.Modal(document.getElementById('studentModal'));
            const deleteModal = new bootstrap.Modal(document.getElementById('deleteModal'));
            const toastEl = document.getElementById('toast');
            const toast = new bootstrap.Toast(toastEl);

            function openCreateModal() {
                isEdit = false;
                document.getElementById('studentModalLabel').textContent = 'Add Student';
                document.getElementById('submitButton').textContent = 'Create';
                document.getElementById('studentForm').reset();
                document.getElementById('formError').classList.add('d-none');
                studentModal.show();
            }

            function openEditModal(student) {
                isEdit = true;
                document.getElementById('studentModalLabel').textContent = 'Edit Student';
                document.getElementById('submitButton').textContent = 'Update';
                document.getElementById('formError').classList.add('d-none');

                document.getElementById('studentId').value = student.id;
                document.getElementById('first_name').value = student.first_name;
                document.getElementById('last_name').value = student.last_name;
                document.getElementById('email').value = student.email;
                document.getElementById('phone').value = student.phone;
                document.getElementById('date_of_birth').value = student.date_of_birth;
                document.getElementById('address').value = student.address;

                studentModal.show();
            }

            function setDeleteId(id) {
                deleteId = id;
            }

            document.getElementById('studentForm').addEventListener('submit', async (e) => {
                e.preventDefault();
                const form = e.target;
                const formData = new FormData(form);
                const data = Object.fromEntries(formData);
                const url = isEdit ? '/api/students.php' : '/api/students.php';
                const method = isEdit ? 'PUT' : 'POST';

                try {
                    const response = await fetch(url, {
                        method: method,
                        headers: { 'Content-Type': 'application/json' },
                        body: JSON.stringify(data)
                    });
                    const result = await response.json();

                    if (result.success) {
                        studentModal.hide();
                        showToast(result.message, 'success');
                        setTimeout(() => location.reload(), 1000);
                    } else {
                        document.getElementById('formError').textContent = result.message;
                        document.getElementById('formError').classList.remove('d-none');
                    }
                } catch (error) {
                    showToast('Server error: ' + error.message, 'danger');
                }
            });

            async function deleteStudent() {
                if (!deleteId) return;

                try {
                    const response = await fetch(`/api/students.php?id=${deleteId}`, {
                        method: 'DELETE'
                    });
                    const result = await response.json();

                    if (result.success) {
                        deleteModal.hide();
                        showToast(result.message, 'success');
                        setTimeout(() => location.reload(), 1000);
                    } else {
                        showToast(result.message, 'danger');
                    }
                } catch (error) {
                    showToast('Server error: ' + error.message, 'danger');
                }
            }

            function showToast(message, type) {
                toastEl.classList.remove('bg-success', 'bg-danger');
                toastEl.classList.add(`bg-${type}`);
                toastEl.querySelector('.toast-body').textContent = message;
                toast.show();
            }
        </script>
    </body>
    </html>

    api/students.php


    <?php
    declare(strict_types=1);

    final class Database {
        private const HOST = 'localhost';
        private const DBNAME = 'bbu_crud';
        private const USERNAME = 'root';
        private const PASSWORD = '';
        private PDO $pdo;

        public function __construct() {
            try {
                $this->pdo = new PDO(
                    sprintf('mysql:host=%s;dbname=%s;charset=utf8mb4', self::HOST, self::DBNAME),
                    self::USERNAME,
                    self::PASSWORD,
                    [
                        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                        PDO::ATTR_EMULATE_PREPARES => false,
                        PDO::ATTR_STRINGIFY_FETCHES => false
                    ]
                );
            } catch (PDOException $e) {
                throw new RuntimeException('Database connection failed: ' . $e->getMessage());
            }
        }

        public function getConnection(): PDO {
            return $this->pdo;
        }
    }

    final class Student {
        private PDO $pdo;
        private const TABLE = 'students';

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

        public function create(array $data): int {
            $sql = "INSERT INTO " . self::TABLE . " (
                first_name, last_name, email, phone, date_of_birth, address
            ) VALUES (
                :first_name, :last_name, :email, :phone, :date_of_birth, :address
            )";

            try {
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute([
                    ':first_name' => $data['first_name'],
                    ':last_name' => $data['last_name'],
                    ':email' => $data['email'],
                    ':phone' => $data['phone'],
                    ':date_of_birth' => $data['date_of_birth'],
                    ':address' => $data['address']
                ]);
                return (int)$this->pdo->lastInsertId();
            } catch (PDOException $e) {
                throw new RuntimeException('Create failed: ' . $e->getMessage());
            }
        }

        public function read(?int $id = null): array|bool {
            try {
                if ($id !== null) {
                    $sql = "SELECT * FROM " . self::TABLE . " WHERE id = :id";
                    $stmt = $this->pdo->prepare($sql);
                    $stmt->execute([':id' => $id]);
                    return $stmt->fetch() ?: false;
                }

                $sql = "SELECT * FROM " . self::TABLE . " ORDER BY last_name, first_name";
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute();
                return $stmt->fetchAll();
            } catch (PDOException $e) {
                throw new RuntimeException('Read failed: ' . $e->getMessage());
            }
        }

        public function update(int $id, array $data): bool {
            $sql = "UPDATE " . self::TABLE . " SET
                first_name = :first_name,
                last_name = :last_name,
                email = :email,
                phone = :phone,
                date_of_birth = :date_of_birth,
                address = :address,
                updated_at = CURRENT_TIMESTAMP
                WHERE id = :id";

            try {
                $stmt = $this->pdo->prepare($sql);
                return $stmt->execute([
                    ':id' => $id,
                    ':first_name' => $data['first_name'],
                    ':last_name' => $data['last_name'],
                    ':email' => $data['email'],
                    ':phone' => $data['phone'],
                    ':date_of_birth' => $data['date_of_birth'],
                    ':address' => $data['address']
                ]);
            } catch (PDOException $e) {
                throw new RuntimeException('Update failed: ' . $e->getMessage());
            }
        }

        public function delete(int $id): bool {
            $sql = "DELETE FROM " . self::TABLE . " WHERE id = :id";

            try {
                $stmt = $this->pdo->prepare($sql);
                return $stmt->execute([':id' => $id]);
            } catch (PDOException $e) {
                throw new RuntimeException('Delete failed: ' . $e->getMessage());
            }
        }

        public function validate(array $data): array {
            $errors = [];

            if (empty($data['first_name']) || strlen($data['first_name']) > 50) {
                $errors[] = 'First name is required and must be less than 50 characters';
            }

            if (empty($data['last_name']) || strlen($data['last_name']) > 50) {
                $errors[] = 'Last name is required and must be less than 50 characters';
            }

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

            if (empty($data['phone']) || !preg_match('/^\+?[1-9]\d{1,14}$/', $data['phone'])) {
                $errors[] = 'Valid phone number is required';
            }

            if (empty($data['date_of_birth']) || !$this->isValidDate($data['date_of_birth'])) {
                $errors[] = 'Valid date of birth (YYYY-MM-DD) is required';
            }

            if (empty($data['address']) || strlen($data['address']) > 255) {
                $errors[] = 'Address is required and must be less than 255 characters';
            }

            return $errors;
        }

        private function isValidDate(string $date): bool {
            if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
                return false;
            }

            try {
                $d = new DateTime($date);
                return $d && $d->format('Y-m-d') === $date;
            } catch (Exception) {
                return false;
            }
        }
    }

    final class StudentController {
        private Student $student;

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

        public function handleRequest(): void {
            $response = ['success' => false, 'message' => '', 'data' => null];

            try {
                switch ($_SERVER['REQUEST_METHOD']) {
                    case 'POST':
                        $data = $this->getRequestData();
                        $errors = $this->student->validate($data);
                        if (!empty($errors)) {
                            $response['message'] = implode(', ', $errors);
                            break;
                        }
                        $id = $this->student->create($data);
                        $response = [
                            'success' => true,
                            'message' => 'Student created successfully',
                            'data' => ['id' => $id]
                        ];
                        break;

                    case 'GET':
                        $id = isset($_GET['id']) ? (int)$_GET['id'] : null;
                        $data = $this->student->read($id);
                        if ($data === false || (is_array($data) && empty($data))) {
                            $response['message'] = 'Student not found';
                            break;
                        }
                        $response = [
                            'success' => true,
                            'message' => 'Student data retrieved successfully',
                            'data' => $data
                        ];
                        break;

                    case 'PUT':
                        $data = $this->getRequestData();
                        $id = isset($data['id']) ? (int)$data['id'] : null;
                        if ($id === null) {
                            $response['message'] = 'Student ID is required';
                            break;
                        }
                        $errors = $this->student->validate($data);
                        if (!empty($errors)) {
                            $response['message'] = implode(', ', $errors);
                            break;
                        }
                        if ($this->student->update($id, $data)) {
                            $response = [
                                'success' => true,
                                'message' => 'Student updated successfully'
                            ];
                        } else {
                            $response['message'] = 'Student not found';
                        }
                        break;

                    case 'DELETE':
                        $id = isset($_GET['id']) ? (int)$_GET['id'] : null;
                        if ($id === null) {
                            $response['message'] = 'Student ID is required';
                            break;
                        }
                        if ($this->student->delete($id)) {
                            $response = [
                                'success' => true,
                                'message' => 'Student deleted successfully'
                            ];
                        } else {
                            $response['message'] = 'Student not found';
                        }
                        break;

                    default:
                        $response['message'] = 'Method not allowed';
                }
            } catch (Exception $e) {
                $response['message'] = 'Server error: ' . $e->getMessage();
                http_response_code(500);
            }

            $this->sendResponse($response);
        }

        private function getRequestData(): array {
            $contentType = $_SERVER['CONTENT_TYPE'] ?? '';
            if (stripos($contentType, 'application/json') !== false) {
                return json_decode(file_get_contents('php://input'), true) ?? [];
            }
            return $_POST;
        }

        private function sendResponse(array $response): void {
            header('Content-Type: application/json; charset=utf-8');
            http_response_code($response['success'] ? 200 : ($response['message'] === 'Server error' ? 500 : 400));
            echo json_encode($response);
            exit;
        }
    }

    try {
        $db = new Database();
        $student = new Student($db);
        $controller = new StudentController($student);
        $controller->handleRequest();
    } catch (Exception $e) {
        $response = [
            'success' => false,
            'message' => 'Server error: ' . $e->getMessage(),
            'data' => null
        ];
        header('Content-Type: application/json; charset=utf-8');
        http_response_code(500);
        echo json_encode($response);
    }

    Database file:


    CREATE DATABASE IF NOT EXISTS bbu_crud
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

    USE bbu_crud;

    CREATE TABLE IF NOT EXISTS students (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(50) NOT NULL,
        last_name VARCHAR(50) NOT NULL,
        email VARCHAR(100) NOT NULL,
        phone VARCHAR(15) NOT NULL,
        date_of_birth DATE NOT NULL,
        address VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uk_email (email),
        INDEX idx_name (last_name, first_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


    Implementation Details:

    • Frontend (index.php):
      • Uses Bootstrap 5 for a responsive, modern UI.
      • Displays a table of students with columns for ID, Name, Email, Phone, Date of Birth, Address, and Actions (Edit/Delete).
      • Includes modals for creating/editing students and confirming deletions.
      • Client-side validation enforces field requirements (e.g., email format, phone pattern).
      • Toast notifications display success/error messages.
      • JavaScript uses the Fetch API for CRUD operations, communicating with the backend via JSON.
      • Table data is initially loaded server-side using cURL for SEO and reliability.
    • Backend (api/students.php):
      • Implements a REST-like API with POST (create), GET (read), PUT (update), and DELETE methods.
      • Uses strict typing and PHP 8 features (e.g., typed properties, union types).
      • PDO with prepared statements prevents SQL injection.
      • Comprehensive validation ensures data integrity (e.g., email format, phone pattern, date format).
      • Returns JSON responses with success status, message, and data.
      • Handles errors gracefully with appropriate HTTP status codes (200, 400, 500).
    • Database (database.sql):
      • Creates a bbu_crud database with a students table.
      • Fields match the application requirements: id, first_name, last_name, email, phone, date_of_birth, address, created_at, updated_at.
      • Includes a unique constraint on email and an index on last_name, first_name for efficient sorting.
      • Uses InnoDB engine and UTF-8 encoding for broad compatibility.
    • Security:
      • Escapes output with htmlspecialchars to prevent XSS.
      • Uses prepared statements to prevent SQL injection.
      • Validates input on both client and server sides.
      • Sanitizes error messages to avoid leaking sensitive information.
      • Sets correct Content-Type headers for JSON responses.
    • Setup Instructions:
      1. Place index.php in the web root (e.g., /var/www/html).
      2. Place students.php in an api subdirectory (e.g., /var/www/html/api).
      3. Execute the database.sql script in MySQL to create the database and table.
      4. Ensure PHP 8+ and MySQL are installed and configured.
      5. Configure the web server (e.g., Apache, Nginx) to serve PHP.
      6. Update the Database class credentials if necessary (host, username, password).
      7. Access the application at http://localhost/index.php.
    • Assumptions:
      • The web server supports PHP 8+ and MySQL.
      • The API endpoint is accessible at /api/students.php.
      • Same-origin requests (no CORS required).
      • Default MySQL credentials (root, no password) are used; adjust as needed.

    This CRUD application is production-ready, secure, and user-friendly, with a focus on performance and maintainability. It aligns with the structure and functionality of your previous requests, Master. If you need additional features (e.g., search, pagination, authentication) or modifications, please let me know!

    អត្ថបទបន្ទាប់