How PHP Search and Pagination Work Together

Search and pagination are two parts of the same database operation. The search decides which records belong in the result set. Pagination decides which small section of that filtered result set should appear on the current page.

A reliable implementation normally runs two related queries. The count query calculates how many filtered records exist. The data query uses the same filters, adds a stable order and retrieves one page through LIMIT and OFFSET.

The Correct LIMIT and OFFSET Calculation

User-facing page numbers usually begin at one, while SQL offsets begin at zero. Therefore, page one begins at offset zero. Page two begins after one complete page of records.

$offset = ($currentPage - 1) * $recordsPerPage;

Using $currentPage * $recordsPerPage skips the first group because page one starts at offset ten. Failing to apply the calculated offset causes every page to return the same first records.

Validate the Page Number Before SQL

A page number comes from the browser and must not be trusted. It may be missing, zero, negative, decimal text or extremely large.

$currentPage = filter_input(
    INPUT_GET,
    "page",
    FILTER_VALIDATE_INT
);

$currentPage = max(1, $currentPage ?: 1);

After the count query calculates the total pages, clamp the requested value again. A request for page 500 should not silently produce a confusing empty page when only five pages exist.

$totalPages = max(
    1,
    (int) ceil($totalRecords / $recordsPerPage)
);

$currentPage = min($currentPage, $totalPages);

Why Page 2 Repeats Page 1

Repeated pages usually mean the offset was calculated but never added to the executed SQL, the offset variable remains zero, or the page parameter is read from the wrong request key.

Inspect these values together:

var_dump([
    "page" => $currentPage,
    "per_page" => $recordsPerPage,
    "offset" => $offset
]);

Also inspect the IDs returned on each page. Page one and page two should not contain the same complete ID sequence when the order and dataset remain unchanged.

Why Search and Filters Disappear on Page 2

A page link such as ?page=2 replaces the existing query string. If the active keyword, status, category or sort option is not included, PHP receives only the page number and rebuilds an unfiltered query.

Build links from all active parameters:

$query = http_build_query([
    "search" => $search,
    "status" => $status,
    "sort" => $sort,
    "page" => $pageNumber
]);

$url = "records.php?" . $query;

This also handles spaces and special URL characters more safely than manually joining raw values with ampersands.

The Count Query Must Match the Data Query

The total-page calculation is only correct when the count query represents the same filtered dataset as the data query.

These conditions must match:

• Search keyword and searched columns
• Status, category and date filters
• User ownership or role restrictions
• Relevant JOIN conditions
• Soft-delete conditions
• Tenant or organization restrictions

SELECT COUNT(*)
FROM products
WHERE name LIKE ?
  AND status = ?;

SELECT id, name, status
FROM products
WHERE name LIKE ?
  AND status = ?
ORDER BY id DESC
LIMIT ? OFFSET ?;

Use Prepared Statements for Search Values

Do not concatenate a browser-provided keyword directly into SQL. Create the wildcard pattern as a value and bind that value to a placeholder.

$search = trim($_GET["search"] ?? "");
$pattern = "%" . $search . "%";

$stmt = $pdo->prepare(
    "SELECT id, name
     FROM products
     WHERE name LIKE :search"
);

$stmt->execute([
    "search" => $pattern
]);

For a multiple-column search, bind the pattern separately for each placeholder rather than inserting the original keyword into the SQL string.

Empty Search and LIKE %%

Adding percent signs around an empty keyword creates LIKE '%%', which normally matches every non-null value. This may be correct when an empty search should show all records, but it is a bug when the page is supposed to require a keyword.

Choose the behavior deliberately:

• Show all records when search is empty.
• Return no records until a keyword is entered.
• Display a validation message requiring a keyword.

Do not let an accidental empty pattern decide the product behavior.

Use a Safe Sorting Allowlist

Prepared-statement placeholders represent data values. They do not safely replace SQL column names or keywords in an ORDER BY clause. Therefore, user-selected sorting should map to server-controlled SQL.

$allowedSorts = [
    "newest" => "created_at DESC, id DESC",
    "oldest" => "created_at ASC, id ASC",
    "name" => "name ASC, id ASC"
];

$orderBy = $allowedSorts[$sort]
    ?? $allowedSorts["newest"];

The unique ID used as a secondary order makes pagination more stable when multiple records share the same name or date.

Why JOINs Produce Duplicate Records

A parent row is repeated when it matches several rows in a joined child table. For example, one product with three tags may appear three times after joining the product-tag relationship.

Possible solutions depend on the intended output:

• Correct an accidental JOIN condition.
• Use DISTINCT when the selected parent columns are identical.
• Group by the parent record when calculating aggregates.
• Load child collections in a separate query.
• Count distinct parent IDs in the count query.

Stable Ordering Prevents Missing and Repeated Rows

Pagination without ORDER BY does not define a reliable record order. Ordering only by a non-unique value, such as status or creation date, can also leave tied rows without a stable position.

ORDER BY created_at DESC, id DESC

Even with a stable order, new inserts and deletions between page requests can shift offset-based pagination. For ordinary student projects, stable offset pagination is usually acceptable. Larger real-time systems may use cursor-based pagination instead.

AJAX Load More Must Advance One State

A Load More feature must maintain one clear page or offset value. It should not increment both before and after a request, and it should not reset to page one when the search term changes unexpectedly.

The response should tell the browser:

• Which page was returned
• How many rows were returned
• Whether another page exists
• The normalized active filters

Disable the button while a request is pending to avoid two concurrent requests loading the same page.

Safe Debugging Order

1. Run the filtered data query without LIMIT and confirm the results.
2. Run the filtered count query and compare its total.
3. Validate the page and records-per-page values.
4. Calculate and display the expected offset.
5. Add stable ORDER BY, LIMIT and OFFSET.
6. Compare returned IDs between page one and page two.
7. Preserve all active query parameters in navigation links.
8. Test sorting, search and filters together.
9. Inspect JOINs when IDs are duplicated.
10. Test empty, invalid and out-of-range page requests.

Continue Your CodeZips PHP Project Workflow

Technical References

• MySQL Reference Manual: SELECT, LIMIT and OFFSET
• PHP Manual: PDO::prepare()
• PHP Manual: PDO Prepared Statements
• PHP Manual: MySQLi Prepared Statements
• PHP Manual: filter_input()
• PHP Manual: http_build_query()