Pagination in php and MySQL tutorial

November 6, 2021 MySQL, MySQL, PHP 1 Comment

Spread the love

Last updated:29th January, 2022

Pagination in php and MySQL

Pagination is a valuable skill for web developers to learn. It is a good UI technique to present long list of records into separate pages. Pagination also decreases the load from server and database so the results are loaded faster. In this tutorial, you will learn pagination in php and MySQL.

Following steps are performed in this tutorial:

Setup the application
Create application folder
Install composer
Create project folder structure
Create a MySQL database
Insert sample data
Create Database and config class
Create Pagination class
Create Functions class
Create Index page to display paginated records
Run the application

What is Pagination?

Using pagination, long list of results are shown on multiple pages. The subset of results are shown in a single page with links to next and previous results.

If you perform a search on any modern search engine, you can see thousands of result pages, instead of displaying all pages, only a subset of records are displayed with next, previous and numbered result pages.

In order to work with pagination, we need to know 3 values

Current Page
Number of records per page
Total number of results

The above three values can be used to calculate all other variables used in pagination. The Current page is always part of Page URL. It is sent to server with the Request. You can get the page variable using the code below

$current_page = (int) $_GET['page'] ?? 1;

Using $current-page variable we can get Next and Previous pages.

$next = $current_page + 1;

$previous = $current_page -1;

The URL remains same for current, next and previous pages but the current, next and previous page values change. If current page is 2 then next would be 3 and previous would be 1. $per_page variable is used inside the code to display number of records per page.

$per_page = 10;

The above variable will display 10 records.

Offset Variable

If we know the current page number and per page records, offset can be calculated for the current page. The offset is the number of records that needs to be skipped over to get to the first record for the current page from database. Using this value, we find start record of the current page.

Current Page	Offset	Returned Records
1	0	1-10
2	10	11-20
3	20	21-30
4	30	31-40
5	40	41-50

Setup the application

You should have PHP, MySQL and phpMyAdmin installed on your system. Install XAMPP or WAMP. After installation, start Apache, MySQL services.

Create application folder

Create a folder pagination-in-php-mysql inside the www or htdocs folder in WAMP or XAMPP. Open command line. Go to the application folder, write the command:

cd pagination-in-php-mysql

Setup using composer

Download and install composer with default wizard instructions.

Type the command.

composer init

Composer.json file is generated. Replace the code in the composer.json file.

{
   "autoload": {
      "psr-4": {
         "App\\": "src/"
      }
   }
}

Note the “App\\”: “src/” line in composer.json. The src directory contains the code files.

Installing required dependencies.

On command line, type command below.

composer install

PSR-4 autoload package is installed.

“Namespace” is App and src is the folder, where database, functions, config and pagination classes are saved. Vendor folder and composer.lock file is also generated.

Creating project directory structure

Open the project folder and create these directories.

Create src folder

Create css folder

2. Create a MySQL database

Open PhpMyAdmin and add a database named php_pagination.

http://localhost/phpmyadmin

Open SQL Tab, and run the command.

CREATE DATABASE php_pagination;

Users table will be created with following fields:

first_name,

last_name,

date_added

Create a table Users, running the CREATE TABLE command in SQL tab of phpMyAdmin.

