How do I Implement Pagination With MySQL LIMIT and OFFSET?

Pagination Using MYSQL LIMIT, OFFSET

Pagination is a technique used to display large datasets in manageable chunks or pages. In MySQL, this can be achieved using the LIMIT and OFFSET clauses.

The Question

A user has a database table with a large number of items and wants to implement pagination on a web page. The user has already set up code to display a fixed number of items (e.g., 4) per page, but they need to dynamically determine the total number of pages and allow users to navigate between them.

The Answer

To achieve this, it's recommended to use a URL query parameter for pagination. The parameter should represent the page number, such as "/itempage.php?page=2". In the server-side code, you can retrieve the page number using $_GET['page' and use it to calculate the LIMIT and OFFSET values for your SQL query.

For example:

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

$items_per_page = 4;
$offset = ($page - 1) * $items_per_page;

// Build the SQL query
$sql = "SELECT * FROM menuitem LIMIT " . $offset . "," . $items_per_page;

To determine the total number of pages, execute a separate SQL query before limiting the results:

$sql = "SELECT COUNT(*) AS total_rows FROM menuitem";
$result = mysqli_query($con, $sql);
$row_count = mysqli_num_rows($result);
mysqli_free_result($result);

$page_count = 0;
if ($row_count === 0) {
    // Handle empty table error
} else {
    $page_count = ceil($row_count / $items_per_page);

    if ($page > $page_count) {
        // Handle out-of-range page request
    }
}

With the total page count and current page number, you can now dynamically generate navigation links for the paginated content.

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