Skip to content

Responsive Pagination with PHP, MySQL and Bootstrap

Share Button

 

Responsive Pagination with PHP, MySQL and Bootstrap

Most of the websites deals with enormous amount of content that of course somehow must be displayed to the users. If a website would need to fetch from database and display thousands of records(images, video links etc) on one page it would not be easy to digest, especially if there would be thousands of queries at the same time. Besides, no one want to see all to content on one , endless to scroll page.

sharemyweb_php_mysql_pagination

Paging allows to display only the records required / requested by a user. So instead of putting all results on one page, the content spread over several pages. It makes it much easier to read and of course, the content is fetched from database only when user request it by clicking on appropriate page.

Download

Pagination is possible thanks to MySQL ‘LIMIT’ and ‘OFFSET’.

‘LIMIT’ clause, basically specifies the number of records to be returned. So we can for example return only records from 1-26 and no more. Or records from 1-15. Simple as that.

‘OFFSET’ clause on the other hand allows to specify from which reord we would like to start. For example, we could start from record 7 or 9 or any other record we wish.

I came across many pagination classes that sometimes were to long winded and messy.That’s why I’ve decided to write a simple php pagination class that can be understand and implemented by anyone.

Throughout the code I have implemented appropriate explanations so you’ll be able to adjust the code to your needs if needed. Also, I’ve made the pagination demo responsive, so you can test in on desktop, laptops, tables and mobile devices.

sharemyweb_php_mysql_pagination_small_screen

All you need to do is:

– Download .zip file (Download button provided below)
– Create database ‘pagination’ in MySQL
– Import pagination.sql(perhaps via phpMyAdmin)
– Change your Database Credentials in dbConfig.php
– Open pagination demo files in your browser and test it ūüėČ

If you have already downloaded .zip, you’re ready to go.

Create MySQL database with:

CREATE DATABASE IF NOT EXISTS pagination;

When you already have your database in place, download pagination.sql via phpMyAdmin

  1. Click the name of newly crated database – > pagination
  2. Click on import to start the import process
  3. Browse for the pagination.sql and import it….. Done!!!!

sharemyweb_php_mysql_pagination_php_my_admin

 

sharemyweb_php_mysql_pagination_image_table

Change MySQL database credentials in dbConf.php file. You’ll only need to specify your database user and password. Then open the browser and open first(index.php) page.

sharemyweb_php_mysql_pagination_content

Pagination demo consists of following files:

sharemyweb_php_mysql_pagination_files_folders

 

Download

dbConf.php

This file includes all MySQL credentials necessary for database connection like. You should open the file and change DB user and password so the successful connection to MySQL database can be established. It’s purely, up to you, but I prefer to keep database credentials in separate file.

database.php

It’s a simple database class that defined method to establish MySQL connection, and fetch the data.

paination.php

This file defines the pagination class. It basically says how the pagination functionality is set up and how it should work. I’ve applied comments and explanations under each single method, so you won’t have any bigger problem with understanding.

First thing that you’ll notice are declared variables like links(first, next, previous, last), current page and so on. Same regards to the methods: There are methods that defines, previous or next page link, finding records using ‘LIMIT’ & ‘OFFSET’ clauses… simple as that.

Pagination class also consist of constructor that takes 3 arguments: current page, items/images displayed per page, total number of items. Current pages keeps track of current page(via $_GET variable), items per page allow to display only specific number of items per page and total number of items count all items in db.

Pagination class also specifies how many pages are to be displayed before and after current/active page.

<?php

/* 
    Pagination Class 1.1
    Developed by 'Kwacho', 05/2016
    info@sharemyweb.com     
*/
class Pagination extends MySQLDatabase{
 
//############################# 
//###   DECLARE VARS  ######### 
//#############################
    
    // Current page number
    public $current_page;
    
    // items displayed per page
    public $per_page;
    
    //total number of all items
    public $total_count;
    
    //number of links displayed after current page link
    public $links_after;
    
    //number of links displayed before current page link
    public $links_before;
    
    // Number of pages after a $current_page
    public $min_pages;
    
    // Number of pages before a $current_page
    public $max_pages;
    
    // First page link name
    public $first_link;
    
    // Last page link name
    public $last_link;
    
    // Next page link name
    public $next_link;
    
