pagination

Introduction To Pagination Scripting

In this tutorial we will cover HOW TO create a simple Pagination Script using PHP MySQL with jQuery. I’m going to use the “bootpag – jQuery plugin for dynamic pagination” . This plugin is easy to use and creates dynamic pagination with jQuery using PHP. 

This really comes in handy when we have a number of records in a MySQL table, and they need to be paginated.

First we need to create this file:

-- index.php
 -- get_records.php
 -- dbconfig.php
 -- jquery.bootpag.min.js

Database Design And Table

Database : db_pagination Table : tutorials

Copy paste the 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 ;

Contenu de la table `tutorials`

INSERT INTO `tutorials` (`tutsID`, `tutsTitle`, `tutsLink`) VALUES
(23, 'this tutorial is N : 23', '#'),
(24, 'this tutorial is N : 24', '#'),
(25, 'this tutorial is N : 25', '#'),
(26, 'this tutorial is N : 26', '#'),
(27, 'this tutorial is N : 27', '#'),
(28, 'this tutorial is N : 28', '#'),
(29, 'this tutorial is N : 29', '#'),
(30, 'this tutorial is N : 30', '#'),
(31, 'this tutorial is N : 31', '#'),
(32, 'this tutorial is N : 32', '#'),
(33, 'this tutorial is N : 33', '#'),
(34, 'this tutorial is N : 34', '#'),
(35, 'this tutorial is N : 35', '#'),
(36, 'this tutorial is N : 36', '#'),
(37, 'this tutorial is N : 37', '#'),
(38, 'this tutorial is N : 38', '#'),
(39, 'this tutorial is N : 39', '#'),
(40, 'this tutorial is N : 40', '#'),
(41, 'this tutorial is N : 41', '#'),
(42, 'this tutorial is N : 42', '#'),
(43, 'this tutorial is N : 43', '#'),
(44, 'this tutorial is N : 44', '#'),
(45, 'this tutorial is N : 45', '#'),
(46, 'this tutorial is N : 46', '#'),
(47, 'this tutorial is N : 47', '#'),
(48, 'this tutorial is N : 48', '#'),
(49, 'this tutorial is N : 49', '#'),
(50, 'this tutorial is N : 50', '#'),
(51, 'this tutorial is N : 51', '#'),
(52, 'this tutorial is N : 52', '#'),
(53, 'this tutorial is N : 53', '#'),
(54, 'this tutorial is N : 54', '#'),
(55, 'this tutorial is N : 55', '#'),
(56, 'this tutorial is N : 56', '#'),
(57, 'this tutorial is N : 57', '#');

Dbconfig.Php

Is a simple database configuration file using PDO extension.

Edit the username, password and host name as per your configuration.

$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 the total number of pages. It looks something like this.

In the ceil() function i pass some 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 the “jquery.bootpag.min.js” plugin file within the header tag.

Once we get the page number from the above code (by assigning $pages variable), we can set this variable as the total value like this:

<script type="text/javascript">
$(document).ready(function() { $("#results").load("get_records.php"); //initial page number to load $(".paging_link").bootpag({ total: }).on("page", function(e, num){ e.preventDefault(); $("#results").prepend(' '); $("#results").load("get_records.php", {'page':num}); }); });
</script>

Index.Php / Complete Index.Php Code

This file contains the complete source code which is explained above. Within this code, I’ve given it a tag as well, which will display records from a mysql table. It also has a second div tag which will set and display pagination link.

<div id=”results”></div>

This tag will display records from the mysql table, and <div class=”paging_link”></div> a second div tag 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

This file contains mainly the PHP code used to fetch the records from the MySQL Database. With this starting position the item per page is set by this simple logic:

$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, This is how we can use jquery (jquery plugin) to create pagination with PHP and MySQL.

Click here to get detailed about bootpag

Click here to get detailed about ceil()



Tags: , , , ,

Spin up a cloud server in no time flat

Simple setup. Full root access. Straightforward pricing.


DEPLOY SERVER