Pagination in PHP with MySQL

Pagination is a commonly used functionality on the data list in almost every web application. Pagination divides the data between pages to load the dynamic data faster. Generally, all the data is listed on the webpage. But when the web application handles huge data and needs to retrieve from the database, it’s not a good idea to load all the records at once. It will take a long time to display all the records on a single page, and sometimes your application may hang while loading the huge amount of data. To overcome this issue, you can divide the records into pages instead of showing all at once. Pagination makes it simple to spread the records across multiple pages and makes it easier to view.

PHP Pagination allows for paging data into pages and access by links. In this tutorial, we will show how to create simple pagination in PHP with MySQL. To make the PHP pagination script reusable, we will create a Pagination library that can be reused in multiple web projects.

In this example we will create a simple web application to display the members list from the database with pagination using PHP and MySQL. Initially, it will show 9 records per page. If there are more than 9 records, it will create pagination links to access the next set of records. The user can navigate to the next or previous page by clicking on the pagination links.

Files Structure

Before getting started, let’s see the files structure of the PHP pagination example script.

pagination_with_php/
├── config.php
├── index.php
├── Pagination.class.php
└── css/
    └── style.css

Pagination Library (Pagination.class.php)

Create a file named Pagination.class.php to define the Pagination class that handles the pagination logic and link generation.

• This pagination library is a PHP class file that contains all the functions to create pagination links. You can include this file in your PHP script and create an object of the Pagination class to use the pagination functionality.

The Pagination class accepts an array of parameters to customize the pagination links. Below are the list of parameters that you can use to customize the pagination links:

• $baseURL – URL of the webpage.
• $totalRows – Total number of records.
• $perPage – Number of records to show per page.
• $numLinks – Number of links to show before and after the current page link.
• $firstLink – First link label.
• $nextLink – Next link label.
• $prevLink – Previous link label.
• $lastLink – Last link label.
• $fullTagOpen – Full open tag.
• $fullTagClose – Full close tag.
• $firstTagOpen – First open tag.
• $firstTagClose – First close tag.
• $lastTagOpen – Last open tag.
• $lastTagClose – Last close tag.
• $curTagOpen – Current open tag.
• $curTagClose – Current close tag.
• $nextTagOpen – Next open tag.
• $nextTagClose – Next close tag.
• $prevTagOpen – Previous open tag.
• $prevTagClose – Previous close tag.
• $numTagOpen – Number open tag.
• $numTagClose – Number close tag.
• $showCount – Show total records count.
• $queryStringSegment – Query string segment to get the current page number.

You can customize the pagination links by changing the default values of the above parameters. Below is the complete code of the Pagination.class.php file.

<?php 
class Pagination{ 
    protected $baseURL        = ''; 
    protected $totalRows      = ''; 
    protected $perPage         = 10; 
    protected $numLinks        =  2; 
    protected $currentPage    =  0; 
    protected $firstLink       = 'First'; 
    protected $nextLink        = 'Next &raquo;'; 
    protected $prevLink        = '&laquo; Prev'; 
    protected $lastLink        = 'Last'; 
    protected $fullTagOpen    = '<nav class="pagination">'; 
    protected $fullTagClose    = '</nav>'; 
    protected $firstTagOpen    = ''; 
    protected $firstTagClose= ''; 
    protected $lastTagOpen    = ''; 
    protected $lastTagClose    = ''; 
    protected $curTagOpen    = '<a class="current">'; 
    protected $curTagClose    = '</a>'; 
    protected $nextTagOpen    = ''; 
    protected $nextTagClose    = ''; 
    protected $prevTagOpen    = ''; 
    protected $prevTagClose    = ''; 
    protected $numTagOpen    = ''; 
    protected $numTagClose    = ''; 
    protected $showCount    = true; 
    protected $currentOffset= 0; 
    protected $queryStringSegment = 'page'; 
     
    function __construct($params = array()){ 
        if (count($params) > 0){ 
            $this->initialize($params);         
        } 
    } 
     
    function initialize($params = array()){ 
        if (count($params) > 0){ 
            foreach ($params as $key => $val){ 
                if (isset($this->$key)){ 
                    $this->$key = $val; 
                } 
            }         
        } 
    } 
     
