October 10, 2026

Meta Title: PHP Search, Filter and Pagination Tutorial with MySQL

Meta Description: Learn PHP search, filter, and pagination step by step using MySQL and PDO. Improve your student management system with secure queries and easy navigation.

Focus Keyword: PHP search filter pagination

Secondary Keywords: PHP MySQL search tutorial, PHP pagination tutorial, PHP PDO search example, PHP database filtering, PHP student management system, MySQL LIMIT OFFSET

URL Slug: php-search-filter-pagination-mysql-tutorial

---

Introduction

Welcome to Module 11 of our Core PHP tutorial series!

In Module 10: PHP CRUD Operations Tutorial, we built a Student Management System using Core PHP, MySQL, PDO, and HTML forms. We learned how to create, view, update, and delete student records.

However, imagine that your application contains thousands of student records. Finding a particular student by manually scrolling through the entire list would be inconvenient.

This is where search, filtering, and pagination in PHP become useful.

In this tutorial, we will enhance our existing Student Management System by adding:

• Search students by name or email.

• Filter students by course.

• Display a limited number of records per page.

• Navigate between pages.

• Use PDO prepared statements for database queries.

• Preserve search and filter selections while navigating pages.

We will reuse the existing student_db database and students table from Module 10 instead of creating a new project.

By the end of this module, you will understand how to build a more efficient, user-friendly, and secure database listing page using Core PHP and MySQL.

1. What Are Search, Filter, and Pagination in PHP?

These three features help users find and navigate database records efficiently.

1.1 Search

Search allows users to find records by entering a keyword.

For example, a user can enter Rahul to find students whose names contain that word.

1.2 Filter

Filtering narrows down records according to a specific condition.

For example, selecting BCA from a course dropdown displays only students enrolled in BCA.

1.3 Pagination

Pagination divides a large set of records into smaller pages.

Instead of displaying 1,000 student records on one page, the application might display 10 records per page.

Feature	Purpose	Example
Search	Find matching records	Search for Rahul
Filter	Narrow down results	Show only BCA students
Pagination	Split results into pages	Display 10 students per page

How do these features work together?

1. The user opens the student listing page.

2. The user enters a search keyword or selects a course.

3. PHP validates the submitted parameters.

4. PDO executes a database query with the appropriate conditions.

5. MySQL returns the matching records for the requested page.

6. PHP displays the results and pagination links.

This approach is useful for student management systems, employee directories, inventory applications, product catalogs, and admin dashboards.

2. Requirements for the Project

Before starting, prepare the following:

• XAMPP or another PHP development environment.

• PHP 8.x.

• MySQL or MariaDB.

• A web browser.

• A code editor such as Visual Studio Code.

• The Student Management System created in Module 10.

Start Apache and MySQL from the XAMPP Control Panel.

Open phpMyAdmin:

http://localhost/phpmyadmin/

Make sure the existing student_db database and students table are available.

Important: If you have already completed Module 10, you do not need to recreate the database or its table.

3. Understand the Existing Database Table

Our Student Management System uses the following table structure:

