BIP KB:
Pagination Script using PHP PDO with jQuery

Article By salem


img

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.

This tag will display records from the mysql table, and

a second div tag will set and display pagination link.

 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 Pagination using PHP PDO : Coding Cage
 
 
 
 
 
 
 




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);
prepare("SELECT tutsTitle,tutsLink FROM tutorials ORDER BY tutsID DESC LIMIT $position, $item_per_page");
$results->execute();
//getting results from database
?>
    fetch(PDO::FETCH_ASSOC)) { ?>
  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 VPS server in no time flat

Simple setup. Full root access. Straightforward pricing.

DEPLOY VPS SERVER

Leave a Reply



Feedbacks