Creating a Simple Pagination Script using PHP PDO with jQuery

hi there, this tutorial will cover creating a simple pagination script using PHP MySQL with jQuery, i have used here "bootpag - jQuery plugin for dynamic pagination" this plugin is easy to use which creates dynamic pagination with jQuery using PHP, when we have number of records in MySQL table, we must paginate them, we already have a pagination tutorial using PHP OOP and PDO without jQuery, now in this tutorial i have used jQuery which makes pagination easy, so before proceed you can see live demo of jQuery pagination or you can download and try it, have a look.

Pagination Demo

Download Script

there are only three file we need to achieve pagination and they are :
-- index.php
-- get_records.php
-- dbconfig.php
-- jquery.bootpag.min.js

click here to get detailed about bootpag : bootpag

Database Design and Table

Database : db_pagination Table : tutorials
copy paste following mysql code into your PHPMyAdmin to create table.


CREATE TABLE IF NOT EXISTS `tutorials` (
  `tutsID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `tutsTitle` varchar(100) NOT NULL,
  `tutsLink` varchar(100) NOT NULL,
  PRIMARY KEY (`tutsID`),
  FULLTEXT KEY `tutsTitle` (`tutsTitle`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=57 ;

dbconfig.php

Simple Database Configuration file using PDO extension, edit username, password, host name as per your configuration.

<?php

 $db_username = 'root';
 $db_password = '';
 $db_name = 'db_pagination';
 $db_host = 'localhost';
 $item_per_page = 7;
 
 
 try
 {
  $dbcon = new PDO("mysql:host={$db_host};dbname={$db_name}",$db_username,$db_password);
  $dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 }
 catch(PDOException $e)
 {
  echo $e->getMessage();
 }

?>

Getting Total number of Pages

using the following code you can get total number of pages, in ceil() function i have passed simple logic (total_rows/itep_per_page). isn't it simple.

$results = $dbcon->prepare("SELECT COUNT(*) FROM tutorials");
$results->execute();
$get_total_rows = $results->fetch();

//breaking total records into pages
$pages = ceil($get_total_rows[0]/$item_per_page);

jQuery/javaScript Code

To execute the following jQuery code we need to add "jquery.bootpag.min.js" plugin file within head tag. once we get page number from the above code by assigning $pages variable , now set this variable as a total's value like this total: <?php echo $pages; ?>

<script type="text/javascript">
$(document).ready(function() {
 $("#results").load("get_records.php");  //initial page number to load
 $(".paging_link").bootpag({
    total: <?php echo $pages; ?>
 }).on("page", function(e, num){
  e.preventDefault();
  $("#results").prepend('<div class="loading-indication"><img src="ajax-loader.gif" /> Loading...</div>');
  $("#results").load("get_records.php", {'page':num});
 });

});
</script>

index.php / Complete Index.php Code

this file contains complete source code which is explained above. here in this code i have given
<div id="results"></div> tag which will display records from mysql table, and
<div class="paging_link"></div> second div tag which will set and display pagination link.

<?php
include("dbconfig.php");

$results = $dbcon->prepare("SELECT COUNT(*) FROM tutorials");
$results->execute();
$get_total_rows = $results->fetch();

//breaking total records into pages
$pages = ceil($get_total_rows[0]/$item_per_page); 


?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>jQuery Pagination using PHP PDO : Coding Cage</title>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>     

<script type="text/javascript" src="js/jquery.bootpag.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
 $("#results").load("get_records.php");  //initial page number to load
 $(".paging_link").bootpag({
    total: <?php echo $pages; ?>
 }).on("page", function(e, num){
  e.preventDefault();
  $("#results").prepend('<div class="loading-indication"><img src="ajax-loader.gif" /> Loading...</div>');
  $("#results").load("get_records.php", {'page':num});
 });

});
</script>
<link href="css/style.css" rel="stylesheet" type="text/css">
</head>
<body>
        

<br />


<div id="results"></div>

<br />

<div class="paging_link"></div>

</body>
</html>

get_records.php

contains mainly PHP code which will fetch records from MySQL Database with the starting position and item per page here is the simple logic for position $position = (($page_number-1) * $item_per_page);

<?php
require_once 'dbconfig.php'; //include required dbconfig file

//sanitize post value
if(isset($_POST["page"])){
 $page_number = filter_var($_POST["page"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH);
 if(!is_numeric($page_number)){die('Invalid page number!');} //incase of invalid page number
}else{
 $page_number = 1;
}

//get current starting point of records
$position = (($page_number-1) * $item_per_page);

$results = $dbcon->prepare("SELECT tutsTitle,tutsLink FROM tutorials ORDER BY tutsID DESC LIMIT $position, $item_per_page");
$results->execute();

//getting results from database
?>
<ul class="page_result">
<?php
while($row = $results->fetch(PDO::FETCH_ASSOC))
{
 ?>
    <li>
    <a href="<?php echo $row['tutsLink']; ?>"><?php echo $row['tutsTitle']; ?></a>
    </li>
    <?php
}
?>
</ul>

That's it, isn't it cool, how it's become easy using jquery (jquery plugin) to create pagination with PHP MySQL, see live demo and download script , if you face any issue regarding this script feel free to comment, thank you.

Pagination Demo

Download Script