CREATE TABLE students (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    course VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The table contains these columns:

• id: Unique identifier for each student.

• name: Student's name.

• email: Student's email address.

• course: Student's course.

• created_at: Record creation timestamp.

If this table already exists, there is no need to run the query again.

To check the existing records, execute:

SELECT * FROM students;

If the table is empty, add a few sample students through the existing create.php page before testing search, filtering, and pagination.

4. Understand the SQL Queries Used for Search and Filtering

Before writing PHP code, let's understand the SQL operations behind these features.

4.1 Search records using LIKE

The SQL LIKE operator searches for values that match a pattern.

SELECT id, name, email, course
FROM students
WHERE name LIKE '%Rahul%';

The % wildcard matches zero or more characters.

Therefore, %Rahul% can match names such as Rahul, Rahul Kumar, or Amit Rahul Sharma.

4.2 Filter records by course

To retrieve students enrolled in BCA:

SELECT id, name, email, course
FROM students
WHERE course = 'BCA';

4.3 Limit the number of records

MySQL supports LIMIT and OFFSET for pagination.

SELECT id, name, email, course
FROM students
ORDER BY id DESC
LIMIT 10 OFFSET 0;

This query retrieves up to 10 records, starting from the first result.

For the second page, the offset becomes 10:

SELECT id, name, email, course
FROM students
ORDER BY id DESC
LIMIT 10 OFFSET 10;

The general offset formula is:

Offset = (Current Page − 1) × Records Per Page

For example, page 3 with 10 records per page uses an offset of 20.

5. Update the Project Structure

We will reuse the existing project from Module 10.

student_management/
│
├── db.php
├── functions.php
├── index.php
├── create.php
├── edit.php
├── delete.php
└── style.css

We only need to update index.php and style.css for this tutorial.

The other files can remain unchanged.

• db.php creates the PDO database connection.

• functions.php contains shared validation and HTML escaping helpers.

• index.php handles searching, filtering, pagination, and listing.

• create.php adds student records.

• edit.php updates student records.

• delete.php deletes student records.

• style.css styles the interface.

Keeping these responsibilities separate makes the application easier to maintain.

6. Build the Search, Filter, and Pagination Logic

Open the existing index.php file and replace its contents with the following code.

The implementation uses PDO prepared statements, validates the page number, preserves search and filter parameters, and escapes output before displaying it.

Complete index.php code

<?php

require_once "db.php";
require_once "functions.php";

// Get and validate search input.
$search = trim($_GET["search"] ?? "");

if (strlen($search) > 150) {
    $search = substr($search, 0, 150);
}

// Get the selected course.
$course = trim($_GET["course"] ?? "");

if (strlen($course) > 100) {
    $course = "";
}

// Validate the requested page number.
$page = filter_var(
    $_GET["page"] ?? 1,
    FILTER_VALIDATE_INT,
    [
        "options" => [
            "min_range" => 1
        ]
    ]
);

$page = ($page === false) ? 1 : $page;

// Records displayed per page.
$limit = 10;

// Build search and filter conditions.
$where = [];
$params = [];

if ($search !== "") {
    $where[] = "(name LIKE :search_name
                 OR email LIKE :search_email)";

    $params["search_name"] = "%" . $search . "%";
    $params["search_email"] = "%" . $search . "%";
}

if ($course !== "") {
    $where[] = "course = :course";
    $params["course"] = $course;
}

$whereSql = $where
    ? " WHERE " . implode(" AND ", $where)
    : "";

// Get the total number of matching records.
$countSql = "SELECT COUNT(*)
             FROM students" . $whereSql;

$countStmt = $pdo->prepare($countSql);
$countStmt->execute($params);

$totalRecords = (int) $countStmt->fetchColumn();

// Calculate pagination.
$totalPages = (int) ceil($totalRecords / $limit);

// Handle a page number beyond the available pages.
if ($totalPages > 0 && $page > $totalPages) {
    $page = $totalPages;
}

$offset = ($page - 1) * $limit;

// Fetch only the records required for this page.
$sql = "SELECT id, name, email, course, created_at
        FROM students"
        . $whereSql .
        " ORDER BY id DESC
          LIMIT :limit OFFSET :offset";

$stmt = $pdo->prepare($sql);

// Bind search and filter parameters.
foreach ($params as $key => $value) {
    $stmt->bindValue(
        ":" . $key,
        $value,
        PDO::PARAM_STR
    );
}

// Bind pagination values as integers.
$stmt->bindValue(":limit", $limit, PDO::PARAM_INT);
$stmt->bindValue(":offset", $offset, PDO::PARAM_INT);

$stmt->execute();

$students = $stmt->fetchAll();

// Get distinct courses for the filter dropdown.
$courseStmt = $pdo->query(
    "SELECT DISTINCT course
     FROM students
     ORDER BY course ASC"
);

$courses = $courseStmt->fetchAll(PDO::FETCH_COLUMN);

// Preserve current search and filter values in page links.
$queryParams = [];

if ($search !== "") {
    $queryParams["search"] = $search;
}

if ($course !== "") {
    $queryParams["course"] = $course;
}

function pageUrl($pageNumber, $queryParams)
{
    $queryParams["page"] = $pageNumber;

    return "index.php?" . http_build_query($queryParams);
}

?>

<!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 rel="stylesheet" href="style.css">
</head>

<body>

