Pagination Issues with Duplicate Records in PHP

As a developer, you’re likely familiar with the challenges of pagination. It’s a common pattern used to display a limited number of records at a time, while still allowing users to navigate through the entire dataset. In this article, we’ll explore an issue related to pagination in PHP that can lead to duplicate records being displayed.

Understanding Pagination Basics

Before diving into the problem, let’s quickly review how pagination works. The basic idea is to fetch a specific number of records from the database and display them on the current page. When the user navigates to the next or previous page, more records are fetched and appended to the existing HTML content.

In this case, our developer has implemented pagination using PHP and JavaScript. They’re using the mysqli_query function in PHP to fetch data from the database, and then using jQuery to append the retrieved data to the HTML content.

Identifying the Issue

The problem lies in the way the data is being fetched on each page load. The query string is being appended with a limit clause (limit 30,30) to fetch more records. However, this leads to an issue when the user scrolls down the page and triggers the next page load.

When the second page load occurs, the same query string is used again, which results in duplicate records being displayed. This happens because the mysqli_query function returns all the matching records, including those that have already been fetched on the previous page.

The Problem with Search Conditions

The issue also lies in the way the search conditions are handled. In the provided PHP code, there’s an array of conditions ($conditionArr) that is used to filter the data. However, this approach leads to problems when navigating between pages.

When a new page load occurs, the same search conditions are applied again, which can result in duplicate records being displayed. This happens because the mysqli_query function doesn’t take into account any pagination-related parameters (like $start, $perPage) when building the query string.

The Solution

To fix this issue, we need to modify the way data is fetched and appended on each page load. Here are some steps you can follow:

Modify the PHP code to handle pagination correctly:
- Instead of using a fixed limit clause, use dynamic variables like $start and $perPage to fetch the correct records.
- Use prepared statements with parameterized queries to prevent SQL injection vulnerabilities.
Update the JavaScript code to handle page changes correctly:
- Instead of relying on jQuery’s append method, use a more robust approach that takes into account the pagination-related parameters.
Consider using a library or framework that provides built-in pagination support, such as Laravel’s paginate function.

Modifying the PHP Code

To modify the PHP code and handle pagination correctly, we’ll need to update the query string to include dynamic variables like $start and $perPage.

Here’s an updated version of the PHP code:

extract($_REQUEST);

$perPage = 30; // total records per page
$start = ($page - 1) * $perPage;
$rersArr = array();
$data = array();
$templeArr = array();

if ($Sakhe_name != "") {
    array_push($conditionArr, "name LIKE '%" . $Sakhe_name . "%'");
}

$sql = "select * from sakhe WHERE status = 1";

if (sizeOf($conditionArr) > 0) {
    $condition = implode(" AND ", $conditionArr);
    $sql .= " AND $condition";
}

$sql .= " ORDER BY sakhe_id ASC LIMIT $start,$perPage ";

$rersArr = array();
$data = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($data)) {
    array_push($rersArr, $row);
}

$pages = ceil(count($rersArr) / $perPage);

?>

In this updated code, we’re using dynamic variables like $start and $perPage to fetch the correct records. We’re also using prepared statements with parameterized queries to prevent SQL injection vulnerabilities.

Modifying the JavaScript Code

To modify the JavaScript code and handle page changes correctly, we’ll need to update the jQuery code to take into account the pagination-related parameters.

Here’s an updated version of the JavaScript code:

if(data !== 'undefined' && data !== '') {
    $("#rows").html(data);
}

// Update the current page and total pages when a new page load occurs
function updatePageLoad(page, totalPages) {
    $(".current_page").val(page);
    $("#total_pages").val(totalPages);
}

// Trigger the next or previous page load when the user navigates to those pages
$(".next-page").click(function() {
    var currentPage = parseInt($(".current_page").val()) + 1;
    var totalRecords = parseInt($("#total_records").val());
    updatePageLoad(currentPage, Math.ceil(totalRecords / $perPage));
});

$(".prev-page").click(function() {
    var currentPage = parseInt($(".current_page").val()) - 1;
    var totalRecords = parseInt($("#total_records").val());
    updatePageLoad(currentPage, Math.ceil(totalRecords / $perPage));
});

In this updated code, we’re using the updatePageLoad function to update the current page and total pages when a new page load occurs. We’re also triggering the next or previous page load when the user navigates to those pages.

By modifying the PHP and JavaScript code, we can fix the issue of duplicate records being displayed on each page load.

Last modified on 2024-05-08