Pagination Using PHP and MySQL

Pagination is the process of separating print content into discrete pages. Pagination in web development is a mechanism or process to break down digital content into different web pages. We divide records into small parts and send them back in response. Paginations help to reduce unnecessary load on the server because the server returns some records rather than all related records.

So, In this tutorial, we will show you how to Pagination Using PHP and MySQL. This is a very simple example, you can just copy-paste and change it according to your requirement.

Also Read : Pagination Using CodeIgniter and MySQL

So let’s implement Pagination Using PHP and MySQL. Look files structure:

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

Step 1: Create the database and Table

Run the following script against MySQL to create the database news table.

--
-- Table structure for table `news`
--

CREATE TABLE `news` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `status` int(1) NOT NULL,
  `author` varchar(255) NOT NULL,
  `created_date` date NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `news`
--

INSERT INTO `news` (`id`, `title`, `description`, `status`, `author`, `created_date`) VALUES
(1, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),
(2, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),
(3, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),
(4, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),
(5, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),
(6, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),
(7, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),
(8, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),
(9, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),
(10, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),
(11, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),
(12, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),
(13, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `news`
--
ALTER TABLE `news`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `news`
--
ALTER TABLE `news`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;
COMMIT;

-- Table structure for table `news`

CREATE TABLE `news` (

`id` int(11) NOT NULL,

`title` varchar(255) NOT NULL,

`description` text NOT NULL,

`status` int(1) NOT NULL,

`author` varchar(255) NOT NULL,

`created_date` date NOT NULL DEFAULT current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table `news`

INSERT INTO `news` (`id`, `title`, `description`, `status`, `author`, `created_date`) VALUES

(1, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),

(2, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),

(3, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),

(4, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),

(5, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),

(6, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),

(7, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),

(8, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),

(9, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),

(10, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),

(11, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),

(12, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31'),

(13, 'Lorem Ipsum is simply dummy text of the printing.', 'It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.', 1, 'CodersMag Team', '2022-12-31');

-- Indexes for dumped tables

-- Indexes for table `news`

ALTER TABLE `news`

ADD PRIMARY KEY (`id`);

-- AUTO_INCREMENT for dumped tables

-- AUTO_INCREMENT for table `news`

ALTER TABLE `news`

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

COMMIT;

Step 2: Create a class file named DbConnection.php (Database Configuration) inside “class/” folder

The DbConnection.php file is used to connect database.

<?php

/**
 * @package Database Connection(Config)
 *
 * @author CodersMag Team
 *
 * @email  info@codersmag.com
 *   
 */
class DBConnection {
    // HOST NAME
    private $_dbHostname = "localhost";
    // DATABASE NAME
    private $_dbName = "coders_DB";
    // DATABASE USERNAME
    private $_dbUsername = "root";
    // DATABASE PASSWORD
    private $_dbPassword = "";
    // declare variable
    private $_dbh;

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

    }

    // return PDO: Connection
    public function returnPDOConnection() {
        return $this->_dbh;
    }
}

?>

<?php

/**

* @package Database Connection(Config)

* @author CodersMag Team

* @email info@codersmag.com

class DBConnection {

// HOST NAME

private $_dbHostname = "localhost";

// DATABASE NAME

private $_dbName = "coders_DB";

// DATABASE USERNAME

private $_dbUsername = "root";

// DATABASE PASSWORD

private $_dbPassword = "";

// declare variable

private $_dbh;

// __construct

public function __construct() {

try {

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

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

} catch(PDOException $e) {

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

}

// return PDO: Connection

public function returnPDOConnection() {

return $this->_dbh;

}

Step 3: Create a class file named News.php inside “class/” folder.

<?php

/**
 * @package News
 *
 * @author CodersMag Team
 *
 * @email  info@codersmag.com
 *   
 */

// include connection class
include("DBConnection.php");

// News
class News
{
    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;
    }

    // __construct
    public function __construct()
    {
        $this->_db = new DBConnection();
        $this->_db = $this->_db->returnPDOConnection();
    }

    // get All News from databse
    public function getAllNews()
    {
        try {
            $sql = "SELECT * FROM news ";
            $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 $error) {
            die("Exception Caught!: ".$error->getMessage());
        }
    }

    // count all News
    public function countNews()
    {
        try {
            $sql = "SELECT * FROM news";
            $stmt = $this->_db->prepare($sql);
            $stmt->execute();
            $count = $stmt->rowCount();
            return $count;
        } catch (Exception $error) {
            die("Exception Caught!: ".$error->getMessage());
        }
    }
}

?>

<?php

/**

* @package News

* @author CodersMag Team

* @email info@codersmag.com

// include connection class

include("DBConnection.php");

// News

class News

{

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;

}

// __construct

public function __construct()

{

$this->_db = new DBConnection();

$this->_db = $this->_db->returnPDOConnection();

}

// get All News from databse

public function getAllNews()

{

try {

$sql = "SELECT * FROM news ";

$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 $error) {

die("Exception Caught!: ".$error->getMessage());

}

// count all News

public function countNews()

{

try {

$sql = "SELECT * FROM news";

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

$stmt->execute();

$count = $stmt->rowCount();

return $count;

} catch (Exception $error) {

die("Exception Caught!: ".$error->getMessage());

}

Step 5: Create action file named index.php

<?php
// include News Class
include_once 'class/News.php';

define("ROW_PER_PAGE", 5);
$page = 1;
$start = 0;

if (!empty($_GET["page"])) {
  $page = $_GET["page"];
  $start = ($page - 1) * ROW_PER_PAGE;
}

$news = new News();
$news->setRowPerPage(ROW_PER_PAGE);
$news->setStart($start);
$news->setPage($page);

$newsInfo = $news->getAllNews();
$totaNewsCount = $news->countNews();
include('templates/header.php');
$per_page_html = '';
?>
<div class="content">
  <div class="container">
    <h2 class="mt-4">Pagination Using PHP and MySQL</h2>
    <div class="row">
      <div class="col-lg-12">
        <table class="table table-striped">
          <thead>
            <tr>
              <th style="width: 30%;">Title</th>
              <th style="width: 50%;">Description</th>
              <th style="width: 10%;">Author</th>
              <th style="width: 10%;">Date</th>
            </tr>
          </thead>
          <tbody>
            <?php
            // Display News Records
            foreach ($newsInfo as $key => $element) { ?>
              <tr>
                <td><?php echo $element['title']; ?></td>
                <td><?php echo $element['description']; ?></td>
                <td><?php echo $element['author']; ?></td>
                <td><?php echo $element['created_date']; ?></td>
              </tr>
            <?php } ?>
          </tbody>
        </table>
        <?php
        //Page Navigation
        if (!empty($totaNewsCount)) {
          $per_page_html .= '<ul class="pagination float-right">';
          $pageCount = ceil($totaNewsCount / ROW_PER_PAGE);
          if ($pageCount > 1) {
            for ($i = 1; $i <= $pageCount; $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>
<?php include('templates/footer.php'); ?>

<?php

// include News Class

include_once 'class/News.php';

define("ROW_PER_PAGE", 5);

$page = 1;

$start = 0;

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

$page = $_GET["page"];

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

}

$news = new News();

$news->setRowPerPage(ROW_PER_PAGE);

$news->setStart($start);

$news->setPage($page);

$newsInfo = $news->getAllNews();

$totaNewsCount = $news->countNews();

include('templates/header.php');

$per_page_html = '';

<h2 class="mt-4">Pagination Using PHP and MySQL</h2>

<thead>

<tr>

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

<th style="width: 50%;">Description</th>

<th style="width: 10%;">Author</th>

</tr>

</thead>

<tbody>

<?php

// Display News Records

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

<tr>

</tr>

<?php } ?>

</tbody>

</table>

<?php

//Page Navigation

if (!empty($totaNewsCount)) {

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

$pageCount = ceil($totaNewsCount / ROW_PER_PAGE);

if ($pageCount > 1) {

for ($i = 1; $i <= $pageCount; $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>

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

Step 6: Create header (header.php)

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.

Located at the top of a website, the header typically contains elements that include a company’s logo, website navigation, and contact information.

<!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="Coders Mag">
  <meta name="author" content="Coders Mag">
  <title>Pagination Using PHP and MySQL | Coders Mag</title>
  <link rel="icon" type="image/ico" href="https://codersmag.com/wp-content/themes/v1/favicon.ico">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/css/bootstrap.min.css" />
  <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">
  <link href="css/style.css" rel="stylesheet">
</head>

<body>
  <nav class="navbar navbar-expand-lg navbar-dark static-top" style="background: #0084B4;">
    <div class="container">
      <a class="navbar-brand font-weight-bold" href="https://codersmag.com">
        <h1>Coders Mag</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://codersmag.com">Home
              <span class="sr-only">(current)</span>
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="https://codersmag.com/php-script-demos">Live Demo</a>
          </li>
        </ul>
      </div>
    </div>
  </nav>

<!DOCTYPE html>

<head>

<title>Pagination Using PHP and MySQL | Coders Mag</title>

</head>

<body>

<h1>Coders Mag</h1>

</a>

</button>

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

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

</a>

</li>

</li>

</ul>

</div>

</nav>

Step 7: Create a view file named footer.php

This view contains the footer section of the webpage.

jQuery – needed by Bootstrap JavaScript.
Bootstrap JavaScript – to make cool UI

<footer class="footer bg-light footer-bg-dark">
  <div class="container">
    <div class="row">
      <div class="col-lg-12 h-100 text-center text-lg-left my-auto">
        <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>
  </div>
</footer>
<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>

</div>

</footer>

</body>

</html>

Also Read: PHP CRUD Operations using jQuery, Ajax and MySQL

Demo Download