codeofaninja
website

Paginating Your Data with jQuery AJAX and Awesome PHP Pagination Class

Photo of Mike Dalisay
Modified Saturday, June 25, 2011
by - @ninjazhai
Hi there! Today we are going to do a very simple yet useful script for your web apps, a jQuery AJAX pagination with the help of a PHP pagination class. This is very easy to use and integrate with your project.

This code will load the paginated data via AJAX, we'll have some loader image to make it look good and user friendly. Paginated data was returned by our PHP script with the help of a Modified PS_Pagination Class (Yes, I modified it myself since I want to use this Awesome pagination class with jQuery.)


We are going to have the following files for today's code:
  1. images/ajax-loader.gif - for our loader animation
  2. js/jquery-1.4.js - our favorite javascript library
  3. libs/ps_pagination.php - the pagination class I modified
  4. styles/style.css - style for our table data and page number navigation
  5. db_connect.php - for database connection
  6. index.php - our main UI
  7. search_results.php - returns the data that will be displayed to index.php

1.0 Prepare our database table and data. 


We're gonna use the following sample table structure and data.

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `email` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=56 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `firstname`, `lastname`, `email`, `username`, `password`, `modified`) VALUES
(28, 'John', 'Dalisay', '', 'john', 'john123', '2012-01-15 07:26:14'),
(39, 'Jem', 'Panlilio', '', 'jemboy09', 'jem123', '2012-01-15 07:26:46'),
(40, 'Darwin', 'Dalisay', '', 'dada08', 'dada123', '2012-01-15 07:25:34'),
(46, 'Jaylord', 'Bitamug', '', 'jayjay', 'jay123', '2012-01-15 07:27:04'),
(49, 'Justine', 'Bongola', '', 'jaja', 'jaja123', '2012-01-15 07:27:21'),
(50, 'Jun', 'Sabayton', '', 'jun', 'jun123', '2012-02-05 10:15:14'),
(51, 'Lourd', 'De Veyra', '', 'lourd', 'lourd123', '2012-02-05 10:15:36'),
(52, 'Asi', 'Taulava', '', 'asi', 'asi123', '2012-02-05 10:15:58'),
(53, 'James', 'Yap', '', 'james', 'jame123', '2012-02-05 10:16:17'),
(54, 'Chris', 'Tiu', '', 'chris', 'chris123', '2012-02-05 10:16:29');

Here's how we connect to the database. By the way, we are you the PDO extension here.

<?php
$db_host = "localhost";
$db_username = "yourUsername";
$db_password = "yourPassword";
$db_name = "yourDatabase";

try {
    $conn = new PDO("mysql:host={$db_host};dbname={$db_name}", $db_username, $db_password);
}

//to handle connection error
catch(PDOException $exception){
    echo "Connection error: " . $exception->getMessage();
}
?>

2.0 Prepare your loader image, jQuery library and our modified PS Pagination library. 


I will not copy the pagination library codes here. You may just download the code package of this tutorial and you may compare it to its original code by downloading it here.

3.0 User interface


Our user interface will be represented by our index.php file.

<!DOCTYPE HTML>
<html>
    <head>
        <title>jQuery AJAX and PHP Pagination Demo - codeofaninja.com</title>
        
        <!-- include style -->
        <link rel="stylesheet" type="text/css" href="styles/style.css" />
        
    </head>
<body>
<div id='retrieved-data'>
    <!-- 
    this is where data will be  shown
    -->
    <img src="images/ajax-loader.gif" />
</div>

<script type = "text/javascript" src = "js/jquery-1.7.1.min.js"></script>
<script type = "text/javascript">
$(function() {
    //call the function onload
    getdata( 1 );
});

function getdata( pageno ){                     
    var targetURL = 'search_results.php?page=' + pageno;   

    $('#retrieved-data').html('<p><img src="images/ajax-loader.gif" /></p>');        
    $('#retrieved-data').load( targetURL ).hide().fadeIn('slow');
}      
</script>

