Simple PHP MySQL page system

A simple PHP MySQL page system written as a function with code comments. For this example i had the following in the database

Simple color names and an id, With an incrementing id or date using ORDER BY DESC gets the latest/most recent entry first than descends through to oldest.

I defined the code below so that Page 1 (which is actually page=0 in this example) shows id 15 to 12, Page 2 shows id 11 to 8, Page 3 shows id 7 to 4, Page 4 shows id 3 to 1.

Ignore the lack of styling (use buttons or icons for page changers) You can see that the first page does not have a previous page button and the last page does not have a next page button.

Here is the code all in a function with comments Gist link:

<?php
function pagination_system($page_number, $items_per_page)
{
    if ($page_number == 0) {
        $start_at = 0;//Items 0 to $items_per_page
    } else {
        $start_at = ($page_number * $items_per_page);//Assume itemsPP = 4. For Page 2 (1*4) so it shows items 4 to 8
        //For Page 3 (?page=2) equation is (2*4) so page=2 will show items 8 to 12
    }
    global $db;//Call DB connection details
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);//Allows to define a prepared statement for the LIMIT definer
    $statement = $db->prepare("SELECT `id`, `color` FROM `objects` ORDER BY `id` DESC LIMIT ?, $items_per_page");//Query
    $statement->execute(array($start_at));//Execute query
    $row_counter = 0;//Row counter start at 0
    while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {//Loop through the returned data
        echo "[" . $row['id'] . "] " . $row['color'] . "<br>";//Example output for this
        $row_counter++;//Adds 1 to row counter
    }
    $prev_page = $page_number - 1;//Previous page is current - 1
    $next_page = $page_number + 1;//next page is current + 1
    $is_last_page = 0;//Pre set
    if ($row_counter <= ($items_per_page - 1)) {//If less rows returned than itemsPP it is the last page
        $is_last_page = 1;
    }
    if ($page_number == 0) {
        if ($row_counter < $items_per_page) {
            echo "";//items per page is more than the items we have to paginate = no pagination needed.
        } else {//Is first page (0) DONT have a prev button
            echo "<a href='?page=$next_page'>[Next]</a>";//Change to Button/icon
        }
    } elseif ($is_last_page == 1) {//Is last page DONT have a next button
        echo "<a href='?page=$prev_page'>[Prev]</a>";//Change to Button/icon
    } else {
        echo "<a href='?page=$prev_page'>[Prev]</a>";//Change to Button/icon
        echo "<a href='?page=$next_page'>[Next]</a>";//Change to Button/icon
    }
}

This is called by

pagination_system($page, 4);

The function and call are put in page_system.php with the following up the top to determine the page number:

if (isset($_GET['page'])) {
    $page = $_GET['page'];//If ?page= is set use it
} else {
    $page = 0;//No ?page= found so we are at page 1 which is a zero
}

Don’t forget the MySQL PDO connection either before the function:

$db = new PDO('mysql:host=localhost;dbname=DATABASE;charset=utf8mb4', 'USERNAME', 'PASSWORD');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);