<div class="container">

    <h1>Student Management System</h1>

    <a class="button" href="create.php">
        + Add New Student
    </a>

    <h2>Search and Filter Students</h2>

    <form method="GET" action="index.php" class="filter-form">

        <div class="form-group">
            <label for="search">Search by Name or Email</label>

            <input
                type="search"
                id="search"
                name="search"
                maxlength="150"
                value="<?= escapeHtml($search) ?>"
                placeholder="Enter student name or email"
            >
        </div>

        <div class="form-group">
            <label for="course">Filter by Course</label>

            <select id="course" name="course">

                <option value="">All Courses</option>

                <?php foreach ($courses as $courseName): ?>

                    <option
                        value="<?= escapeHtml($courseName) ?>"
                        <?= $course === $courseName
                            ? "selected"
                            : "" ?>
                    >
                        <?= escapeHtml($courseName) ?>
                    </option>

                <?php endforeach; ?>

            </select>
        </div>

        <button type="submit">Search</button>

        <a class="button secondary" href="index.php">
            Reset
        </a>

    </form>

    <h2>Student Records</h2>

    <p class="result-info">
        Total matching records: <?= $totalRecords ?>
    </p>

    <div class="table-wrapper">

        <table>

            <thead>
                <tr>
                    <th>ID</th>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Course</th>
                    <th>Created At</th>
                    <th>Actions</th>
                </tr>
            </thead>

            <tbody>

            <?php if ($students): ?>

                <?php foreach ($students as $student): ?>

                    <tr>
                        <td>
                            <?= (int) $student["id"] ?>
                        </td>

                        <td>
                            <?= escapeHtml($student["name"]) ?>
                        </td>

                        <td>
                            <?= escapeHtml($student["email"]) ?>
                        </td>

                        <td>
                            <?= escapeHtml($student["course"]) ?>
                        </td>

                        <td>
                            <?= escapeHtml($student["created_at"]) ?>
                        </td>

                        <td class="actions">

                            <a href="edit.php?id=<?= (int) $student["id"] ?>">
                                Edit
                            </a>

                            <form
                                action="delete.php"
                                method="POST"
                                onsubmit="return confirm('Delete this student?');"
                            >
                                <input
                                    type="hidden"
                                    name="id"
                                    value="<?= (int) $student["id"] ?>"
                                >

                                <button
                                    type="submit"
                                    class="delete-button"
                                >
                                    Delete
                                </button>
                            </form>

                        </td>
                    </tr>

                <?php endforeach; ?>

            <?php else: ?>

                <tr>
                    <td colspan="6">
                        No matching student records found.
                    </td>
                </tr>

            <?php endif; ?>

            </tbody>

        </table>

    </div>

    <?php if ($totalPages > 1): ?>

        <nav class="pagination" aria-label="Student pages">

            <?php if ($page > 1): ?>

                <a href="<?= escapeHtml(
                    pageUrl($page - 1, $queryParams)
                ) ?>">
                    Previous
                </a>

            <?php endif; ?>

            <?php for (
                $i = max(1, $page - 2);
                $i <= min($totalPages, $page + 2);
                $i++
            ): ?>

                <a
                    href="<?= escapeHtml(
                        pageUrl($i, $queryParams)
                    ) ?>"
                    class="<?= $i === $page ? "active" : "" ?>"
                    <?= $i === $page ? 'aria-current="page"' : "" ?>
                >
                    <?= $i ?>
                </a>

            <?php endfor; ?>

            <?php if ($page < $totalPages): ?>

                <a href="<?= escapeHtml(
                    pageUrl($page + 1, $queryParams)
                ) ?>">
                    Next
                </a>

            <?php endif; ?>

        </nav>

    <?php endif; ?>

</div>

</body>
</html>

How does this code work?

Let's understand the main parts.

Step 1: Read the search parameters

$search = trim($_GET["search"] ?? "");
$course = trim($_GET["course"] ?? "");

The application reads the search keyword and selected course from the URL.

The null coalescing operator (??) supplies a default value if a parameter is missing.

Step 2: Build the SQL conditions

$where[] = "(name LIKE :search_name
             OR email LIKE :search_email)";

This condition searches both the student's name and email address.

The course filter adds another condition:

$where[] = "course = :course";

When both conditions are selected, AND ensures that results must match the search and the chosen course.

Step 3: Count matching records

SELECT COUNT(*) FROM students

The application adds the same search and filter conditions to the count query.

This is important because pagination must be based on the number of matching records, not the total number of students in the database.

Step 4: Calculate the number of pages

$totalPages = (int) ceil($totalRecords / $limit);

The ceil() function rounds the result upward.

For example, 25 matching records with 10 records per page require three pages.

Step 5: Retrieve records for the current page

