Building a Student Management System with PDO
Design and build a full CRUD student management system backed by MySQL, using PDO with prepared statements throughout, server-side validated forms, keyset/offset pagination, and safely parameterized search and filtering.
This project takes everything the course has covered about relational databases — schema design, PDO, prepared statements, and safe dynamic queries — and applies it to a system real institutions actually run: a student roster that staff can create, read, update, delete, search, filter, and page through. Where the CLI Calculator project was about clean logic and exceptions, this project is about the layer most PHP applications spend the majority of their code on: talking to a database correctly and safely, using the PDO Basics and Prepared Statements lessons as the foundation for every single query the application issues.
Project Overview and Requirements
The system manages student records with fields for a full name, email, date of birth, grade level (an integer 1–12), enrollment status (active, inactive, graduated), and an enrollment date. Staff need to create a new student via a form, view a paginated list of all students, edit any existing student's details, and delete a student — the four CRUD operations the acronym is named for, each backed by its own validated form or confirmation step rather than an unguarded raw query triggered directly from a link.
Two features turn this from a toy CRUD demo into something closer to production: pagination and search/filtering. With even a few hundred students, rendering every row on one page is both slow to query and unusable to scroll through, so the list view must page through results in fixed-size chunks. And staff need to find students by typing a partial name, or narrow the list to a specific grade or enrollment status, without the search feature becoming a SQL injection vector — which is the central risk this whole project is designed to confront head-on.
Every one of these requirements maps to a lesson already covered: forms and validation from the Forms and Server-Side Validation lessons, PDO connections and prepared statements from the PDO and Prepared Statements lessons, and the specific technique of building a query whose WHERE clause changes shape based on which filters are active while never concatenating user input directly into SQL.
• CRUD: create, list/read, update, delete student records. • Fields: full name, email, date of birth, grade (1-12), status (active/inactive/graduated), enrollment date. • Pagination: the list view must never load the entire table at once. • Search by name (partial match) and filtering by grade/status, combinable, always via parameterized queries. • MySQL as the target database, accessed exclusively through PDO.
Designing the Students Table Schema
The students table needs a surrogate primary key (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY), a full_name VARCHAR(150) NOT NULL, and an email VARCHAR(255) NOT NULL UNIQUE — the uniqueness constraint at the database level is a safety net behind the application-level duplicate check, following the defense-in-depth principle from the Database Constraints lesson: never rely on application code alone to enforce a rule the database can enforce for you. date_of_birth DATE NOT NULL and enrollment_date DATE NOT NULL use MySQL's native DATE type rather than storing dates as strings, which keeps range queries (e.g. "students who enrolled this year") both correct and index-friendly.
grade TINYINT UNSIGNED NOT NULL stores 1 through 12, and status ENUM('active','inactive','graduated') NOT NULL DEFAULT 'active' restricts enrollment status to exactly the three valid values at the schema level — an ENUM column rejects any other value outright, which is a second line of defense if application validation were ever bypassed or buggy. Two indexes matter for this project specifically: an index on full_name (or a FULLTEXT index, for larger datasets) to keep name searches fast, and a composite index on (status, grade) to keep the common "filter by status and grade" query from scanning the entire table.
Choosing column types this deliberately — DATE instead of VARCHAR, ENUM instead of a free-text status column, UNSIGNED integers where negative values make no sense — is what the Database Design and Normalization lesson calls letting the schema do validation work for you, rather than pushing every single rule into PHP code where it's easier to accidentally skip.
• id: INT UNSIGNED AUTO_INCREMENT PRIMARY KEY. • email: VARCHAR(255) NOT NULL UNIQUE — database-level duplicate protection. • date_of_birth / enrollment_date: native DATE columns, not strings. • status: ENUM('active','inactive','graduated') — invalid values are rejected by MySQL itself. • Indexes on full_name and on (status, grade) to keep search and filtering fast.
Building the Create and Edit Forms
Both the create and edit forms post to the same handler pattern: an HTML form with named inputs matching the column names (full_name, email, date_of_birth, grade, status), a hidden id field present only on the edit form to distinguish an UPDATE from an INSERT, and a CSRF token hidden field generated the same way the CSRF Protection lesson describes, since this is a state-changing form and is exactly the kind of request CSRF protection exists to guard.
Server-side validation runs before any database call, checking that full_name is non-empty and under 150 characters, that email passes PHP's built-in filter_var($email, FILTER_VALIDATE_EMAIL) check, that date_of_birth and enrollment_date parse as valid dates and that the enrollment date isn't before the date of birth, that grade is an integer between 1 and 12, and that status is one of the three allowed values — mirroring the ENUM constraint in the database, but catching the problem earlier, with a message the form can display next to the specific field, which is more useful to a staff member than a generic database error surfaced after the fact.
Client-side HTML5 attributes (required, type="email", type="date", a numeric min/max on the grade field) improve the experience for well-behaved browsers, but as the Client-Side vs Server-Side Validation lesson stresses, they are a convenience layer only — every one of those checks is re-run in PHP because client-side validation can be bypassed trivially by anyone sending a raw HTTP request, and the application's data integrity cannot depend on the browser cooperating.
• Create and edit share one form shape; a hidden id field (present only on edit) distinguishes INSERT from UPDATE. • Every form includes a CSRF token, checked before any write happens. • Server-side validation duplicates every client-side rule and is the one that actually matters for data integrity. • filter_var(..., FILTER_VALIDATE_EMAIL) validates email format before it ever reaches a query.
The PDO Data Access Layer
All database access goes through a small StudentRepository class wrapping a PDO connection, following the Repository Pattern touched on in the OOP and Database Design lessons: every place in the application that needs a student record calls a method on this class, and nowhere else in the codebase constructs a raw SQL string. The PDO connection itself is created once with PDO::ATTR_ERRMODE set to PDO::ERRMODE_EXCEPTION (so a database error surfaces as a catchable PHP exception rather than a silently-ignored false return) and PDO::ATTR_EMULATE_PREPARES set to false, which forces MySQL's real server-side prepared statements rather than PHP emulating them — the safer, and on repeated queries generally faster, choice covered in the PDO Configuration lesson.
Every single query — insert, update, delete, and select — uses a parameterized prepared statement with named placeholders (:full_name, :email, and so on) bound via bindValue() or passed in the execute() array, never string interpolation of user-supplied values into the SQL text. This is non-negotiable: it is the single most important defense against SQL injection covered in the SQL Injection Prevention lesson, and this project treats it as a hard rule with zero exceptions, including for values that "seem safe" like an integer grade — even an integer should be bound as a parameter rather than concatenated, both for safety and for consistency across the codebase.
The repository's create() and update() methods return the affected student's id (via PDO::lastInsertId() for create), while delete() returns a boolean based on the prepared statement's row count, so calling code can distinguish "delete succeeded" from "no student with that id existed" without a separate lookup query.
• One StudentRepository class is the only place raw SQL is written. • PDO::ERRMODE_EXCEPTION turns database errors into catchable exceptions instead of silent false returns. • PDO::ATTR_EMULATE_PREPARES = false uses real server-side prepared statements. • Every value, including integers, is bound as a parameter — never concatenated into the SQL string.
Pagination for the Student List
The simplest approach — and the right starting point for a dataset in the thousands rather than millions of rows — is LIMIT/OFFSET pagination: the list query accepts a page number and a fixed page size (say, 25), computes OFFSET = (page - 1) * pageSize, and appends LIMIT :limit OFFSET :offset to the query, with both values bound as integer parameters. A second, lightweight COUNT(*) query (using the same WHERE clause as the main query, so the count matches what's actually filtered) tells the view how many total pages exist, which is what powers a "Page 3 of 12" style pagination control.
LIMIT/OFFSET has a well-known weakness: for very large offsets, MySQL still has to scan and discard all the skipped rows before it can return the requested page, so performance degrades on very deep pages of very large tables. The more scalable alternative, keyset (or "seek") pagination, avoids OFFSET entirely: instead of asking for "skip 5,000, take 25", the query asks for "the next 25 rows after id 5000" using WHERE id > :last_seen_id ORDER BY id LIMIT :page_size, which MySQL can satisfy directly from the primary key's index without scanning anything it's going to discard.
For this project, LIMIT/OFFSET is the primary implementation because the requirement calls for numbered pages with a visible "page 3 of 12" control, which keyset pagination doesn't naturally support (you can't jump to an arbitrary page without walking through it), but the lesson explicitly notes keyset pagination as the better choice for a simple "load more" style list or for any dataset expected to grow into the hundreds of thousands of rows.
• LIMIT :limit OFFSET :offset — simple, supports numbered pages, degrades on very large offsets. • A matching COUNT(*) query (same WHERE clause) computes total pages for the pagination control. • Keyset pagination (WHERE id > :last_id ORDER BY id LIMIT :n) scales better but doesn't support jumping to an arbitrary page number. • Both limit and offset values must be bound as integer parameters, never interpolated directly.
Search and Filtering with Safe Dynamic Queries
The list page accepts three optional query-string parameters: q (a name search term), grade, and status. The tricky part is that the WHERE clause needs to change shape depending on which of these are actually present — searching with no filters at all, or with all three combined — while still using nothing but parameterized placeholders. The safe pattern is to build the SQL string and the parameter array together in the same loop: start with a base query and an empty conditions array, and for each active filter, append a WHERE-clause fragment containing only a named placeholder (never the value itself) to the conditions array, and add the actual value to a separate parameters array bound at execute time.
Name search uses full_name LIKE :name_search with the parameter value set to '%' . $searchTerm . '%' — building the % wildcards into the bound value, not into the SQL text, so a search term containing % or _ (LIKE's own wildcard characters) is treated as literal text to search for rather than as part of the pattern, unless the project explicitly wants those characters to behave as wildcards for the user, in which case they should be escaped with addcslashes($term, '%_') before wrapping in wildcards.
Grade and status filters are equality comparisons (grade = :grade, status = :status), and status in particular is validated against the fixed allow-list of three values *before* it's ever added to the parameters array — not because a bound parameter is vulnerable to injection (it isn't), but because an invalid status value passed through unnoticed would just return zero rows and confuse the user, so validating it early gives a clearer error instead of a silently empty result.
This assembled-conditions approach is the safe way to build a dynamic query, and it stands in direct contrast to the number one project-breaking mistake this lesson calls out explicitly: interpolating $_GET['q'] or $_GET['grade'] straight into the SQL string with string concatenation. That approach may appear to work in casual testing and then become a full SQL injection vulnerability the moment someone submits ' OR '1'='1 as a search term — which is precisely why every value in this system, no matter how it entered the WHERE clause, is bound through a placeholder.
• Build the WHERE clause and parameter array together: only placeholders go into the SQL string, values go into a separate bound-parameters array. • LIKE search wraps '%' wildcards around the bound value, not into the raw SQL text. • Validate status/grade against an allow-list before binding, to give a clear error instead of a silently empty result. • Never concatenate $_GET values into a SQL string, even 'just for search' — this is the classic entry point for SQL injection.
Testing and Extending the Project
Following the Testing Database Code lesson, StudentRepository's methods are tested against a real (test) MySQL database or SQLite in-memory database rather than mocked outright, since the whole point of this layer is its SQL correctness — a mock PDO object can't catch a typo in a column name or an incorrect JOIN. Tests should cover: creating a student and reading it back, updating a student and verifying only the intended fields changed, deleting a student and verifying a second delete attempt correctly reports nothing was deleted, and at least one search/filter combination returning exactly the expected subset of rows.
Natural extensions include adding CSV export of the filtered list (reusing the exact same WHERE-building logic so the export always matches what's on screen), soft-deletes (a deleted_at timestamp column instead of a real DELETE, so records can be recovered — a pattern from the Soft Deletes lesson), and role-based access so only staff with an 'admin' role can delete records while all staff can view and search, which ties directly into the authorization concepts explored fully in the next project.
Code Examples
Native DATE and ENUM types push validation into the database itself, the UNIQUE constraint on email backs up the application-level duplicate check, and the two indexes target exactly the queries the list/search page will run most often.
status ENUM('active', 'inactive', 'graduated') NOT NULL DEFAULT 'active',
Every write goes through a prepared statement with named placeholders bound from an associative array, ERRMODE_EXCEPTION means any constraint violation (like the UNIQUE email index) surfaces as a catchable PDOException instead of a silent failure, and rowCount() lets callers tell a real update/delete apart from a no-op on a missing id.
PDO::ATTR_EMULATE_PREPARES => false, The WHERE clause is assembled from only placeholder fragments while every real value lives in the $params array, the status filter is checked against an allow-list before it's trusted, and LIMIT/OFFSET are bound explicitly as PDO::PARAM_INT since PDO would otherwise quote them as strings, which MySQL rejects in a LIMIT clause under real prepared statements.
$stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT);
$sql = "SELECT * FROM students WHERE full_name LIKE '%" . $_GET['q'] . "%'";
$stmt = $pdo->prepare("SELECT * FROM students WHERE full_name LIKE :q"); $stmt->execute([':q' => '%' . $_GET['q'] . '%']);
Concatenating a raw $_GET value into SQL text is a direct SQL injection vulnerability — a search term like ' OR '1'='1 would return every row, and a more crafted payload could read or modify unrelated tables. Every value must be bound through a placeholder, with no exceptions for 'just a search box'.
$stmt->bindValue(':limit', $pageSize); $stmt->bindValue(':offset', $offset);
$stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT); $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
PDO binds values as strings by default; MySQL's real (non-emulated) prepared statements reject a quoted string in a LIMIT/OFFSET clause. The integer type must be specified explicitly with PDO::PARAM_INT for pagination to work under PDO::ATTR_EMULATE_PREPARES = false.
// COUNT(*) query ignores the search/filter WHERE clause entirely
// Build the WHERE clause once, reuse it for both the COUNT(*) and the SELECT
If the total-pages count is computed from an unfiltered COUNT(*) while the list itself is filtered, the pagination control will show far more pages than actually contain matching rows, and the last few 'pages' will render empty.
Why is the email column declared VARCHAR(255) NOT NULL UNIQUE at the database level, given that the application also checks for duplicate emails before inserting? A. It isn't necessary; the application check alone is always sufficient B. It provides a database-level safety net (defense in depth) in case the application check is ever skipped, buggy, or bypassed by a race condition C. MySQL requires every VARCHAR column to declare UNIQUE D. It makes the column faster to read but has no effect on data integrity
B. It provides a database-level safety net (defense in depth) in case the application check is ever skipped, buggy, or bypassed by a race condition — Relying on application code alone to enforce uniqueness leaves a window (for example, two concurrent requests) where a duplicate could slip through; a UNIQUE constraint makes the database itself reject it as a second, independent layer of protection.
What is the main downside of LIMIT/OFFSET pagination compared to keyset pagination? A. LIMIT/OFFSET cannot be combined with a WHERE clause B. It doesn't support numbered pages C. For large offsets, the database still has to scan and discard all skipped rows, so deep pages get slower as the table grows D. LIMIT/OFFSET is not supported by MySQL
C. For large offsets, the database still has to scan and discard all skipped rows, so deep pages get slower as the table grows — MySQL must count through and discard every row before the offset even though those rows aren't returned, so performance degrades on very large offsets — keyset pagination avoids this by seeking directly via an indexed WHERE condition instead.
In the search() method, why is the status filter checked against an allow-list of three values before being added to the query parameters? A. Because a bound parameter is vulnerable to SQL injection if not validated first B. To give a clear, early error instead of a silently empty result set if an invalid status somehow arrives C. PDO requires all string parameters to be pre-validated D. MySQL cannot run a query with an ENUM column unless the value is checked in PHP first
B. To give a clear, early error instead of a silently empty result set if an invalid status somehow arrives — A bound parameter is already safe from injection regardless of validation, but an invalid, unvalidated status value would simply match zero rows and confuse the user with no explanation — checking it early lets the application respond with a clear message instead.
Why does the LIKE search build '%' . $term . '%' into the bound parameter value rather than into the SQL string itself? A. It has no effect either way; both approaches are identical B. Because PDO does not allow the % character inside SQL text at all C. So that a search term containing literal % or _ characters is not treated as SQL syntax, and the wildcards remain part of the safely bound value rather than the query text D. Because MySQL requires wildcards to be passed as a separate query parameter named :wildcard
C. So that a search term containing literal % or _ characters is not treated as SQL syntax, and the wildcards remain part of the safely bound value rather than the query text — Keeping the % wildcards inside the bound value (not the SQL text) means the entire search term, wildcards included, is treated as data by the prepared statement — nothing about it is parsed as SQL syntax, which is what keeps the search box safe from injection.
CREATE TABLE students (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
full_name VARCHAR(150) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
date_of_birth DATE NOT NULL,
enrollment_date DATE NOT NULL,
grade TINYINT UNSIGNED NOT NULL,
status ENUM('active', 'inactive', 'graduated') NOT NULL DEFAULT 'active',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_full_name (full_name),
INDEX idx_status_grade (status, grade)
);<?php
declare(strict_types=1);
class StudentRepository
{
private PDO $pdo;
public function __construct(string $dsn, string $user, string $pass)
{
$this->pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
}
public function create(array $data): int
{
$sql = "INSERT INTO students (full_name, email, date_of_birth, enrollment_date, grade, status)
VALUES (:full_name, :email, :date_of_birth, :enrollment_date, :grade, :status)";
$stmt = $this->pdo->prepare($sql);
$stmt->execute([
':full_name' => $data['full_name'],
':email' => $data['email'],
':date_of_birth' => $data['date_of_birth'],
':enrollment_date' => $data['enrollment_date'],
':grade' => $data['grade'],
':status' => $data['status'],
]);
return (int) $this->pdo->lastInsertId();
}
public function update(int $id, array $data): bool
{
$sql = "UP
…<?php
declare(strict_types=1);
class StudentRepository
{
// ...constructor and CRUD methods from above...
/**
* @param array{q?:string, grade?:int, status?:string} $filters
* @return array{rows: array<int, array>, total: int, page: int, totalPages: int}
*/
public function search(array $filters, int $page, int $pageSize = 25): array
{
$conditions = [];
$params = [];
if (!empty($filters['q'])) {
$conditions[] = "full_name LIKE :name_search";
$params[':name_search'] = '%' . $filters['q'] . '%';
}
if (!empty($filters['grade'])) {
$conditions[] = "grade = :grade";
$params[':grade'] = (int) $filters['grade'];
}
$allowedStatuses = ['active', 'inactive', 'graduated'];
if (!empty($filters['status']) && in_array($filters['status'], $allowedStatuses, true)) {
$conditions[] = "status = :status";
$params[':status'] = $filters['status'];
}
$whereClause = $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';
// Count query shares the exact same WHERE clause so totals match the filtered resul
…