How to Implement Dynamic Pagination in MySQL Using LIMIT and OFFSET?

Pagination Using MySQL LIMIT and OFFSET

Introduction

Pagination is essential for managing large datasets efficiently, allowing users to browse data in smaller, manageable pages. MySQL provides two keywords, LIMIT and OFFSET, to implement pagination.

Understanding the Problem

The provided code uses a fixed limit of 4 items per page, which works for a predetermined database size. However, the goal is to create dynamic pagination without hard-coding page offsets.

Implementing Dynamic Pagination

To create dynamic pagination, we need to:

1. Determine the current page number from the URL parameter ($_GET['page']).
2. Define the number of items to display per page ($items_per_page).
3. Calculate the offset for the current page: $offset = ($page - 1) * $items_per_page.
4. Build the SQL query using LIMIT $offset, $items_per_page.

Calculating the Total Pages

To determine if a "NEXT PAGE" link should be displayed, we need to know the total number of pages:

1. Execute a separate SQL query to count the total number of rows in the table: $row_count = mysqli_num_rows(mysqli_query($con, "SELECT your_primary_key_field FROM menuitem"));.
2. Calculate the total number of pages: $page_count = (int)ceil($row_count / $items_per_page).

Displaying Pagination Links

Using the current page number ($page) and the total pages ($page_count), you can dynamically generate links for pagination. For instance, a loop could iterate over the pages and create links for each one. The current page would be displayed as text, while other pages would be displayed as links.

Sample Code

// Get the current page number from the URL
$page = 1;
if (isset($_GET['page'])) {
    $page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);
    if ($page === false) {
        $page = 1;
    }
}

// Set the number of items to display per page
$items_per_page = 4;

// Build the query
$offset = ($page - 1) * $items_per_page;
$sql = "SELECT * FROM menuitem LIMIT $offset, $items_per_page";

// Execute the query and fetch the results

// Calculate the total number of rows
$sql_count = "SELECT COUNT(*) AS row_count FROM menuitem";
$result_count = mysqli_query($con, $sql_count);
$row_count = mysqli_num_rows($result_count);
$page_count = (int)ceil($row_count / $items_per_page);

// Check if the requested page is in range
if ($page > $page_count) {
    // Display an error or set the page to 1
}

// Later, when outputting the page, you can use $page and $page_count to generate pagination links

The above is the detailed content of How to Implement Dynamic Pagination in MySQL Using LIMIT and OFFSET?. For more information, please follow other related articles on the PHP Chinese website!