Pagination using MySQL LIMIT, OFFSET

Question

First off, don’t have a separate server script for each page, that is just madness. Most applications implement pagination via use of a pagination parameter in the URL. Something like:

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

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

This makes your SQL formulation really easy:

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

So for example if input here was page=2, with 4 rows per page, your query would be:

SELECT * FROM menuitem LIMIT 4,4

So that is the basic problem of pagination. Now, you have the added requirement that you want to understand the total number of pages (so that you can determine if “NEXT PAGE” should be shown or if you wanted to allow direct access to page X via a link).

In order to do this, you must understand the number of rows in the table.

You can simply do this with a DB call before trying to return your actual limited record set (I say BEFORE since you obviously want to validate that the requested page exists).

This is actually quite simple:

$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 = (int)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;
   }
}

// make your LIMIT query here as shown above


// 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="/menuitem.php?page=" . $i . "">Page ' . $i . '</a><br>';
   }
}

Leave a Comment Cancel reply