    /** 
     * Generate the pagination links 
     */     
    function createLinks(){  
        // If total number of rows is zero, do not need to continue 
        if ($this->totalRows == 0 OR $this->perPage == 0){ 
           return ''; 
        } 
        // Calculate the total number of pages 
        $numPages = ceil($this->totalRows / $this->perPage); 
        // Is there only one page? will not need to continue 
        if ($numPages == 1){ 
            if ($this->showCount){ 
                $info = 'Showing : ' . $this->totalRows; 
                return $info; 
            }else{ 
                return ''; 
            } 
        } 
         
        // Determine query string 
        $query_string_sep = (strpos($this->baseURL, '?') === FALSE) ? '?page=' : '&amp;page='; 
        $this->baseURL = $this->baseURL.$query_string_sep; 
         
        // Determine the current page 
        $this->currentPage = isset($_GET[$this->queryStringSegment])?$_GET[$this->queryStringSegment]:0; 
         
        if (!is_numeric($this->currentPage) || $this->currentPage == 0){ 
            $this->currentPage = 1; 
        } 
         
        // Links content string variable 
        $output = ''; 
         
        // Showing links notification 
        if ($this->showCount){ 
           $currentOffset = ($this->currentPage > 1)?($this->currentPage - 1)*$this->perPage:$this->currentPage; 
           $info = 'Showing ' . $currentOffset . ' to ' ; 
         
           if( ($currentOffset + $this->perPage) <= $this->totalRows ) 
              $info .= $this->currentPage * $this->perPage; 
           else 
              $info .= $this->totalRows; 
         
           $info .= ' of ' . $this->totalRows . ' | '; 
         
           $output .= $info; 
        } 
         
        $this->numLinks = (int)$this->numLinks; 
         
        // Is the page number beyond the result range? the last page will show 
        if($this->currentPage > $this->totalRows){ 
            $this->currentPage = $numPages; 
        } 
         
        $uriPageNum = $this->currentPage; 
         
        // Calculate the start and end numbers.  
        $start = (($this->currentPage - $this->numLinks) > 0) ? $this->currentPage - ($this->numLinks - 1) : 1; 
        $end   = (($this->currentPage + $this->numLinks) < $numPages) ? $this->currentPage + $this->numLinks : $numPages; 
         
        // Render the "First" link 
        if($this->currentPage > $this->numLinks){ 
            $firstPageURL = str_replace($query_string_sep,'',$this->baseURL); 
            $output .= $this->firstTagOpen.'<a href="'.$firstPageURL.'">'.$this->firstLink.'</a>'.$this->firstTagClose; 
        } 
        // Render the "previous" link 
        if($this->currentPage != 1){ 
            $i = ($uriPageNum - 1); 
            if($i == 0) $i = ''; 
            $output .= $this->prevTagOpen.'<a href="'.$this->baseURL.$i.'">'.$this->prevLink.'</a>'.$this->prevTagClose; 
        } 
        // Write the digit links 
        for($loop = $start -1; $loop <= $end; $loop++){ 
            $i = $loop; 
            if($i >= 1){ 
                if($this->currentPage == $loop){ 
                    $output .= $this->curTagOpen.$loop.$this->curTagClose; 
                }else{ 
                    $output .= $this->numTagOpen.'<a href="'.$this->baseURL.$i.'">'.$loop.'</a>'.$this->numTagClose; 
                } 
            } 
        } 
        // Render the "next" link 
        if($this->currentPage < $numPages){ 
            $i = ($this->currentPage + 1); 
            $output .= $this->nextTagOpen.'<a href="'.$this->baseURL.$i.'">'.$this->nextLink.'</a>'.$this->nextTagClose; 
        } 
        // Render the "Last" link 
        if(($this->currentPage + $this->numLinks) < $numPages){ 
            $i = $numPages; 
            $output .= $this->lastTagOpen.'<a href="'.$this->baseURL.$i.'">'.$this->lastLink.'</a>'.$this->lastTagClose; 
        } 
        // Remove double slashes 
        $output = preg_replace("#([^:])//+#", "\\1/", $output); 
        // Add the wrapper HTML if exists 
        $output = $this->fullTagOpen.$output.$this->fullTagClose; 
         
        return $output;         
    } 
} 
?>

