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.
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
copy paste following mysql code into your PHPMyAdmin to create table.
<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.
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.
-- 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 : tutorialscopy 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.
thank you pradeep for this script :)
ReplyDeleteCPU limit reached. Demo not working.
ReplyDeleteThanks for sharing this Information, Got to learn new things from your Blog on PHP.
ReplyDeleteRef link : http://thecreatingexperts.com/php-training-in-chennai/
cpu-limit-reached
ReplyDeletethe DEMO not work..
Download not work...
ReplyDeleteDownload not working. Could you fix it?
ReplyDeleteDOWNLOAD LINK IS NOT WORKING
ReplyDeleteDownload not work...
ReplyDeletecan you help sir, pagination in search input
ReplyDeleteThe link is borked, please fix asap
ReplyDeleteIssue:
ReplyDeleteif you have thousands of records, then you will see hundreds of pagination numbers !