Create Simple Pagination Using PHP and MySQL

Pagination is one of the most frequently used features for web applications. Pagination is the process of dividing a document into discrete pages. Paging allows you to display all of your retrieved results on multiple pages rather than all of them on a single page. So We have shared in this tutorial, we will learn how to Build a pagination PHP & MySQL pagination and enhance it further as per our needs.

Also Read: Simple Pagination Using CodeIgniter and MySQL

Before starting to implement the Simple Pagination Using PHP and MySQL, look files structure:

pagination-php-mysql
- class
  - DBConnection.php
  - Employee.php
- templates
  - header.php
  - footer.php
- index.php

Step 1: Create the database and Table

For this tutorial, you need a MySQL database with the following table:

   //Table structure for table `employee`
   CREATE TABLE `employee` (
    `id` int(11) NOT NULL,
    `name` varchar(255) NOT NULL,
    `email` varchar(255) DEFAULT NULL,
    `gender` varchar(11) NOT NULL,
    `address` varchar(255) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  ALTER TABLE `employee`
    ADD PRIMARY KEY (`id`);

  ALTER TABLE `employee`
      MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

//Dumping data for table `employee`

INSERT INTO `employee` (`id`, `name`, `email`, `gender`, `address`) VALUES
(1, 'Adam', 'adam@techarisel.com', 'Male', 'New York'),
(2, 'Admin', 'admin@techarisel.com', 'Male', 'Boston'),
(3, 'User', 'user@techarisel.com', 'Male', 'California'),
(4, 'Author', 'author@techarisel.com', 'Male', 'Washington '),
(5, 'Sam', 'sam@techarisel.com', 'Male', 'Delhi'),
(6, 'James', 'james@techarisel.com', 'Male', 'Chennai'),
(7, 'Jakey', 'jakey@techarisel.com', 'Male', 'Mumbai'),
(8, 'Harry', 'harry@techarisel.com', 'Male', 'Kolkata'),
(9, 'John', 'john@techarisel.com', 'Male', 'Lucknow'),
(10, 'Lily', 'lily@techarisel.com', 'Female', 'Landon'),
(11, 'Smith', 'smith@techarisel.com', 'Male', 'New York'),
(12, 'Jacab', 'jacab@techarisel.com', 'Male', 'California'),
(13, 'Joseph', 'joseph@techarisel.com', 'Male', 'Washington '),
(14, 'Jolly', 'jolly@techarisel.com', 'Male', 'Delhi');

//Table structure for table `employee`

CREATE TABLE `employee` (

`id` int(11) NOT NULL,

`name` varchar(255) NOT NULL,

`email` varchar(255) DEFAULT NULL,

`gender` varchar(11) NOT NULL,

`address` varchar(255) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `employee`

ADD PRIMARY KEY (`id`);

ALTER TABLE `employee`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

//Dumping data for table `employee`

INSERT INTO `employee` (`id`, `name`, `email`, `gender`, `address`) VALUES

(1, 'Adam', 'adam@techarisel.com', 'Male', 'New York'),

(2, 'Admin', 'admin@techarisel.com', 'Male', 'Boston'),

(3, 'User', 'user@techarisel.com', 'Male', 'California'),

(4, 'Author', 'author@techarisel.com', 'Male', 'Washington '),

(5, 'Sam', 'sam@techarisel.com', 'Male', 'Delhi'),

(6, 'James', 'james@techarisel.com', 'Male', 'Chennai'),

(7, 'Jakey', 'jakey@techarisel.com', 'Male', 'Mumbai'),

(8, 'Harry', 'harry@techarisel.com', 'Male', 'Kolkata'),

(9, 'John', 'john@techarisel.com', 'Male', 'Lucknow'),

(10, 'Lily', 'lily@techarisel.com', 'Female', 'Landon'),

(11, 'Smith', 'smith@techarisel.com', 'Male', 'New York'),

(12, 'Jacab', 'jacab@techarisel.com', 'Male', 'California'),

(13, 'Joseph', 'joseph@techarisel.com', 'Male', 'Washington '),

(14, 'Jolly', 'jolly@techarisel.com', 'Male', 'Delhi');

Step 2: Database Connection/Config class

Create a file named DBConnection.php inside “class/” folder.

<?php
/**
 * @package DBConnection
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 *   
 */

// Database Connection
class DBConnection {
    private $_dbHostname = "localhost";
    private $_dbName = "techarise_DB";
    private $_dbUsername = "root";
    private $_dbPassword = "";
    private $_con;

    public function __construct() {
      try {
          $this->_con = new PDO("mysql:host=$this->_dbHostname;dbname=$this->_dbName", $this->_dbUsername, $this->_dbPassword);    
          $this->_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      } catch(PDOException $e) {
      echo "Connection failed: " . $e->getMessage();
    }

    }
    // return Connection
    public function returnConnection() {
        return $this->_con;
    }
}

?>

<?php

/**

* @package DBConnection

* @author TechArise Team

* @email info@techarise.com

// Database Connection

class DBConnection {

private $_dbHostname = "localhost";

private $_dbName = "techarise_DB";

private $_dbUsername = "root";

private $_dbPassword = "";

private $_con;

public function __construct() {

try {

$this->_con = new PDO("mysql:host=$this->_dbHostname;dbname=$this->_dbName", $this->_dbUsername, $this->_dbPassword);

$this->_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch(PDOException $e) {

echo "Connection failed: " . $e->getMessage();

}

// return Connection

public function returnConnection() {

return $this->_con;

}

Step 3: Create a class file

Create a class file named Employee.php inside “class/” folder.

The class handles the Employee process.
__construct() – Loads the required class.
getAllEmployee() – list all recoreds from database
countEmployee() – count emp records from database

<?php
/**
 * @package Employee class
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 *   
 */

// include connection class
include("DBConnection.php");
// Employee
class Employee 
{
    protected $db;
    private $_page;
    private $_rowPerPage;
    private $_start;

    public function setPage($page) {
        $this->_page = $page;
    }

    public function setRowPerPage($rowPerPage) {
        $this->_rowPerPage = $rowPerPage;
    }
    public function setStart($start) {
        $this->_start = $start;
    }
    public function setStatus($status) {
        $this->_status = $status;
    }
    
    // __construct
    public function __construct() {
        $this->db = new DBConnection();
        $this->db = $this->db->returnConnection();
    }

    // getAll Employee
    public function getAllEmployee() {
      try {
        $sql = "SELECT * FROM employee ";
            $limit=" limit " . $this->_start . "," . $this->_rowPerPage;
            $query = $sql.$limit;
        $stmt = $this->db->prepare($query);
        
        $stmt->execute();
        $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            return $result;
    } catch (Exception $err) {
        die("Oh noes! There's an error in the query! " . $err);
    }
    }
    // count Employee
    public function countEmployee() {
      try {
        $sql = "SELECT * FROM employee";
        $stmt = $this->db->prepare($sql);
        $stmt->execute();
        $count = $stmt->rowCount();
            return $count;
    } catch (Exception $err) {
        die("Oh noes! There's an error in the query! " . $err);
    }
    }

}

?>

<?php

/**

* @package Employee class

* @author TechArise Team

* @email info@techarise.com

// include connection class

include("DBConnection.php");

// Employee

class Employee

{

protected $db;

private $_page;

private $_rowPerPage;

private $_start;

public function setPage($page) {

$this->_page = $page;

}

public function setRowPerPage($rowPerPage) {

$this->_rowPerPage = $rowPerPage;

}

public function setStart($start) {

$this->_start = $start;

}

public function setStatus($status) {

$this->_status = $status;

}

// __construct

public function __construct() {

$this->db = new DBConnection();

$this->db = $this->db->returnConnection();

}

// getAll Employee

public function getAllEmployee() {

try {

$sql = "SELECT * FROM employee ";

$limit=" limit " . $this->_start . "," . $this->_rowPerPage;

$query = $sql.$limit;

$stmt = $this->db->prepare($query);

$stmt->execute();

$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);

return $result;

} catch (Exception $err) {

die("Oh noes! There's an error in the query! " . $err);

}

// count Employee

public function countEmployee() {

try {

$sql = "SELECT * FROM employee";

$stmt = $this->db->prepare($sql);

$stmt->execute();

$count = $stmt->rowCount();

return $count;

} catch (Exception $err) {

die("Oh noes! There's an error in the query! " . $err);

}

Step 4: Create HTML file named index.php

<?php
include_once 'class/Employee.php';
// Number of entries to show in a page.
define("ROW_PER_PAGE", 5);
$page = 1;
$start = 0;
// Look for a GET variable page if not found default is 1.
if (!empty($_GET["page"])) {
  $page = $_GET["page"];
  $start = ($page - 1) * ROW_PER_PAGE;
}
$emp = new Employee();
$emp->setRowPerPage(ROW_PER_PAGE);
$emp->setStart($start);
$emp->setPage($page);
$empInfo = $emp->getAllEmployee();
$rowCount = $emp->countEmployee();
include('templates/header.php');
$per_page_html = '';
?>
<section class="showcase">
  <div class="container">
    <div class="pb-2 mt-4 mb-2 border-bottom">
      <h2 class="text-center">Simple Pagination Using PHP and MySQL</h2>
    </div>
    <div class="page-content page-container" id="page-content">
      <div class="padding">
        <div class="row container d-flex justify-content-center">
          <div class="col-lg-12">
            <div class="card px-3">
              <div class="card-body">
                <div class="list-wrapper">
                  <table class="table table-striped">
                    <thead>
                      <tr>
                        <th style="width: 20%;">Name</th>
                        <th style="width: 30%;">Email</th>
                        <th style="width: 20%;">Gender</th>
                        <th style="width: 30%;">Address</th>
                      </tr>
                    </thead>
                    <tbody>
                      <?php
                      // Showing the Records
                      foreach ($empInfo as $key => $element) { ?>
                        <tr>
                          <td><?php echo $element['name']; ?></td>
                          <td><?php echo $element['email']; ?></td>
                          <td><?php echo $element['gender']; ?></td>
                          <td><?php echo $element['address']; ?></td>
                        </tr>
                      <?php } ?>
                    </tbody>
                  </table>
                  <?php
                  //Showing the Page Navigation
                  if (!empty($rowCount)) {
                    $per_page_html .= '<ul class="pagination float-right">';
                    $page_count = ceil($rowCount / ROW_PER_PAGE);
                    if ($page_count > 1) {
                      for ($i = 1; $i <= $page_count; $i++) {
                        if ($i == $page) {
                          $per_page_html .= '<li class="page-item active"><a class="page-link" href="?page=' . $i . '">' . $i . '</a></li>';
                        } else {
                          $per_page_html .= '<li class="page-item"><a class="page-link" href="?page=' . $i . '">' . $i . '</a></li>';
                        }
                      }
                    }
                    $per_page_html .= "</ul>";
                  }
                  echo $per_page_html;
                  ?>

                </div>
              </div>
            </div>
          </div>
        </div>
      </div>
    </div>
  </div>
</section>
<?php include('templates/footer.php'); ?>
?>

<?php

include_once 'class/Employee.php';

// Number of entries to show in a page.

define("ROW_PER_PAGE", 5);

$page = 1;

$start = 0;

// Look for a GET variable page if not found default is 1.

if (!empty($_GET["page"])) {

$page = $_GET["page"];

$start = ($page - 1) * ROW_PER_PAGE;

}

$emp = new Employee();

$emp->setRowPerPage(ROW_PER_PAGE);

$emp->setStart($start);

$emp->setPage($page);

$empInfo = $emp->getAllEmployee();

$rowCount = $emp->countEmployee();

include('templates/header.php');

$per_page_html = '';

<h2 class="text-center">Simple Pagination Using PHP and MySQL</h2>

</div>

<thead>

<tr>

<th style="width: 30%;">Email</th>

<th style="width: 20%;">Gender</th>

<th style="width: 30%;">Address</th>

</tr>

</thead>

<tbody>

<?php

// Showing the Records

foreach ($empInfo as $key => $element) { ?>

<tr>

</tr>

<?php } ?>

</tbody>

</table>

<?php

//Showing the Page Navigation

if (!empty($rowCount)) {

$per_page_html .= '<ul class="pagination float-right">';

$page_count = ceil($rowCount / ROW_PER_PAGE);

if ($page_count > 1) {

for ($i = 1; $i <= $page_count; $i++) {

if ($i == $page) {

$per_page_html .= '<li class="page-item active"><a class="page-link" href="?page=' . $i . '">' . $i . '</a></li>';

} else {

$per_page_html .= '<li class="page-item"><a class="page-link" href="?page=' . $i . '">' . $i . '</a></li>';

}

$per_page_html .= "</ul>";

}

echo $per_page_html;

</div>

</section>

<?php include('templates/footer.php'); ?>

Step 5:Create a view(header)

Create a view file named header.php inside “templates” folder.

This view contains the header section of the webpage. The Bootstrap library is used to provide a better UI, so, include it in the header section.

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="">
  <meta name="author" content="">

  <title>Simple Pagination Using PHP and MySQL | Tech Arise</title>
  <!-- Bootstrap core CSS -->
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/css/bootstrap.min.css" />
  <!-- Custom fonts for this template -->
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.7.2/css/all.min.css" />
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/simple-line-icons/2.4.1/css/simple-line-icons.css" />
  <link href="https://fonts.googleapis.com/css?family=Lato:300,400,700,300italic,400italic,700italic" rel="stylesheet" type="text/css">
  <!-- Custom styles for this template -->
  <link href="css/style.css" rel="stylesheet">
</head>

<body>
  <!-- Navigation -->
  <nav class="navbar navbar-expand-lg navbar-dark bg-dark static-top header-bg-dark" style="background: #FFFFFF;">
    <div class="container">
      <a class="navbar-brand font-weight-bold" href="https://techarise.com">
        <h1>Tech Arise</h1>
      </a>
      <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarResponsive" aria-controls="navbarResponsive" aria-expanded="false" aria-label="Toggle navigation">
        <span class="navbar-toggler-icon"></span>
      </button>
      <div class="collapse navbar-collapse" id="navbarResponsive">
        <ul class="navbar-nav ml-auto">
          <li class="nav-item active">
            <a class="nav-link" href="https://techarise.com">Home
              <span class="sr-only">(current)</span>
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="https://techarise.com/php-free-script-demos/">Live Demo</a>
          </li>
        </ul>
      </div>
    </div>
  </nav>

<!DOCTYPE html>

<head>

<title>Simple Pagination Using PHP and MySQL | Tech Arise</title>

</head>

<body>

<h1>Tech Arise</h1>

</a>

</button>

<a class="nav-link" href="https://techarise.com">Home

<span class="sr-only">(current)</span>

</a>

</li>

</li>

</ul>

</div>

</nav>

Step 6: Create a view(footer)

Create a view file named footer.php inside “templates” folder.

This view contains the footer section of the webpage.

<!-- Footer -->
<footer class="footer bg-light footer-bg-dark">
  <div class="container">
    <div class="row">
      <div class="col-lg-6 h-100 text-center text-lg-left my-auto">
        <ul class="list-inline mb-2">
          <li class="list-inline-item">
            <a href="#">About</a>
          </li>
          <li class="list-inline-item">&sdot;</li>
          <li class="list-inline-item">
            <a href="#">Contact</a>
          </li>
          <li class="list-inline-item">&sdot;</li>
          <li class="list-inline-item">
            <a href="#">Terms of Use</a>
          </li>
          <li class="list-inline-item">&sdot;</li>
          <li class="list-inline-item">
            <a href="#">Privacy Policy</a>
          </li>
        </ul>
        <p class="text-muted small mb-4 mb-lg-0">Copyright &copy; 2011 - <?php print date('Y', time()); ?> <a href="https://techarise.com/">TECHARISE.COM</a> All rights reserved.</p>
      </div>
      <div class="col-lg-6 h-100 text-center text-lg-right my-auto">
        <ul class="list-inline mb-0">
          <li class="list-inline-item mr-3">
            <a href="#">
              <i class="fab fa-facebook fa-2x fa-fw"></i>
            </a>
          </li>
          <li class="list-inline-item mr-3">
            <a href="#">
              <i class="fab fa-twitter-square fa-2x fa-fw"></i>
            </a>
          </li>
          <li class="list-inline-item">
            <a href="#">
              <i class="fab fa-instagram fa-2x fa-fw"></i>
            </a>
          </li>
        </ul>
      </div>
    </div>
  </div>
</footer>
<!-- Bootstrap core JavaScript -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/js/bootstrap.bundle.min.js"></script>
</body>

</html>

<a href="#">About</a>

</li>

<a href="#">Contact</a>

</li>

<a href="#">Terms of Use</a>

</li>

<a href="#">Privacy Policy</a>

</li>

</ul>

</div>

</a>

</li>

</a>

</li>

</a>

</li>

</ul>

</div>

</footer>

</body>

</html>

Also Read : Ajax Pagination Using CodeIgniter and MySQL

Demo Download