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.