How do you implement pagination in MySQL using LIMIT and OFFSET?

Using MySQL LIMIT and OFFSET for Pagination

Pagination is essential for displaying large datasets in manageable chunks. MySQL provides two commands, LIMIT and OFFSET, to enable this functionality.

LIMIT sets the maximum number of rows to retrieve, while OFFSET specifies the number of rows to skip before starting retrieval. This allows you to retrieve a specific page of data.

Sample Code for Pagination:

Consider the following code, which displays four items per page:

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

This code will retrieve the first four rows from the menuitem table. To create multiple pages, you can use OFFSET to skip rows:

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

This code will skip the first four rows and retrieve the next four.

Determining the Total Number of Pages:

To create pagination without hard-coding page numbers, you must first determine the total number of pages. You can achieve this by counting the total number of rows in the table:

$sql = "SELECT COUNT(*) FROM menuitem";
$result = mysqli_query($con, $sql);
$row_count = mysqli_num_rows($result);
// Free the result set
mysqli_free_result($result);

Now, you can calculate the number of pages:

$items_per_page = 4;
$page_count = (int)ceil($row_count / $items_per_page);

Generating Page Links:

With the total number of pages known, you can generate page links:

for ($i = 1; $i <= $page_count; $i++) {
  if ($i === $page) { // This is the current page
    echo 'Page ' . $i . '<br>';
  } else { // Show link to other page
    echo '<a href="/menuitem.php?page=' . $i . '">Page ' . $i . '</a><br>';
  }
}

This will generate links to all pages, highlighting the current page.

By utilizing this approach, you can create pagination without hard-coding page numbers and ensure flexibility in displaying large datasets.

The above is the detailed content of How do you implement pagination in MySQL using LIMIT and OFFSET?. For more information, please follow other related articles on the PHP Chinese website!