LIMIT :limit OFFSET :offset

This prevents the application from retrieving every matching record just to display a single page.

Step 6: Preserve filters in pagination links

$queryParams["page"] = $pageNumber;

return "index.php?" . http_build_query($queryParams);

This preserves the active search keyword and course when the user moves between pages.

Without this step, clicking the next page could remove the selected filters.

Step 7: Escape output

<?= escapeHtml($student["name"]) ?>

The existing escapeHtml() helper protects displayed values from being interpreted as HTML markup.

7. Update the CSS for Search, Filters, and Pagination

Open style.css and add the following styles at the end of the existing file.

.filter-form {
    display: flex;
    flex-wrap: wrap;
    align-items: flex-end;
    gap: 15px;
    margin: 20px 0;
    padding: 20px;
    background: #f8fafc;
    border: 1px solid #e2e8f0;
    border-radius: 8px;
}

.form-group {
    flex: 1 1 220px;
}

.form-group label {
    margin-top: 0;
}

.form-group input,
.form-group select {
    width: 100%;
    padding: 10px;
    border: 1px solid #cbd5e1;
    border-radius: 5px;
    background: #ffffff;
    font-size: 14px;
}

.filter-form button,
.filter-form .button {
    margin-top: 0;
}

.button.secondary {
    background: #64748b;
}

.result-info {
    color: #475569;
    margin: 15px 0;
}

.pagination {
    display: flex;
    flex-wrap: wrap;
    justify-content: center;
    gap: 8px;
    margin-top: 25px;
}

.pagination a {
    display: inline-block;
    padding: 9px 13px;
    color: #1769aa;
    text-decoration: none;
    border: 1px solid #cbd5e1;
    border-radius: 5px;
    background: #ffffff;
}

.pagination a:hover,
.pagination a.active {
    background: #1769aa;
    border-color: #1769aa;
    color: #ffffff;
}

@media (max-width: 600px) {
    .filter-form {
        padding: 15px;
    }

    .form-group {
        flex-basis: 100%;
    }

    .pagination a {
        padding: 8px 10px;
    }
}

These styles provide a responsive search form, a readable result count, and clear pagination links.

8. Run and Test the Application

Save the updated files and start Apache and MySQL.

Open:

http://localhost/student_management/

Test the following scenarios:

Test	Expected result
Open the listing page	Students appear in descending ID order
Search by student name	Matching names appear
Search by email	Matching email addresses appear
Select a course	Only students in that course appear
Search and select a course	Both conditions apply
Click Next	The next set of records appears
Navigate while searching	Search keyword remains active
Navigate while filtering	Selected course remains active
Click Reset	Search and filters are cleared
Search for a nonexistent student	A no-records message appears
Enter an invalid page number	The application handles it safely
Open a page beyond the last page	The application uses the last available page

For meaningful pagination testing, create more than 10 student records.

Also verify that the existing Add, Edit, and Delete links continue to work.

9. Common Problems and Solutions

Problem 1: Search returns no results

Possible cause: The keyword does not match any record, or the database column names differ from the code.

Solution: Check the students table and confirm that the query uses the correct column names.

Problem 2: The course dropdown is empty

Possible cause: The table has no records, or the course column contains no available values.

Solution: Add student records and confirm that their course values are populated.

Problem 3: Pagination always shows one page

Possible cause: There are fewer than 11 matching records when the page size is 10.

Solution: Add more records or change the $limit value temporarily for testing.

Problem 4: Search works, but pagination loses the filter

Possible cause: Pagination URLs do not contain the current search and filter parameters.

Solution: Use http_build_query() to preserve the active parameters, as demonstrated in this tutorial.

Problem 5: A database error appears near LIMIT or OFFSET

Possible cause: The pagination parameters may be bound incorrectly or the SQL syntax may differ from the database driver.

Solution: Confirm that both values are integers and are bound using PDO::PARAM_INT.

Problem 6: The page is slow with a large database

Possible cause: Searching with leading wildcards, such as %keyword%, can require scanning many records.

Solution: Consider database indexing strategies, query analysis, and alternative search designs appropriate to your requirements. A standard B-tree index generally cannot efficiently optimize a leading-wildcard search.

10. Security and Performance Best Practices

Search and pagination should be implemented with security and performance in mind.

• Use prepared statements: Bind search and filter values instead of concatenating user input directly into SQL.

