Paginating Your Data with jQuery AJAX and Awesome PHP Pagination Class

Published by - @ninjazhai on June 26, 2011
LIKE and SHARE:

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.)

Paginating Your Data with jQuery AJAX and Awesome PHP Pagination Class

DOWNLOAD MySQL Version
DOWNLOAD MySQLi Version
DOWNLOAD PDO Version
LIVE DEMO

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

  • http://www.gl3nnx.net/ Glenn

    Wow nice tut, ninja mike! Napaka detailed! :)

  • http://www.blogger.com/profile/05733400401287031490 Mike Dalisay

    Thanks sir :)

  • Anonymous

    Great script!. Just one weird issue. The css on my target page is a little goofy when going through the index page but looks just fine when I go directly to it.

    Any thoughts?

    Thanks again for the great script.

  • Anonymous

    Ty =)

  • http://www.blogger.com/profile/05733400401287031490 Mike Dalisay

    @Anonymous_1: Can you give me a link to a screenshot or page of your problem?

    @Anonymous_2: You’re welcome. :)

  • Anonymous

    If you were a girl, I will definitely marry you. I love you… Great work buddy, keep it up…

  • http://www.blogger.com/profile/05733400401287031490 Mike Dalisay

    Hahahahahaha! Thanks @Anonymous! :)

  • Anonymous

    hi i do like your cool and easy pagination class..however is it possible to have pages that would have been edited to have a different link colour.
    For example say i am paginating user comments and have 100 pages of comments and i have 20 records per page and 5 page links at the bottom.How can i have say the page 3 link show a different colour to indicate that a comment has been made on that page?

    i am new to php and ajax

  • http://www.blogger.com/profile/05733400401287031490 Mike Dalisay

    Thanks man, look at the bottom part of my CSS code, you’ll see the .page_link class. That class was used for the current/selected page. You can play around with that to change the link colour/style. :)

  • Joe

    I keep getting an error when I click the links to go to another page:

    “Uncaught ReferenceError: getdata is not defined”

    URL:http://www.25idtacops.com/pbsvss.php

  • http://www.blogger.com/profile/05733400401287031490 Mike Dalisay

    @Joe: I just saw you got it working now, nice. :)

  • http://www.despresuflet.ro/psihologie/anxietate/ psihologie

    wooooooow, great man! thank you so much, i am immediately implementing this on my website! this if of great help! i was looking for a dynamic pagination solution using ajax, and there you go… you have it :) thanks!

  • http://www.blogger.com/profile/06296223448502883720 Joni Wilson

    HI, I try to use this magnifique pagination with MSSQL but i have on error in LIMIT

    $rs = @mysql_query($this->sql . ” LIMIT {$this->offset}, {$this->rows_per_page}” );

    I change mysql_query for mssql_query and LIMIT to TOP, but i have a error:

    Warning: mssql_fetch_array(): supplied argument is not a valid MS SQL-result resource

    Can you Help me???

  • http://www.blogger.com/profile/05733400401287031490 Mike Dalisay

    @psihologie: You’re welcome. :)

    @Joni Wilson: Sorry dude I’m not using MSSQL and not familiar with its syntax..

  • Anonymous

    MSSQL does not support the SQL Limit statement. Try using TOP instead.

  • Anonymous

    Hello thanks for your script. Any clue abut how to use it with a MySQLi connection? I’m always getting Fatal error: Call to a member function query() on a non-object but I don’t really know how to get rid of it.
    Many thanks

    Franz

  • http://www.blogger.com/profile/06860252616929920176 Patrick

    Hi I think this is just the ticket for what I’m looking for but getting an error.

    Method name must be a string..line 111

    Line 111 $result=$pager->$paginate();
    Line 112 $num=mysql_num_rows($results);

    I cannot see anything wrong..
    Any help would be grateful.

  • http://www.blogger.com/profile/08777741601311434750 Helen

    Nice code – I will try and see if I can get it working in a project I currently have.

    Helen Neely

  • http://www.blogger.com/profile/02750979008551926380 Guarro Man

    ease on implementing and configuration. This code rocks!

    Thanks ninja guy

  • Anonymous

    sup… got any hints on what to do when using it with jQuery mobile? teh html gets injected properly but it doesn’t get styled like the other elements on the page. some refresh trigger or something? cheers

  • http://www.blogger.com/profile/00070678456984544813 mahendranthenur

    I like your script ,I would like to implement this script to more then 10 pages.Its is posiple to pass the filename to the javascript function getdata(2) .

  • http://blog.antoniomtz.org/ Antonio Martinez

    I have rewritten your code to work with MongoDB. It works great. But I have a problem now. I had some ajax request and they don’t work.

    For example, I can’t get this:

    var row = $(this).parent().parent();

    Probably because the table is not render and I can’t find elements. What should I do?

  • Anonymous

    Can anybody re-upload please?
    The download isn’t working.
    Thanks.

  • Anonymous

    download worked fine for me.
    Nice script! Learned a lot from it, Im new to dealing with ajax

  • http://www.blogger.com/profile/17634191501413157401 Muhammad Umer

    Dear Sir,

    $sql=mysql_Query(“select * from ads where cityname=’$city’ and type=’$sid’ order by id desc”);

    Whenever i use this sql statment in php it work great in first page but next page i got errors

    like:
    Undefined index: subcatn in D:xampphtdocstestdbads1.php on line 17

    Notice: Undefined index: cityn in D:xampphtdocstestdbads1.php on line 18

    Notice: Undefined index: mcn in D:xampphtdocstestdbads1.php on line 19

    Query returned zero rows.

    What i do now?

    thanks for advance

  • http://www.blogger.com/profile/05733400401287031490 Mike Dalisay

    @Muhammad Umer: Hi there! Please double check your database fields, subcatn, cityn and mcn seems like they does not exist.

  • http://www.blogger.com/profile/17634191501413157401 Muhammad Umer

    Dear Respected Mike.

    I am very tahnkful for your kind reply. but subcatn and cityn and mcn just the values that get from users the database fields is cityname, and type.

    i show you whlo script that is here:

    ‘.$mcn.’->’.$sid;
    ?>

    setDebug(true);

    if ($result){

    $rs = $pager->paginate();
    if(!$rs) die(mysql_error());
    while ($row=mysql_fetch_array($rs)){

    Retrive Data here

    echo $pager->renderFullNav();

    echo ‘

    ‘;
    echo “Post Ad“;

    ?>

  • http://www.blogger.com/profile/17634191501413157401 Muhammad Umer

    whenever i use simple sql query like

    “select * from ads” then its worked great but when i use “select * from ads where cityname=$city” then in first page working good but when i click second page error occor Notice: Undefined index: cityn in D:xampphtdocstestdbads1.php

    This is actual prob with me please help me i will very thankful to you.

  • http://www.blogger.com/profile/17634191501413157401 Muhammad Umer

    Dear Mike,

    your pagination is not working when we use variables in sql statement.

  • http://www.blogger.com/profile/05733400401287031490 Mike Dalisay

    @Muhammad Umer: Glad to know it is now working… By the way, you can paste your code next time in pastebin and link it here so we can see it properly, blogger does not allow code to be posted in comments..

  • http://www.blogger.com/profile/17634191501413157401 Muhammad Umer

    Dear Mike,

    I have posted my code in pastbin please now tell me where is problem my project is stuck there. Please help me.

    you can find code here http://pastebin.ca/2308539

  • http://www.blogger.com/profile/05733400401287031490 Mike Dalisay

    @Muhammad – are you sure you included your parameters such as cityn on your javascript, like in my code, i only have ‘page’ as the parameter.

    var targetURL = ‘search_results.php?page=’ + pageno;

    yours should be something like:

    var targetURL = ‘search_results.php?page=’ + pageno + ‘&cityn=php_generated_cityn_here’;

  • http://www.blogger.com/profile/17634191501413157401 Muhammad Umer

    Sir i used javascript in my page and also give the value of variable but still not working

    i am using code like

    var targetURL = ‘search_results.php?page=’ + pageno + ‘&cityn=$city’;

  • Pingback: Google

  • Mehti

    Mike, I know my wife will hate you because I say this but I love you dude!!! LOVE YOU!!