CREATE TABLE users (
  id int(11) NOT NULL auto_increment,
  first_name varchar(255),
  last_name varchar(255),
  date_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Insert sample data into the database

To use pagination we will insert around 2000 records. so copy SQL queries and run in SQL table of PhpMyAdmin.

2. Create Class files

Let’s create config.php, database.php, functions.php and pagination.php files

Create config.php

Inside src folder, create a file config.php. Add code below.

<?php
  define("DB_SERVER", "127.0.0.1");
  define("DB_NAME", "php_pagination");
  define("DB_USER", "root");
  define("DB_PASS", "");
?>

Create Database.php file

Inside src folder, create a file Pagination.php and add code below into it.

<?php

  namespace App;

  require_once('config.php');

  class Database {

    private $host;
    private $user;
    private $password;
    private $database;

    public function __construct()
    {
      $this->host = DB_SERVER;
      $this->user = DB_USER;
      $this->password = DB_PASS;
      $this->database = DB_NAME;

    }
  
    public function db_connect() {
      $connection = mysqli_connect($this->host, $this->user, $this->password, $this->database);

      if(mysqli_connect_errno()) {
        $msg = "Database connection failed: ";
        $msg .= mysqli_connect_error();
        $msg .= " (" . mysqli_connect_errno() . ")";
        exit($msg);
      }

      return $connection;
    }

    public function db_query($connection, $sql) {
      $result_set = mysqli_query($connection, $sql);
      
      if(substr($sql, 0, 7) == 'SELECT ') {
        $this->confirm_query($result_set);
      }
      return $result_set;
    }

    public function confirm_query($result_set) {
      if(!$result_set) {
        exit("Database query failed.");
      }
    }

    public function db_fetch_assoc($result_set) {
      return mysqli_fetch_assoc($result_set);
    }

    public function db_free_result($result_set) {
      return mysqli_free_result($result_set);
    }

    public function db_num_rows($result_set) {
      return mysqli_num_rows($result_set);
    }

    public function db_insert_id($connection) {
      return mysqli_insert_id($connection);
    }

    public function db_error($connection) {
      return mysqli_error($connection);
    }

    public function db_close($connection) {
      return mysqli_close($connection);
    }

    public function db_escape($connection, $string) {
      return mysqli_real_escape_string($connection, $string);
    }
  }
?>

In this file, Database class is added. This class contains database connect, query and many other useful methods.

Create Functions.php class

Inside src folder, create Functions.php file and add code below into it.

<?php
  namespace App;

  class Functions {
    private $dbObj;
    private $db;

    public function __construct($db)
    {
      $this->dbObj = $db;
      $this->db = $this->dbObj->db_connect();
    }

    public function find_users($limit=0, $offset=0) {
      
      $sql = "SELECT * FROM users ";
      $sql .= "ORDER BY last_name ASC, first_name ASC";

      if($limit > 0) {
        $sql .= " LIMIT " . $this->dbObj->db_escape($this->db, $limit);
      }

      if($offset > 0) {
        $sql .= " OFFSET " . $this->dbObj->db_escape($this->db, $offset);
      }
      $result = $this->dbObj->db_query($this->db, $sql);

      return $result;
    }

    public function count_users() {
      $sql = "SELECT COUNT(*) FROM users ";
      $result = $this->dbObj->db_query($this->db, $sql);
      $array =  $this->dbObj->db_fetch_assoc($result);

      return $array['COUNT(*)'];
    }

    public  function escape($string="") {
      return htmlspecialchars($string);
    }  
  }
?>

Functions class contains 2 important methods required for Pagination in php, that are find_users and count_users.

Count_users method

Count_users method returns count of all the records from users table. This count will be used in pagination.

find_users method

Find_users is an important method for pagination in php and MySQL. This method selects records based on two conditions

1. Limit the records based on per page variable.

2. Offset variable to start the records for the page.

Create Pagination class

Inside src folder, create Pagination.php file. Add code below into it.

<?php
namespace App;

class Pagination {

  public $current_page;
  public $per_page;
  public $total_count;

  public function __construct($total_count=0, $page=1, $per_page=20) {
    $this->per_page = (int) $per_page;
    $this->total_count = (int) $total_count;
    $this->current_page = (int) $page;
    if($this->current_page < 1 || $this->current_page > $this->total_pages()) {
      $this->current_page = 1;
    }
  }

  public function offset() {
    return $this->per_page * ($this->current_page - 1);
  }

  public function total_pages() {
    return ceil($this->total_count / $this->per_page);
  }

  public function previous_page() {
    $prev = $this->current_page - 1;
    return ($prev > 0) ? $prev : false;
  }

  public function previous_link($url='') {
    $link = '';
    if($this->previous_page() != false) {
      $link .= "<a href=\"{$url}?page={$this->previous_page()}\">";
      $link .= "&larr; Previous</a> ";
    }
    return $link;
  }

  public function next_page() {
    $next = $this->current_page + 1;
    return ($next <= $this->total_pages()) ? $next : false;
  }

  public function next_link($url='') {
    $link = '';
    if($this->next_page() != false) {
      $link .= "<a href=\"{$url}?page={$this->next_page()}\">";
      $link .= "Next &rarr;</a> ";
    }
    return $link;
  }

  public function number_links($url='', $window=2) {
    $output = '';
    $win = (int) $window;
    $gap = false;
    for($i=1; $i <= $this->total_pages(); $i++) {
      if($win > 0 && $i > 1 + $win && $i < $this->total_pages() - $win && abs($i - $this->current_page) > $win) {
        if(!$gap) {
          $output .= "... ";
          $gap = true;
        }
        continue;
      }
      $gap = false;
      if($this->current_page == $i) {
        $output .= "<strong>{$i}</strong> ";
      } else {
        $output .= "<a href=\"{$url}?page={$i}\">{$i}</a> ";
      }
    }
    return $output;
  }

  public function page_links($url="") {
    $output = '';
    if($this->total_pages() > 1) {
      $output .= "<p class=\"pagination\">";
      $output .= $this->previous_link('index.php');
      $output .= $this->number_links('index.php');
      $output .= $this->next_link('index.php');
      $output .= "</p>";
    }
    return $output;
  }

}

?>

In this file, Pagination class is created. This class contains methods for pagination in php like offset, next_link, previous_link, number_links and page-links methods.

Create index.php file

Open the root folder and add index.php file and add code below into it.

<?php
    use \App\
    { 
        Database,
        Functions,
        Pagination 
    };

    include_once('vendor/autoload.php');

    $dbObj = new Database();
    $db = $dbObj->db_connect();
    $utility = new Functions($dbObj);
    $total_count = $utility->count_users();
    $page = $_GET['page'] ?? 1;
    $pagination = new Pagination($total_count, $page, 20);
    $users = $utility->find_users($pagination->per_page, $pagination->offset());

?>

<!doctype html>
        
<html lang="en">
  <head>
    <title>User Listing</title>
    <link rel="stylesheet" href="css/style.css">
  </head>
  <body>

    <h1>Users Listing - Pagination in PHP and MySQL </h1>

    <p class="page-status">
      Page <?php echo $pagination->current_page; ?> of <?php echo $pagination->total_pages(); ?>
    </p>

    <table id="customer-list">
      <tr>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Date Added</th>
      </tr>

      <?php
        while($user = $dbObj->db_fetch_assoc($users)) {
          echo "<tr>";
          echo "<td>" . $utility->escape($user['first_name']) . "</td>";
          echo "<td>" . $utility->escape($user['last_name']) . "</td>";
          echo "<td>" . $utility->escape(Date('Y-m-d', strtotime($user['date_added']))) . "</td>";
          echo "</tr>";
        } // end while

        $dbObj->db_free_result($users);
      ?>
    </table>

    <?php echo $pagination->page_links('index.php'); ?>

  </body>
</html>

<?php if(isset($dbObj)) { $dbObj->db_close($db); } ?>

On the top, Database, Functions, Pagination class are included from App namespace.

Next, after connecting to the database, total_count of user is selected. The total count, current page number and per page records are passed to Pagination class. Find_users method is called to get pagination records from database.

After adding basic HTML template, a table is added and selected user records are displayed. to user.

Add CSS styles

Inside CSS folder, create a style.css file and add styles below.

html, body {
  margin: 0; padding: 0;
  height: 100%; width: 100%;
}
h1 {
  width: 100%;
  text-align: center;
  font-family: sans-serif;
  color: #1B7ABC;
}
table#customer-list {
  width: 50%;
  border-collapse: collapse;
  margin: 0 auto;
}
table tr th {
  color: #fff;
  background: #1B7ABC;
}
table tr td, table tr th {
  border: 1px solid #333;
  padding: 5px 10px;
}

p.page-status, p.pagination {
  width: 100%;
  text-align: center;
}
p.pagination a, p.pagination a:visited {
  text-decoration: none;
  color: #333;
}
p.pagination strong {
  color: #1B7ABC;
}

Running the application

In order to run the pagination in php application, make sure Apache and MySQL services are running. Open the browser, type the URL in address bar.

http://localhost:8080/index.php

You can see the records displayed on page with pagination links. Clicking on next, previous or page number links will load the records accordingly.

Source code of tutorial:

You can find the source code of the tutorial on our GitHub Repository. Clone or download the project in htdocs or www root folder. Run the SQL queries. The sample SQL of the page is also present in the code files.

If you like this tutorial, subscribe to our newsletter and follow us on Twitter, or like our Facebook page to stay updated about the next tutorials.

Summary:

In this tutorial, you have learned to pagination in php and MySQL. A database with sample data is created and then a Database and pagination classes were added to paginate the results form database.