Simple jQuery AJAX Pagination with PHP and MySQL – Step By Step Guide!

UPDATE: You should learn not only the AJAX pagination. Learn the PHP CRUD (create, read, update, delete, etc.) operations as well.

Go to our PHP, MySQL and AJAX CRUD Tutorial – Step by Step Guide!

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

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

Prepare our database table and data.

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

-- Table structure for table `users`
  `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,
  PRIMARY KEY (`id`)
-- 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');

Create our database connection file

Create db_connect.php file. The following code is how we connect to the database. We are using the PDO extension here.

// Database credentials
$db_host = "your_db_host";
$db_name = "your_db_name";
$db_username = "your_db_username";
$db_password = "your_db_password";
try {
    // Get database connection
    $conn = new PDO("mysql:host={$db_host};dbname={$db_name}", $db_username, $db_password);
// Handle connection error
catch(PDOException $exception){
    echo "Connection error: " . $exception->getMessage();

Prepare your loader image and jQuery

Create “images” folder and put your loader GIF image inside this folder. Generate and download your AJAX loader from the website.

Create “js” folder. Download jQuery and put it inside the “js” folder.

If you’re not yet familiar with jQuery, I highly recommend studying our jQuery Tutorial for Beginners – Step By Step Guide!

User interface

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

        <title>jQuery AJAX and PHP Pagination Demo -</title>
        <!-- Include custom CSS. -->
        <link rel="stylesheet" type="text/css" href="styles/style.css" />
<div id='retrieved-data' style='height:15em;'>
    This is where data will be shown.
    <img src="images/ajax-loader.gif" />
<script type = "text/javascript" src = "js/jquery-1.7.1.min.js"></script>
<script type = "text/javascript">
    // show the first page
function getdata(pageno){
    // source of data
    var targetURL = 'search_results.php?page=' + pageno;
    // show loading animation
    $('#retrieved-data').html('<img src="images/ajax-loader.gif" />');
    // load to show new data

Getting the page data

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

// Connect to database.
include 'db_connect.php';
// Include our pagination class / library.
include 'libs/ps_pagination.php';
// Query all data anyway you want
$sql = "select * from users ORDER BY firstname DESC";
    Now, we are going to 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 - We used null because we don't have any other parameters to pass
    (i.e. param1=valu1&param2=value2)
    You can use this if you are going to use this class for search results.
    The last parameter is useful because you will have to pass the 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();
// Count how many rows of data were returned.
$num = $rs->rowCount();
if($num >= 1 ){
    // Create our table header
    echo "<table id='my-tbl'>";
    echo "<tr>";
        echo "<th>Firstname</th>";
        echo "<th>Lastname</th>";
        echo "<th>Email</th>";
    echo "</tr>";
    // Loop 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['email']}</td>";
        echo "</tr>";
    echo "</table>";
    // If no records found
    echo "No records found!";
// 'page-nav' CSS class is used to control the appearance of our page number navigation
echo "<div class='page-nav'>";
    // Display our page number navigation
    echo $pager->renderFullNav();
echo "</div>";

Modified PS_Pagination Class

The previous section will not work without the PS_Pagination class we modified. Create “libs” folder. Open “libs” folder. Create ps_pagination.php file. Put the following code inside it.

 * PHPSense Pagination Class
 * PHP tutorials and scripts
 * @package     PHPSense
 * @author      Jatinder Singh Thind
 * @copyright   Copyright (c) 2006, Jatinder Singh Thind
 * @link
 * @modification        Mike Dalisay
 * @link      
// ------------------------------------------------------------------------
class PS_Pagination {
    var $php_self;
    var $rows_per_page = 10; //Number of records to display per page
    var $total_rows = 0; //Total number of rows returned by the query
    var $links_per_page = 5; //Number of links to display per page
    var $append = ""; //Paremeters to append to pagination links
    var $sql = "";
    var $debug = false;
    var $conn = false;
    var $page = 1;
    var $max_pages = 0;
    var $offset = 0;
     * Constructor
     * @param resource $connection Mysql connection link
     * @param string $sql SQL query to paginate. Example : SELECT * FROM users
     * @param integer $rows_per_page Number of records to display per page. Defaults to 10
     * @param integer $links_per_page Number of links to display per page. Defaults to 5
     * @param string $append Parameters to be appended to pagination links 
    function PS_Pagination($connection, $sql, $rows_per_page = 10, $links_per_page = 5, $append = "") {
        $this->conn = $connection;
        $this->sql = $sql;
        $this->rows_per_page = (int)$rows_per_page;
        if (intval($links_per_page ) > 0) {
            $this->links_per_page = (int)$links_per_page;
        } else {
            $this->links_per_page = 5;
        $this->append = $append;
        $this->php_self = htmlspecialchars($_SERVER['PHP_SELF'] );
        if (isset($_GET['page'] )) {
            $this->page = intval($_GET['page'] );
     * Executes the SQL query and initializes internal variables
     * @access public
     * @return resource
    function paginate() {
        //Check for valid mysql connection
        //Find total number of rows
        $all_rs = $this->conn->prepare( $this->sql );
        if (! $all_rs) {
            if ($this->debug)
                echo "SQL query failed. Check your query.<br /><br />Error Returned: " . mysql_error();
            return false;
        $this->total_rows = $all_rs->rowCount();
        //Return FALSE if no rows found
        if ($this->total_rows == 0) {
            if ($this->debug)
                echo "Query returned zero rows.";
            return FALSE;
        //Max number of pages
        $this->max_pages = ceil($this->total_rows / $this->rows_per_page );
        if ($this->links_per_page > $this->max_pages) {
            $this->links_per_page = $this->max_pages;
        //Check the page value just in case someone is trying to input an aribitrary value
        if ($this->page > $this->max_pages || $this->page <= 0) {
            $this->page = 1;
        //Calculate Offset
        $this->offset = $this->rows_per_page * ($this->page - 1);
        //Fetch the required result set
        $query = $this->sql . " LIMIT {$this->offset}, {$this->rows_per_page}";
        $rs = $this->conn->prepare( $query );
        if (! $rs) {
            if ($this->debug)
                echo "Pagination query failed. Check your query.<br /><br />Error Returned: " . mysql_error();
            return false;
        return $rs;
     * Display the link to the first page
     * @access public
     * @param string $tag Text string to be displayed as the link. Defaults to 'First'
     * @return string
    function renderFirst($tag = 'First') {
        if ($this->total_rows == 0)
            return FALSE;
        if ($this->page == 1) {
            return "$tag ";
        } else {
            //return '<a href="' . $this->php_self . '?page=1&' . $this->append . '">' . $tag . '</a> ';
            //default to one (1)
            return " <a href='javascript:void(0);' OnClick='getdata( 1 )' title='First Page'>$tag</a> ";
     * Display the link to the last page
     * @access public
     * @param string $tag Text string to be displayed as the link. Defaults to 'Last'
     * @return string
    function renderLast($tag = 'Last') {
        if ($this->total_rows == 0)
            return FALSE;
        if ($this->page == $this->max_pages) {
            return $tag;
        } else {
            //return ' <a href="' . $this->php_self . '?page=' . $this->max_pages . '&' . $this->append . '">' . $tag . '</a>';
            $pageno = $this->max_pages;
            return " <a href='javascript:void(0);' OnClick='getdata( $pageno )' title='Last Page'>$tag</a> ";
     * Display the next link
     * @access public
     * @param string $tag Text string to be displayed as the link. Defaults to '>>'
     * @return string
    function renderNext($tag = '>>') {
        if ($this->total_rows == 0)
            return FALSE;
        if ($this->page < $this->max_pages) {
            //return '<a href="' . $this->php_self . '?page=' . ($this->page + 1) . '&' . $this->append . '" title=\'next to\'>' . $tag . '</a>';
            $pageno = $this->page + 1;
            return " <a href='javascript:void(0);' OnClick='getdata( $pageno )' title='Next Page'>$tag</a> ";
        } else {
            return $tag;
     * Display the previous link
     * @access public
     * @param string $tag Text string to be displayed as the link. Defaults to '<<'
     * @return string
    function renderPrev($tag = '<<') {
        if ($this->total_rows == 0)
            return FALSE;
        if ($this->page > 1) {
            //return ' <a href="' . $this->php_self . '?page=' . ($this->page - 1) . '&' . $this->append . '">' . $tag . '</a>';
            $pageno = $this->page - 1;
            return " <a href='javascript:void(0);' OnClick='getdata( $pageno )' title='Previous Page'>$tag</a> ";
        } else {
            return " $tag ";
     * Display the page links
     * @access public
     * @return string
    function renderNav($prefix = '<span class="page_link">', $suffix = '</span>') {
        if ($this->total_rows == 0)
            return FALSE;
        $batch = ceil($this->page / $this->links_per_page );
        $end = $batch * $this->links_per_page;
        if ($end == $this->page) {
        //$end = $end + $this->links_per_page - 1;
        //$end = $end + ceil($this->links_per_page/2);
        if ($end > $this->max_pages) {
            $end = $this->max_pages;
        $start = $end - $this->links_per_page + 1;
        $links = '';
        for($i = $start; $i <= $end; $i ++) {
            if ($i == $this->page) {
                $links .= $prefix . " $i " . $suffix;                                       
            } else {
                //$links .= ' ' . $prefix . '<a href="' . $this->php_self . '?page=' . $i . '&' . $this->append . '">' . $i . '</a>' . $suffix . ' ';
                //$pageno = $this->page + 1;
                $links .= " <a href='javascript:void(0);' OnClick='getdata( $i )' title='Another page'>$i</a> ";
        return $links;
     * Display full pagination navigation
     * @access public
     * @return string
    function renderFullNav() {
        //echo $this->renderFirst() . " " . $this->renderPrev();
        return $this->renderFirst() . '&nbsp;' . $this->renderPrev() . '&nbsp;' . $this->renderNav() . '&nbsp;' . $this->renderNext() . '&nbsp;' . $this->renderLast();
     * Set debug mode
     * @access public
     * @param bool $debug Set to TRUE to enable debug messages
     * @return void
    function setDebug($debug) {
        $this->debug = $debug;

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;
    background-color: #000;
    color: #FFF;
    padding: 10px; 
    border-bottom: thin solid #000;  
    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*/
    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*/
    padding: 8px;

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

Download Source Code

You can download all the code used in this tutorial for only $9.99 $5.55!

Thank you for learning from our post about: jQuery AJAX and PHP pagination example!

RELATED TUTORIAL: PHP, MySQL and AJAX CRUD Tutorial – Step by Step Guide!

By Mike Dalisay

I'm Mike Dalisay, a pro web developer since 2010. I love web development. Improving our tutorials and source codes makes me happy. Do you want to suggest an edit to our tutorial? Got something to ask about our source codes? You may use our comments section below or email our team at [email protected]


  1. Hi @Pedro, thanks for the kind words about our work! To display more than 3 rows per page, change this:

    $pager = new PS_Pagination($conn, $sql, 3, 4, null);

    to this:

    $pager = new PS_Pagination($conn, $sql, 10, 4, null);

    1. Hi Mike,
      Thanks for your reply. I finally find the solution :)

      I have an other request please. I added search fields such as date range and two combobox. I performed à first test but it failed when i tried to access the second page. it not displayed expected results

      In previous comments i read that i only have to change « TargetUrl » variable. Is this the only thing to change on your code? Or are there other part to modify?

      I’ll post my code if needed.


  2. Dear Mike,

    Thank you so much for this tutorial and it’s working fine but one this am struggling with is one I need to get by category id; it is not showing.

    1. Hi @afaaro , you need to add the category ID as parameter. For example:

      var targetURL = 'search_results.php?page=' + pageno + '&category_id=' + category_id;

  3. Hello:
    I have not read a single line of code, but noticed 1 very amazing thing. You are providing 3 methods of downloading. I think this is the first time I have ever seen that and because of that, AWESOME. Seriously, thank you for your advanced thinking.

  4. Hi Mike,

    Excellence tutorial. I’m new in this, I need little favour from you, how to I need to send 2 parameters when calling the result. Where and how should I go about this?

    My parameters is like these: tmid=1&saluran=1

  5. excellent tutorial! thanks mike… can you make a tutorial series, how to integrate this awesome pagination into awesome live search engine in ajax? thanks in advance… i subscribe for more updates and also waiting for this update also. keep on sharing, you rock…

    1. Hi @3d47fad84c5309c9862ccdba115cb52f, thanks for dropping by! Thanks for the idea, I’m gonna try to make that tutorial series, thanks also for subscribing! :)

  6. 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’;

  7. @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’;

  8. @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..

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

  10. 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:



    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“;


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

    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

  12. 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?

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

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

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


  16. 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???

  17. 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!

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

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

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

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to top