    // Previous page link name
    public $previous_link;
    
    
//########################################## 
//###   CONSTRUCTOR WITH DEFAULT VALUES  ###  
//##########################################
    
    public function __construct($page=1, $per_page=1, $total_count=0){
        $this->open_connection();
        
        $this->current_page = (int)$page; // current _page
        $this->per_page = (int)$per_page; // 
        $this->total_count = (int)$total_count;
        $this->links_after = 3;
        $this->links_before = 3;
        
        // Button names and custom design
        $this->first_link = '>&laquo&laquo; First';     // <<<< First
        $this->previous_link = '">&laquo; Previous';    // << Previous
        $this->next_link = '">Next &raquo;';            // Next >>
        $this->last_link = '"\">Last &raquo&raquo;';        // Last >>>>
        $this->max_pages=''; 
        $this->min_pages=''; 
    }
    
    // Return current page
    public function getPage($page){
        $this->page = $page;
        return $this->page;
    }
    
    // Return items displayed per page
    public function getPerPage(){
        $this->per_page = 4;
        return $this->per_page;
    }
    
    public function offset(){
        // Assuming 10 items per page:
        // page 1 has an offset of 0    (1-1)*10
        // page 2 has an offset of 20   (2-1)*10
        // in other words, page 2 starts with item 11        
        return ($this->current_page - 1) * $this->per_page;
    }
    
    // Check total pages of items. Total number of items divided 
    // by number of items per page
    public function total_pages(){
        return ceil($this->total_count / $this->per_page);
    }
    
    // Counts all item in databse.
    public function last_item_number(){
        return ceil($this->total_count);
    }
    
    // Previous page = $current_page - 1;
    public function previous_page(){
        return $this->current_page - 1;
    }
    
    // Next page = $current_page + 1;
    public function next_page(){
        return $this->current_page + 1;
    }

    // if previous page is > 1(not = 0) than there is a previous page
    public function has_previous_page(){
        return $this->previous_page() >= 1 ? true : false;
    }
    
    // if next page is not smaller than a total number of pages than there is a next page 
    public function has_next_page(){
        return $this->next_page() <= $this->total_pages() ? true : false;
    }
    
    // return first page link link
    public function getFirstLink(){
        return $this->first_link;
    }
    
    // return previous page link link
    public function getPreviousLink(){
        return $this->previous_link;
    }
    
    // return next page link link
    public function getNextLink(){
        return $this->next_link;
    }
    
    // return last page link link
    public function getLastLink(){
        return $this->last_link;
    }
    
    // Finds requested records of images : DESCENDING order, so the last image added will be displayed as first one
    public function findRecords($per_page, $offset){

        global $database;        
        // Finds records for the page only.
        $sql = "SELECT * FROM images ";
        $sql .= "ORDER BY id DESC ";
        $sql .= "LIMIT {$per_page} ";
        $sql .= "OFFSET {$offset}";
        return ($database->find_by_query($sql)) ? $sql : false;
    }
}

// Instantiate class
$pagination = new Pagination();

?>

painationView.php

Display the pagination links utilizing of already created methods in pagination class(pagination.php). I have applied plenty of comments in this file so you’ll have a good idea of what’s going on and how to use it. Some people put all the paging links into one function and display it, but I preffer to give each link an independent freedom so they can be called separately if needed.

<?php 

/*
    PAGINATION LINKS / BUTTONS DISPLAY
*/

/** Defines number of pages before & after current page /active 
    For example if $pagination->links_after is set to 3, only 3 pages after current page will be available....
    You can change it if Pagination class if you wish
*/
$min = max($page - $pagination->links_before, 1); // there are no pages < 1
$max = min($page + $pagination->links_after, $pagination->total_pages());

