Building a PHP Student Management System with PDO and MySQL

Database Setup

Before implementing the application, set up the MySQL database and table structure:

CREATE DATABASE IF NOT EXISTS student_db;
USE student_db;

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(10),
    interests VARCHAR(100),
    location VARCHAR(50),
    notes TEXT
);

Project Structure

File	Purpose
`student_form.php`	Form for adding new student records
`student_list.php`	Displays all records with edit/delete options
`student_edit.php`	Form for modifying existing records
`handler.php`	Centralized CRUD operation processro

Adding Records (student_form.php)

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>New Student Entry</title>
</head>
<body>
<center>
    <form id="entryForm" name="entryForm" method="post" action="handler.php?op=create">
        <table>
            <tr style="background: #555; color: white; font-size: 24px; text-align: center">
                <td colspan="2">Student Information Entry</td>
            </tr>
            <tr>
                <td>Name:</td>
                <td><input id="studentName" name="studentName" type="text" required></td>
            </tr>
            <tr>
                <td>Gender:</td>
                <td>
                    <input type="radio" name="gender" value="Male" checked>Male
                    <input type="radio" name="gender" value="Female">Female
                </td>
            </tr>
            <tr>
                <td>Interests:</td>
                <td><input type="text" name="interests" id="interests"></td>
            </tr>
            <tr>
                <td>Location:</td>
                <td>
                    <select id="location" name="location">
                        <option value="">-- Select Region --</option>
                        <option value="Shanghai">Shanghai</option>
                        <option value="Guangzhou">Guangzhou</option>
                        <option value="Beijing">Beijing</option>
                    </select>
                </td>
            </tr>
            <tr>
                <td>Notes:</td>
                <td><textarea id="notes" name="notes" rows="5" cols="30"></textarea></td>
            </tr>
            <tr>
                <td><input type="submit" value="Submit"></td>
                <td><input type="reset" value="Reset"></td>
            </tr>
        </table>
    </form>
</center>
</body>
</html>

Displaying and Managing Records (student_list.php)

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>Student Roster</title>
    <style>
        th, td {
            padding: 10px;
            border: 1px solid #ddd;
        }
        th {
            background-color: #f4f4f4;
        }
    </style>
    <script>
        function confirmDeletion(recordId) {
            if (confirm("Are you sure you want to delete this record?")) {
                window.location.href = 'handler.php?op=delete&id=' + recordId;
            }
        }
    </script>
</head>
<body>
<center>
    <h2>Student Information Directory</h2>
    <table width="800" border="1" cellpadding="5" cellspacing="0">
        <tr style="background: #444; color: white; font-size: 20px; text-align: center">
            <td colspan="6">Student Records</td>
        </tr>
        <tr>
            <th>Name</th>
            <th>Gender</th>
            <th>Interests</th>
            <th>Location</th>
            <th>Notes</th>
            <th>Actions</th>
        </tr>
        <?php
        try {
            $db = new PDO("mysql:host=localhost;dbname=student_db;charset=utf8", "root", "password");
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $err) {
            die("Database connection failed: " . $err->getMessage());
        }

        $query = "SELECT * FROM students ORDER BY id DESC";
        $result = $db->query($query);

        while ($record = $result->fetch(PDO::FETCH_ASSOC)) {
            echo "<tr>";
            echo "<td>" . htmlspecialchars($record['name']) . "</td>";
            echo "<td>" . htmlspecialchars($record['gender']) . "</td>";
            echo "<td>" . htmlspecialchars($record['interests']) . "</td>";
            echo "<td>" . htmlspecialchars($record['location']) . "</td>";
            echo "<td>" . htmlspecialchars($record['notes']) . "</td>";
            echo "<td>";
            echo "<a href='javascript:confirmDeletion(" . $record['id'] . ")'>Delete</a> | ";
            echo "<a href='student_edit.php?id=" . $record['id'] . "'>Edit</a>";
            echo "</td>";
            echo "</tr>";
        }
        ?>
    </table>
    <p><a href="student_form.php">Add New Student</a></p>
</center>
</body>
</html>