PHP Pagination Library Usage Example:
Here is a simple example of how to use the PHP Pagination library to paginate a list of records from a database.

<?php 
 
// Include pagination library file 
include_once 'Pagination.class.php'; 
 
// Initialize the pagination class 
$params = array( 
    'baseURL'   => 'https://www.example.com/data-list/', 
    'totalRows' => $row_count, 
    'perPage'   => $limit 
); 
$pagination =  new Pagination($params); 
 
?>

To render the pagination links, use the following code:

<?php echo $pagination->createLinks(); ?>

Create Members List with Pagination

In this example, we will create a simple members list with pagination using PHP and MySQL. The members data will be fetched from the MySQL database and displayed in a tabular format with pagination links using PHP.

Create Database Table:
First, create a database table named members to store the members’ data. You can use the following SQL query to create the table:

CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(25) NOT NULL,
  `last_name` varchar(25) NOT NULL,
  `email` varchar(50) NOT NULL,
  `gender` enum('Male','Female') NOT NULL,
  `country` varchar(20) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Configuration File (config.php):
Create a configuration file named config.php to store the database connection settings and other configuration options.

<?php 
// Base URL of the webpage 
$baseURL = 'https://www.example.com/data-list/'; 
 
// Number of records to show per page 
$perPageLimit = 9; 
 
// Name of the database table from which to fetch records 
$dbTable = 'members'; 
 
// Database credentials 
$dbHost     = "localhost"; 
$dbUsername = "root"; 
$dbPassword = "root"; 
$dbName     = "codexworld_db"; 
 
// Create database connection 
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
 
// Check connection 
if ($db->connect_error) { 
    die("Connection failed: " . $db->connect_error); 
} 
?>

Members List with Pagination (index.php):
Create a file named index.php to display the members list with pagination.

• Include the configuration file and pagination library.
• Fetch the total number of records from the database table.
• Initialize the Pagination class with the required parameters.
• Fetch the records for the current page using SQL LIMIT.
• Display the records in a tabular format.
• Render the pagination links using the createLinks() method.

<?php 
// Include the configuration file 
require_once 'config.php'; 
 
// Include pagination library file  
include_once 'Pagination.class.php'; 
 
// Set the limit of records per page 
$limit  = $perPageLimit; 
 
// Get the current page number from the URL, if not set default to 1  
$page = isset($_GET['page']) ? $_GET['page'] : 1;  
  
// Calculate the offset for the SQL LIMIT clause  
$offset = ($page- 1) * $limit; 
 
// Get the total number of records from the database  
$result = $db->query("SELECT COUNT(*) as numRows FROM {$dbTable}"); 
$row  = $result->fetch_assoc(); 
$row_count = $row['numRows']; 
 
// Fetch the records from the database with offset and limit 
$result = $db->query("SELECT * FROM {$dbTable} ORDER BY id DESC LIMIT $offset,$limit"); 
 
// Initialize the pagination class  
$params = array( 
    'baseURL' => $baseURL, 
    'totalRows' => $row_count, 
    'perPage' => $limit 
); 
$pagination =  new Pagination($params); 
?>

<table>
    <thead>
        <tr>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Email</th>
            <th>Gender</th>
            <th>Country</th>
            <th>Created</th>
        </tr>
    </thead>
    <tbody>
    <?php if($result->num_rows > 0){ ?> 
        <?php while($row = $result->fetch_assoc()){ ?>
            <tr>
                <td><?php echo $row["first_name"]; ?></td>
                <td><?php echo $row["last_name"]; ?></td>
                <td><?php echo $row["email"]; ?></td>
                <td><?php echo $row["gender"]; ?></td>
                <td><?php echo $row["country"]; ?></td>
                <td><?php echo $row["created"]; ?></td>
            </tr>
        <?php } ?> 
    <?php }else{ ?>
        <tr><td colspan="6">No records found...</td></tr>
    <?php } ?>
    </tbody>
</table>

<!-- Display pagination links -->
<?php echo $pagination->createLinks(); ?>

Conclusion

Our PHP Pagination library makes it easier to add pagination feature to the data tables. You can easily implement the pagination functionality in PHP with MySQL. The functionality of the Pagination class can be extended as per your website’s needs. If you want to provide a better user experience, use Ajax Pagination with PHP in your web application.