echo "<div class='pagination' id='paginationHolder'>
        <div class='bs-example' id='paginationNav'>
            <ul class='pagination paginationList'>";

    if($pagination->total_pages() > 1) {
        
        /*##############################################
        ###          FIRST PAGE LINK / BUTTON        ###
        ##############################################*/
        if($pagination->has_previous_page()){
            echo "<li><a class='pagination_links controLinks' id='first_link' href=index.php?page=1";
        } else {
            echo "<li><a class='pagination_links controLinks disabledLink' id='first_link' disabled ";
        }
            echo $pagination->getFirstLink() ."</a></li> ";
        
        
        /*##############################################
        ###       PREVIOUS PAGE LINK / BUTTON        ###
        ##############################################*/  
        
       if($pagination->has_previous_page()){
        echo "<li><a class='pagination_links controLinks' id='previous_link' href=\"index.php?page=";
        } else {
           echo "<li><a class='pagination_links controLinks disabledLink' id='previous_link' disabled";
        }
        echo $pagination->previous_page();
        echo $pagination->getPreviousLink() ."</a></li> ";
        
        
        
        /*##############################################
        ###            ALL PAGES LINKS               ###
        ##############################################*/
        for($i = $min; $i <= $max; ++$i) {

                echo "<li><a";
if( $i==$page ? $idStyleName='selected' : $idStyleName='regularLink');
                    echo " class='pagination_links' id='{$idStyleName}' href=\"index.php?page={$i}\">{$i}</a></li> ";
        }
        
        /*##############################################
        ###          NEXT PAGE LINK / BUTTON         ###
        ##############################################*/
        if($pagination->has_next_page()){
            
            echo "<li><a class='pagination_links controLinks' id='next_link' href=\"index.php?page="; 
            
        } else {  
            echo "<li><a class='pagination_links controLinks disabledLink' id='next_link' disabled "; 
        }   
            echo $pagination->next_page();
            echo $pagination->getNextLink() ."</a></li> ";
        
        
        /*##############################################
        ###          LAST PAGE LINK / BUTTON         ###
        ##############################################*/
        if($pagination->has_next_page()){
            echo "<li><a class='pagination_links controLinks' id='last_link' href=\"index.php?page=";
        }else{
            echo "<li><a class='pagination_links controLinks disabledLink' id='last_link' disabled";
            }
            echo $pagination->total_pages();
            echo $pagination->getLastLink() ."</a></li> ";      
    }

echo "
        </ul>
    </div>
</div>";

?>

sharemyweb_php_mysql_pagination_content_2

index.php

File that is being read once you access pagination demo files and folders. This files request all other files and is the only file that a user should interact with. index.php. Requests pagination class, sets current page with $_GET[], create an instance of pagination class and display content(images)

<?php
    require_once("header.php");
?>
<body>
<?php

    // Require database
    require_once("database.php");
    // Require pagination class
    require_once("pagination.php");
    
    // Sets the current page for Pagination - Get hte page from $_GET global variable
    $page = !empty($_GET['page']) ? (int)$_GET['page'] : 1;

    // Create new instance of Pagination class with arguments: current page, items per pages and count all items
    $pagination = new Pagination($pagination->getPage($page), $pagination->getPerPage(), $database->num_rows());

    // Find all images according to pagination criterias- items displayed per page and offset
    $images = $database->find_by_query($pagination->findRecords($pagination->getPerPage($page), $pagination->offset()));
 ?>
    
    <div class='row' style='margin: auto; text-align:center;'>
        <div class="span9">
             <a href='http://www.sharemyweb.com' target="_blank"><img class="img-responsive" style='width:400px; margin:0 auto;' alt="ShareMyweb_Logo" src="http://sharemyweb.com/wp-content/uploads/2016/01/Demo_site_logo.png"></a>
        </div>
        <h2 style='text-align:center;'>PHP, MySQL Responsive Pagination</h2>
    <hr>
    </div>
    
    <!-- Display images-->
    <div class='container' style='width:auto; text-align:center;'>
    <?php 
        // foreach image.... display image
        foreach($images as $image){
            echo "<img clas='img display_images' src='images/{$image['image_name']}' alt='{$image['image_desc']}'><br />";          
        }
    ?>
    </div>
    
<?php 
    //Display Pagination
    require_once("paginationView.php");
    // Display footer
    require_once("footer.php");
?>

 

Download

header and footer

I believe that these two sections don’tt need a bigger explanation so I’ll just take few seconds to describe header briefly. This file defines bootstrap links and scripts, website title, css file and small browser icon that appears on your browser tab(it just looks nice).

Images folder

I have included few custom images so you see how the pagination works in the demo. You can change images as you wish, same as you can use this demo as you like.

That’s all. Now, download the pagination demo, play with it and change it as much as you wish. If you have any questions, ideas for improvements, don’t hesitate let me know.

Share Button