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'; 
$username = 'root';   
$password = 'root';   
$database = 'demo';       
$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); 
$totalRecords = mysqli_num_rows($result);  
$totalPages = ceil($totalRecords / $recordsPerPage); 
 
echo "<a href='posts.php?page=1'>".'|<'."</a> "; 
 
for ($num=1; $num<=$totalPages; $num++) { 
            echo "<a href='posts.php?page=".$num."'>".$num."</a> "; 
}; 
echo "<a href='posts.php?page=$totalPages'>".'>|'."</a> "; 
?>

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.