How to Implement Pagination in MySQL: A Comprehensive Guide Using LIMIT and OFFSET?

Pagination using MySQL LIMIT and OFFSET: A Comprehensive Guide

Pagination is a crucial technique used to display data in manageable chunks on web pages. In this context, MySQL's LIMIT and OFFSET operators play a vital role in achieving pagination. This article provides a detailed explanation of pagination using these operators, along with a real-world example.

Consider a scenario where you have a table named menuitem containing 20-30 menu items. Your goal is to display these items in a paginated manner, with four items per page. To achieve this, you initially use the following code to display the first page:

$result = mysqli_query($con,"SELECT * FROM menuitem LIMIT 4");

This query displays the first four items. However, to handle additional pages, you need a mechanism to determine the number of available pages dynamically. This can be achieved by calculating the total row count in the table:

$sql = "SELECT your_primary_key_field FROM menuitem";
$result = mysqli_query($con, $sql);
$row_count = mysqli_num_rows($result);

Next, calculate the number of pages based on the total row count and the number of items per page:

$page_count = 0;
if (0 === $row_count) {  
    // Handle the case where the table is empty
} else {
   $page_count = (int)ceil($row_count / $items_per_page);
}

To navigate between pages, you can use a URL parameter such as page. For example, the URL http://yoursite.com/itempage.php?page=2 would display the second page. To handle this request, you can modify your SQL query as follows:

// determine page number from $_GET
$page = 1;
if(!empty($_GET['page'])) {
    $page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);
    if(false === $page) {
        $page = 1;
    }
}
// set the number of items to display per page
$items_per_page = 4;

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

This query dynamically calculates the offset based on the requested page number, allowing you to display the appropriate page of results.

To display the pagination links, you can use a loop to generate links for all available pages:

for ($i = 1; $i <= $page_count; $i++) {
   if ($i === $page) { 
       echo 'Page ' . $i . '<br>';
   } else {
       echo '<a href="/menuitem.php?page=' . $i . '">Page ' . $i . '</a><br>';
   }
}

By following these steps, you can effectively implement pagination using MySQL LIMIT and OFFSET, providing a user-friendly and efficient way to display large data sets in a structured manner.

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