Simple PHP Pagination Script to Handle Your Growing Data

<?php
$host = "CHANGE_TO_YOUR_HOST";
$db_name = "CHANGE_TO_YOUR_DATABASE_NAME";
$username = "CHANGE_TO_YOUR_DATABASE_USERNAME";
$password = "CHANGE_TO_YOUR_DATABASE_PASSWORD";
 
try {
    $con = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
}
 
//to handle connection error
catch(PDOException $exception){
    echo "Connection error: " . $exception->getMessage();
}
?>

<?php
// include for database connection
include 'libs/db_connect.php';
 
// page is the current page, if there's nothing set, default is page 1
$page = isset($_GET['page']) ? $_GET['page'] : 1;
 
// set records or rows of data per page
$recordsPerPage = 3;
 
// calculate for the query LIMIT clause
$fromRecordNum = ($recordsPerPage * $page) - $recordsPerPage;
 
// select all data
$query = "SELECT 
            id, firstname, lastname, username 
        FROM 
            users 
        ORDER BY 
            id desc
        LIMIT 
            {$fromRecordNum}, {$recordsPerPage}";
             
            /*
            page and its LIMIT clause looks like:
            1 = 0, 5
            2 = 5,10
            3 = 10,15
            4 = 15, 20
            5 = 20, 25
            */
         
$stmt = $con->prepare( $query );
$stmt->execute();
 
//this is how to get number of rows returned
$num = $stmt->rowCount();
 
//check if more than 0 record found
if($num>0){
 
    //start table
    echo "<table id='tfhover' class='tftable' border='1'>";
     
        //creating our table heading
        echo "<tr>";
            echo "<th>Firstname</th>";
            echo "<th>Lastname</th>";
            echo "<th>Username</th>";
        echo "</tr>";
         
        //retrieve our table contents
        //fetch() is faster than fetchAll()
        //http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
         
            //extract row, this will make $row['firstname'] to just $firstname only
            extract($row);
             
            //creating new table row per record
            echo "<tr>";
                echo "<td>{$firstname}</td>";
                echo "<td>{$lastname}</td>";
                echo "<td>{$username}</td>";
            echo "</tr>";
        }
         
    echo "</table>";//end table
 
    // ***** Paging section will be here ***** 
}

// ***** for 'first' and 'previous' pages
if($page>1){
    // ********** show the first page
    echo "<a href='" . $_SERVER['PHP_SELF'] . "' title='Go to the first page.' class='customBtn'>";
        echo "<span style='margin:0 .5em;'> << </span>";
    echo "</a>";
     
    // ********** show the previous page
    $prev_page = $page - 1;
    echo "<a href='" . $_SERVER['PHP_SELF'] 
            . "?page={$prev_page}' title='Previous page is {$prev_page}.' class='customBtn'>";
        echo "<span style='margin:0 .5em;'> < </span>";
    echo "</a>";
     
}
?>

<?php
// ********** show the number paging
 
// find out total pages
$query = "SELECT COUNT(*) as total_rows FROM users";
$stmt = $con->prepare( $query );
$stmt->execute();
 
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$total_rows = $row['total_rows'];
 
$total_pages = ceil($total_rows / $recordsPerPage);
 
// range of num links to show
$range = 2;
 
// display links to 'range of pages' around 'current page'
$initial_num = $page - $range;
$condition_limit_num = ($page + $range)  + 1;
 
for ($x=$initial_num; $x<$condition_limit_num; $x++) {
     
    // be sure '$x is greater than 0' AND 'less than or equal to the $total_pages'
    if (($x > 0) && ($x <= $total_pages)) {
     
        // current page
        if ($x == $page) {
            echo "<span class='customBtn' style='background:red;'>$x</span>";
        } 
         
        // not current page
        else {
            echo " <a href='{$_SERVER['PHP_SELF']}?page=$x' class='customBtn'>$x</a> ";
        }
    }
}
 
?>

<?php
// ***** for 'next' and 'last' pages
if($page<$total_pages){
    // ********** show the next page
    $next_page = $page + 1;
    echo "<a href='" . $_SERVER['PHP_SELF'] . "?page={$next_page}' title='Next page is {$next_page}.' class='customBtn'>";
        echo "<span style='margin:0 .5em;'> > </span>";
    echo "</a>";
     
    // ********** show the last page
    echo "<a href='" . $_SERVER['PHP_SELF'] . "?page={$total_pages}' title='Last page is {$total_pages}.' class='customBtn'>";
        echo "<span style='margin:0 .5em;'> >> </span>";
    echo "</a>";
}
?>

<?php
// ***** allow user to enter page number
echo "<form action='" . $_SERVER['PHP_SELF'] . "' method='GET'>";
    echo "Go to page: ";
    echo "<input type='text' name='page' size='1' />";
    echo "<input type='submit' value='Go' class='customBtn' />";
echo "</form>"; 
?>