How to Add Search and Pagination to a PHP and MySQL Listing Page

To add search and pagination to a PHP and MySQL listing page, you filter rows with a LIKE search query, count how many rows match, then load only one small batch at a time using LIMIT and OFFSET, with numbered page links that keep the search word in the URL. Done with prepared statements throughout, this is safe against SQL injection and stays fast as your table grows. This guide upgrades a plain listing page step by step, with working code you can copy into your own project.

Almost every beginner project reaches this moment. Your list of students, products or posts looks great with twenty rows. Then it grows to 300, then 3,000, and the page slows down while finding one record feels like searching a drawer full of mixed socks. Search fixes the finding. Pagination fixes the loading — and you will reuse both in nearly every project after this one.

What You Are Building

You will start from a normal listing page that prints every row in a table, and add two things: a search box at the top, and page links like "1, 2, 3, Next" at the bottom, showing ten rows per page. The search word and the page number both travel in the URL, so a results page can be bookmarked or shared.

This builds directly on our project to build a simple CRUD app in PHP and MySQL, which created a students table with add, view, edit and delete pages. We use PDO here because its named placeholders keep longer queries readable; if that choice is new to you, our comparison of MySQLi vs PDO and what the difference is explains both styles. The ideas work in MySQLi too — only the binding syntax changes.

Step 1: The Search Form and the URL Values

The form itself is tiny. Use GET, not POST, so the search word lands in the URL where your page links can carry it along:

<form method="get">
    <input type="search" name="q" value="<?= htmlspecialchars($search) ?>" placeholder="Search students...">
    <button type="submit">Search</button>
</form>

At the top of the page, read the two URL values and clean them before they go anywhere near the database. The page number gets cast to an integer, so playful input like ?page=-500 becomes a harmless number:

$search  = trim($_GET["q"] ?? "");
$page    = (int) ($_GET["page"] ?? 1);
if ($page < 1) { $page = 1; }
$perPage = 10;
$like    = "%" . $search . "%";

That last line builds the pattern for the search: the % signs mean "anything can come before or after", so searching for asha also finds Asharma. When the box is empty, the pattern becomes %%, which matches every row — so one query handles both "show all" and "search", with no extra branching.

Step 2: Count the Matching Rows

Pagination needs one number before anything else: how many rows match the search? That total decides how many page links to draw. If 47 students match and you show ten per page, you need five pages. A count query asks the database for just that number, so it stays fast even on big tables:

$count = $pdo->prepare("SELECT COUNT(*) FROM students WHERE name LIKE :term");
$count->execute(["term" => $like]);
$totalRows  = (int) $count->fetchColumn();
$totalPages = (int) ceil($totalRows / $perPage);
if ($page > $totalPages && $totalPages > 0) { $page = $totalPages; }

Notice the search condition here. It must be identical in the count query and in the row query below. If they drift apart, your page links will promise pages that do not exist. The last line also rescues anyone who lands on page 99 when only three pages exist, by sending them to the last real page.

Step 3: Fetch One Page With LIMIT and OFFSET

Now fetch only the rows for the current page. LIMIT 10 means "give me ten rows", and OFFSET 20 means "skip the first twenty". The offset is always ($page - 1) * $perPage, so page 1 skips nothing, page 2 skips ten, and so on:

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

$stmt = $pdo->prepare(
    "SELECT id, name, marks FROM students
     WHERE name LIKE :term
     ORDER BY id
     LIMIT :limit OFFSET :offset"
);
$stmt->bindValue(":term", $like);
$stmt->bindValue(":limit", $perPage, PDO::PARAM_INT);
$stmt->bindValue(":offset", $offset, PDO::PARAM_INT);
$stmt->execute();
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);

Three details matter here. First, the search text is a bound value, never pasted into the SQL string — that separation is the whole point of prepared statements, and the PHP manual's page on PDO prepared statements explains why it blocks SQL injection so reliably. Second, LIMIT and OFFSET are bound as integers with PDO::PARAM_INT. Third, this query only ever loads ten rows, whether the table holds 300 or 30,000. The MySQL manual's notes on LIMIT query optimization explain why small, ordered batches perform so well compared with loading everything and slicing in PHP.

Step 4: Show the Rows — and the Empty State

Printing the rows is the familiar loop, with one upgrade that makes the page feel professional: a friendly message when the search finds nothing, instead of a blank table that looks broken.

<?php foreach ($students as $s): ?>
    <tr>
        <td><?= (int) $s["id"] ?></td>
        <td><?= htmlspecialchars($s["name"]) ?></td>
        <td><?= (int) $s["marks"] ?></td>
    </tr>
<?php endforeach; ?>

<?php if (empty($students)): ?>
    <p>No students found for "<?= htmlspecialchars($search) ?>". Try a different name.</p>
<?php endif; ?>

Every printed value goes through htmlspecialchars() or an integer cast — including the search word itself, echoed back in the empty-state message. Skipping that is how cross-site scripting sneaks into careful projects.

Step 5: Page Links That Keep the Search

The classic beginner bug is page links that forget the search: you search for "asha", click page 2, and suddenly you are browsing everyone again. The fix is to carry the search word inside every link, encoded safely:

<?php for ($i = 1; $i <= $totalPages; $i++): ?>
    <a href="?q=<?= urlencode($search) ?>&page=<?= $i ?>"><?= $i ?></a>
<?php endfor; ?>

urlencode() handles search words with spaces or special characters. Adding simple "Previous" and "Next" links on either side is a kind extra — most visitors reach for those instead of hunting for a number.

Common Mistakes to Avoid

One honest limit: OFFSET makes the database count past every earlier row, so page 500 of a huge table is slower than page 2. For a project with a few thousand rows you will never notice. If you ever do, the remedy is keyset pagination — remembering the last ID you saw and asking for rows after it.

Frequently Asked Questions

How do I search across several columns, like name and city?

Add one LIKE condition per column, joined with OR, each with its own placeholder bound to the same pattern. Use the identical conditions in the count query, or your page numbers will be wrong.

How many rows per page should I show?

Ten to twenty suits most tables. Fewer feels clicky, more slows the page down. Keep the number in one variable like $perPage so you can change it in a single place.

Can I add sorting, like highest marks first?

Yes — but build the ORDER BY column from a fixed allowlist in your code, never from raw URL input. Column names cannot be bound as placeholders, so the allowlist is your safety net.

Is LIKE search fast enough for a big table?

For tens of thousands of rows, yes. Beyond that, look at a full-text index for the searched column — but only optimise when you actually have that much data.

Conclusion

Search and pagination turn a fragile "show everything" page into one that stays fast as your data grows. The recipe is short: clean the URL input, count the matches, fetch one page with LIMIT and OFFSET, keep the search word in every page link, and escape everything you print. Build it once this way, and every future listing page gets the same reliable upgrade.