• Validate page numbers: Accept positive integers and handle invalid or out-of-range values.

• Escape HTML output: Continue using htmlspecialchars() through the existing escapeHtml() helper.

• Use a fixed page size: Do not allow unrestricted user-controlled limits.

• Preserve authorization checks: Authentication and permissions must be enforced on protected records and actions.

• Protect state-changing requests: Add CSRF protection to forms such as Delete, and require appropriate authorization.

• Use appropriate indexes: Add indexes based on actual query patterns and confirm their usefulness with EXPLAIN.

• Avoid excessive results: Retrieve only the records required for the current page.

• Handle database errors safely: Log technical details privately and show visitors a generic error message.

Important note: This tutorial uses LIKE '%keyword%' for a simple substring search. For larger databases, full-text search or a dedicated search service may be more appropriate, depending on the required matching behavior.

11. SEO Considerations for Search and Pagination

Although this is a PHP programming tutorial, it is also useful to understand how search and pagination affect websites.

For public-facing websites, search result URLs can create many combinations of query parameters. These pages do not automatically need to be indexed by search engines.

Consider the following practices:

• Keep important public landing pages accessible through stable, descriptive URLs.

• Avoid generating indexable URLs for every possible internal search combination unless there is a clear SEO purpose.

• Use canonical URLs appropriately when multiple URLs represent substantially the same content.

• Do not assume that adding pagination automatically improves search rankings.

• Ensure that public pages provide useful, unique content rather than thin or duplicated pages.

• Keep internal application search pages separate from the website's main SEO content strategy where appropriate.

For this Student Management System, the main goal is to improve usability and database efficiency, not to make private student records searchable by search engines.

Never expose personal student information through publicly accessible pages.

12. Practice Exercises

Try the following exercises after completing the tutorial.

Beginner exercises

1. Add a search option for the student's course.

2. Display the total number of students alongside the matching record count.

3. Change the number of records displayed per page from 10 to 5.

4. Display a message when no matching students are found.

5. Improve the search form styling using CSS.

Intermediate exercises

1. Add sorting by student name.

2. Add a date filter using the created_at column.

3. Display the current page number and total pages.

4. Add a selectable page-size dropdown with fixed, validated options.

5. Add an email uniqueness constraint and handle duplicate registration errors properly.

Mini project challenge

Extend the Student Management System with:

• Search by name, email, and course.

• Course-based filtering.

• Pagination and sorting.

• A student details page.

• Login and logout.

• Role-based access for administrators and staff.

• Server-side authorization checks.

• Secure form submissions and CSRF protection.

Build and test each feature separately before combining them.

13. Frequently Asked Questions (FAQs)

Q1. How do I search records in PHP using MySQL?

You can use a SQL SELECT query with the LIKE operator and a PDO prepared statement to search records by a keyword.

Q2. What is pagination in PHP?

Pagination divides a large set of database results into smaller pages, making records easier to navigate and reducing the amount of data displayed at once.

Q3. What are LIMIT and OFFSET in MySQL?

LIMIT controls the maximum number of records returned, while OFFSET specifies how many matching records to skip before returning results.

Q4. Can I use search, filtering, and pagination together?

Yes. You can combine search and filter conditions in the SQL query and apply LIMIT and OFFSET to the matching results.

Q5. Why should I use PDO prepared statements?

Prepared statements separate SQL instructions from supplied values and help prevent SQL injection when used correctly.

Q6. How many records should I display per page?

There is no universal number. Ten, twenty, or fifty records per page can be reasonable starting points depending on the interface and the size of each record.

Q7. Do I need a new database for this tutorial?

No. You can reuse the student_db database and students table created in Module 10.

Q8. Does pagination improve SEO automatically?

No. Pagination mainly improves navigation and usability. SEO depends on content quality, crawlability, canonicalization, internal linking, and other factors.

Conclusion

In this module, we enhanced our existing Student Management System by implementing search, course filtering, and pagination using Core PHP, MySQL, and PDO.

You learned how to:

• Search records by name or email.

• Filter records by course.

• Count matching database records.

• Calculate page numbers and offsets.

• Retrieve a limited number of records.

• Preserve search and filter values across pages.

• Use prepared statements and escape HTML output.

• Test and troubleshoot a database-driven listing page.

These techniques are useful in many real-world PHP applications, including employee management systems, inventory dashboards, e-commerce catalogs, and administrative panels.