</body>
</html>

4.0 Getting the page data


This will be the code inside our search_results.php file, where our modified pagination class was used.

<?php
//open database
include 'db_connect.php';
//include our awesome pagination
//class (library)
include 'libs/ps_pagination.php';

//query all data anyway you want
$sql = "select * from users ORDER BY firstname DESC";

//now, where gonna use our pagination class
//this is a significant part of our pagination
//i will explain the PS_Pagination parameters
//$conn is a variable from our config_open_db.php
//$sql is our sql statement above
//3 is the number of records retrieved per page
//4 is the number of page numbers rendered below
//null - i used null since in dont have any other
//parameters to pass (i.e. param1=valu1&param2=value2)
//you can use this if you're gonna use this class for search
//results since you will have to pass search keywords
$pager = new PS_Pagination( $conn, $sql, 3, 4, null );

//our pagination class will render new
//recordset (search results now are limited
//for pagination)
$rs = $pager->paginate(); 

//get retrieved rows to check if
//there are retrieved data
$num = $rs->rowCount();

if($num >= 1 ){     
    //creating our table header
    echo "<table id='my-tbl'>";
    echo "<tr>";
        echo "<th>Firstname</th>";
        echo "<th>Lastname</th>";
        echo "<th>Username</th>";
    echo "</tr>";
    
    //looping through the records retrieved
    while ($row = $rs->fetch(PDO::FETCH_ASSOC)){
        echo "<tr class='data-tr' align='center'>";
        echo "<td>{$row['firstname']}</td>";
        echo "<td>{$row['lastname']}</td>";
        echo "<td>{$row['username']}</td>";
        echo "</tr>";
    }       
    
    echo "</table>";
}else{
    //if no records found
    echo "No records found!";
}
//page-nav class to control
//the appearance of our page 
//number navigation
echo "<div class='page-nav'>";
    //display our page number navigation
    echo $pager->renderFullNav();
echo "</div>";

?>


5.0 Style page links


Style your page links, as for this tutorial, I used the following style.css:

<style type='text/css'>         
/*you can change you table style here*/  
#my-tbl {
    background-color: #FFF;
    color: black;
    padding: 5px;
    width: 700px;
    border: thin solid red;
}

#th{
    background-color: #000;
    color: #FFF;
    padding: 10px; 
    border-bottom: thin solid #000;  
}

.data-tr{
    background-color: #FFF;
    color: #000;
    border-bottom: thin solid #000;
}

.data-tr:hover {
    background-color: #FAEFCF;
    color: black;
    padding: 5px; 
    border-bottom: thin solid #000;
       
}

.data-tr td{       
    padding: 10px;            
    margin: 0px;         
}

/* you can change page navigation here*/
.page-nav{
    margin: 10px 0 0 0;
    padding: 8px;
}

.page-nav a{
    border: none;
    padding: 8px;
    text-decoration: none;
    background-color: #FFC;
    border: thin solid #FC0;
    color: #000;
}

.page-nav a:hover{
    border: thin solid #FC0;
    background-color: #FC0;
    color: #fff;
}

/*this is the style for selected page number*/
.page_link{
    padding: 8px;
}
</style>

I decided to delete the old MySQLi and PDO version pages and put it all in this one post. If you want to see the old comments, see MySQLi version old comments and PDO version old comments

Thanks for reading this jQuery AJAX and PHP pagination example!

The Code of a Ninja Resources

For FREE programming tutorials, click the red button below and subscribe! :)
Thanks for the comments!
 
 
Fundamentals
"First do it, then do it right, then do it better."
~ Addy Osmani
"Talk is cheap. Show me the code."
~ Linus Torvalds
Let's Stay Connected!
g+ r
Android app on Google Play
© 2011-2014 The Code Of A Ninja. All rights reserved. Proudly Powered by Google Blogger. Images, logos, marks or names mentioned herein are the property of their respective owners.