How to Dynamically Generate Pagination Links in MySQL Using LIMIT and OFFSET?

Pagination using MySQL LIMIT, OFFSET: Dynamic Page Creation

Pagination allows users to navigate large datasets by displaying a limited number of items per page. By utilizing the MySQL LIMIT and OFFSET clauses, we can efficiently retrieve specific pages of data.

However, it's inefficient to create a separate page for each potential page number. Instead, we can dynamically determine the number of pages based on the total number of rows in the database.

Determining the Number of Pages

To find the total number of pages, we must first calculate the total number of rows in the table using a separate query.

SELECT COUNT(*) FROM menuitem

This will return a single row with a column containing the total row count.

Calculating Page Count

We can then calculate the total number of pages by dividing the total rows by the desired items per page and rounding up to the nearest integer.

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

Adjusting for Invalid Page Requests

When a user requests a page that doesn't exist (e.g., page 5 when there are only 3), we can redirect them to the last page or the first page, depending on the situation.

Outputting Page Links

Once we know the total number of pages, we can generate a list of page links. For the current page, we display it as text, while for other pages, we create links to them.

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

Revised Code

Using the dynamic page calculation method, your revised code could look something like this:

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

// Get total row count
$sql = "SELECT COUNT(*) FROM menuitem";
$result = mysqli_query($con, $sql);
$row_count = mysqli_num_rows($result);
mysqli_free_result($result);

// Calculate page count
$page_count = (int)ceil($row_count / $items_per_page);

// Double check page is in range
if ($page > $page_count) {
  $page = 1;
}

// Calculate offset
$offset = ($page - 1) * $items_per_page;

// Select limited data
$sql = "SELECT * FROM menuitem LIMIT " . $offset . "," . $items_per_page;
$result = mysqli_query($con, $sql);
// ... (Rest of your code) ...

// Output page links
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>';
  }
}

This code allows you to dynamically create pages based on the number of rows in the database, eliminating the need for hard-coded page numbers and offsets.

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