How to Implement Database Pagination in PHP using MySQL's LIMIT and OFFSET?

Pagination using MySQL LIMIT, OFFSET with Response

Pagination allows splitting a large dataset into smaller, manageable pages, a common technique in web development. MySQL offers LIMIT and OFFSET clauses to handle pagination.

Problem:

You need to create multiple pages to display data from a database table with a predefined number of items per page, but without hardcoding the pagination logic.

Solution:

Use a pagination parameter in the URL, such as:

http://yoursite.com/itempage.php?page=2

You can access the requested page number using $_GET['page'].

Modify your SQL query to dynamically calculate the offset based on the page number:

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

To determine the total number of pages, you need to count the number of rows in the table:

$sql = "SELECT your_primary_key_field FROM menuitem";
$result = mysqli_query($con, $sql);
$row_count = mysqli_num_rows($result);
// free the result set as you don't need it anymore
mysqli_free_result($result);

$page_count = 0;
if (0 === $row_count) {  
    // maybe show some error since there is nothing in your table
} else {
   // determine page_count
   $page_count = ceil($row_count / $items_per_page);
   // double check that request page is in range
   if($page > $page_count) {
        // error to user, maybe set page to 1
        $page = 1;
   }
}

When displaying the paginated data, you can use the $page and $page_count variables to generate appropriate links:

// later when outputting page, you can simply work with $page and $page_count to output links
// for example
for ($i = 1; $i <= $page_count; $i++) {
   if ($i === $page) { // this is current page
       echo 'Page ' . $i . '<br>';
   } else { // show link to other page   
       echo '<a href=&quot;/menuitem.php?page=' . $i . '&quot;>Page ' . $i . '</a><br>';
   }
}

The above is the detailed content of How to Implement Database Pagination in PHP using MySQL's LIMIT and OFFSET?. For more information, please follow other related articles on the PHP Chinese website!