Home > Backend Development > PHP Tutorial > How do you implement pagination in MySQL using LIMIT and OFFSET?

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

DDD
Release: 2024-11-17 12:13:01
Original
334 people have browsed it

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:

1

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

Copy after login

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

1

2

$offset = 4;

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

Copy after login

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:

1

2

3

4

5

$sql = "SELECT COUNT(*) FROM menuitem";

$result = mysqli_query($con, $sql);

$row_count = mysqli_num_rows($result);

// Free the result set

mysqli_free_result($result);

Copy after login

Now, you can calculate the number of pages:

1

2

$items_per_page = 4;

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

Copy after login

Generating Page Links:

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

1

2

3

4

5

6

7

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>';

  }

}

Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template