Editing Records (student_edit.php)

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>Update Student Record</title>
</head>
<body>
<center>
    <?php
    if (!isset($_GET['id']) || empty($_GET['id'])) {
        die("Invalid record ID specified.");
    }

    try {
        $db = new PDO("mysql:host=localhost;dbname=student_db;charset=utf8", "root", "password");
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $err) {
        die("Database connection failed: " . $err->getMessage());
    }

    $recordId = intval($_GET['id']);
    $stmt = $db->prepare("SELECT * FROM students WHERE id = :id");
    $stmt->execute(['id' => $recordId]);
    $student = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$student) {
        die("Record not found in database.");
    }
    ?>

    <form id="updateForm" name="updateForm" method="post" action="handler.php?op=update">
        <input type="hidden" name="recordId" value="<?php echo $student['id']; ?>">
        <table>
            <tr style="background: #555; color: white; font-size: 24px; text-align: center">
                <td colspan="2">Modify Student Information</td>
            </tr>
            <tr>
                <td>Name:</td>
                <td><input id="studentName" name="studentName" type="text" 
                          value="<?php echo htmlspecialchars($student['name']); ?>" required></td>
            </tr>
            <tr>
                <td>Gender:</td>
                <td>
                    <input type="radio" name="gender" value="Male" 
                           <?php echo $student['gender'] === 'Male' ? 'checked' : ''; ?>>Male
                    <input type="radio" name="gender" value="Female" 
                           <?php echo $student['gender'] === 'Female' ? 'checked' : ''; ?>>Female
                </td>
            </tr>
            <tr>
                <td>Interests:</td>
                <td><input type="text" name="interests" id="interests" 
                          value="<?php echo htmlspecialchars($student['interests']); ?>"></td>
            </tr>
            <tr>
                <td>Location:</td>
                <td>
                    <select id="location" name="location">
                        <option value="">-- Select Region --</option>
                        <option value="Shanghai" 
                                <?php echo $student['location'] === 'Shanghai' ? 'selected' : ''; ?>>Shanghai</option>
                        <option value="Guangzhou" 
                                <?php echo $student['location'] === 'Guangzhou' ? 'selected' : ''; ?>>Guangzhou</option>
                        <option value="Beijing" 
                                <?php echo $student['location'] === 'Beijing' ? 'selected' : ''; ?>>Beijing</option>
                    </select>
                </td>
            </tr>
            <tr>
                <td>Notes:</td>
                <td><textarea id="notes" name="notes" rows="5" cols="30">
                    <?php echo htmlspecialchars($student['notes']); ?>
                </textarea></td>
            </tr>
            <tr>
                <td colspan="2"><input type="submit" value="Save Changes"></td>
            </tr>
        </table>
    </form>
</center>
</body>
</html>

CRUD Handler (handler.php)

<?php
try {
    $db = new PDO("mysql:host=localhost;dbname=student_db;charset=utf8", "root", "password");
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

$operation = $_GET['op'] ?? $_POST['op'] ?? '';

switch ($operation) {
    case 'create':
        $name = trim($_POST['studentName']);
        $gender = $_POST['gender'] ?? 'Male';
        $interests = trim($_POST['interests']);
        $location = $_POST['location'] ?? '';
        $notes = trim($_POST['notes']);

        $insertSql = "INSERT INTO students (name, gender, interests, location, notes) 
                      VALUES (:name, :gender, :interests, :location, :notes)";
        $stmt = $db->prepare($insertSql);
        $stmt->execute([
            ':name' => $name,
            ':gender' => $gender,
            ':interests' => $interests,
            ':location' => $location,
            ':notes' => $notes
        ]);

        if ($stmt->rowCount() > 0) {
            header("Location: student_list.php?status=added");
            exit;
        } else {
            echo "<script>alert('Failed to add record'); history.back();</script>";
        }
        break;

    case 'delete':
        $id = isset($_GET['id']) ? intval($_GET['id']) : 0;
        
        if ($id > 0) {
            $deleteSql = "DELETE FROM students WHERE id = :id";
            $stmt = $db->prepare($deleteSql);
            $stmt->execute([':id' => $id]);

            if ($stmt->rowCount() > 0) {
                header("Location: student_list.php?status=deleted");
                exit;
            }
        }
        echo "<script>alert('Deletion failed or record not found'); history.back();</script>";
        break;

    case 'update':
        $id = intval($_POST['recordId']);
        $name = trim($_POST['studentName']);
        $gender = $_POST['gender'] ?? 'Male';
        $interests = trim($_POST['interests']);
        $location = $_POST['location'] ?? '';
        $notes = trim($_POST['notes']);

        $updateSql = "UPDATE students SET 
                      name = :name, 
                      gender = :gender, 
                      interests = :interests, 
                      location = :location, 
                      notes = :notes 
                      WHERE id = :id";
        $stmt = $db->prepare($updateSql);
        $stmt->execute([
            ':name' => $name,
            ':gender' => $gender,
            ':interests' => $interests,
            ':location' => $location,
            ':notes' => $notes,
            ':id' => $id
        ]);

        if ($stmt->rowCount() > 0) {
            header("Location: student_list.php?status=updated");
            exit;
        } else {
            echo "<script>alert('No changes made or update failed'); history.back();</script>";
        }
        break;

    default:
        header("Location: student_list.php");
        exit;
}
?>

Security Considerations

SQL Injection Prevention:

The refactored code uses PDO prepared statements exclusively. Placeholder markers (:name, :id, etc.) ensure user input is never concatenated directly into SQL queries, preventing injection attacks.

XSS Prevention:

The htmlspecialchars() function escapes output when displaying user-submitted data in HTML context, neutralizing potential Cross-Site Scripting vectors.

Input Validation:

intval() converts numeric parameters to integers before database operations
trim() removes extraneous whitespace from text inputs
Required field validation on the form prevents empty submissions

Key Improvements Over Procedural Approach

Aspect	Original Code	Refactored Code
Database abstraction	Procedural `mysql_*` style	PDO with prepared statements
Error handling	Basic try-catch	PDO exception modes enabled
Parameter handling	String interpolation	Bound parameters
Output encoding	None	`htmlspecialchars()` throughout
Code organization	Scattered across files	Centralized handler

Database Connection Pattern

Establish the PDO connection once and reuse the $db object across all operations:

$dsn = "mysql:host=localhost;dbname=student_db;charset=utf8";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, 'root', 'password', $options);

This configuration enables exception-based error handling, sets associative array fetching as default, and disables emulated prepares for security.

Tags: PHP MySQL PDO CRUD web development

Posted on Wed, 08 Jul 2026 17:01:00 +0000 by v1ral

Freaks City