Create pagination using php and mysql

This post belongs to newbie php developer. In this post we are going to learn about how to create simple paging program in core php with mysql database.

Paging is very important feature, we always need this feature in our applications.


First of all create your Mysql database and table

CREATE TABLE IF NOT EXISTS `posts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `description` text NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

So see below code and modify it according to your need. this is a very basic level paging in corephp and mysql.

posts.php

<?php
$hostname = 'localhost'; // your mysql hostname
$username = 'root';   // Your mysql db username
$password = 'root';   // You mysql db password
$database = 'demo';       // mysql db name
$con = mysqli_connect($hostname, $username, $password, $database);
 
    if (isset($_GET["page"])) { 
      $page  = $_GET["page"]; 
    } else { 
      $page=1; 
    };
$recordsPerPage=20; 
$start = ($page-1) * $recordsPerPage; 
$query = "SELECT * FROM posts LIMIT $start, $recordsPerPage"; 
$result = mysqli_query($con, $query);
 
echo "<table><tr><th>Title</th><th>Description</th></tr>";
while ($row = mysqli_fetch_assoc($result)) { 
            echo "<tr>
            <td>".$row['title']."</td><td>".$row['description']."</td></tr>";            
}
echo "</table>";
 
$query = "SELECT * FROM posts"; 
$result = mysqli_query($con, $query); //run the query
$totalRecords = mysqli_num_rows($result);  //count number of records
$totalPages = ceil($totalRecords / $recordsPerPage); 
 
echo "<a href='posts.php?page=1'>".'|<'."</a> "; // Go to 1st page  
 
for ($num=1; $num<=$totalPages; $num++) { 
            echo "<a href='posts.php?page=".$num."'>".$num."</a> "; 
}; 
echo "<a href='posts.php?page=$totalPages'>".'>|'."</a> "; // Go to last page
?>

Hope this script will help you to create paging in php and mysql

If you like this post please don’t forget to subscribe My Public Notebook for more useful stuff.