Create Ajax Pagination with MySQL, PHP and jQuery

Hi guys, I received an email from one of our readers requesting tutorial on Ajax table pagination. There are already many tutorials on Ajax Pagination but I saw users comment and found most of the scripts are missing basic features that a standard pagination system must have, so we wrote our own. It is a simple full working ajax pagination system with MySQL, PHP and jQuery. Pagination is highly recommended when you have too much data to be shown on a table or div, you can prevent your user to scroll down a long page and get irritated. Use ajax pagination  and offer an awesome interface to switch between the pages easily. We have already written about delete, update, add rows in a table using PHP, jQuery, MySQL and Ajax, you can use this tutorial to integrate pagination system in our previous tutorial.

ajax-pagination-php

Live Demo

Let me write down features of this little script so that you can decide if it fits your requirement or not:

  • Switch between pages without refreshing the page.
  • Easily set how many rows to be displayed in table by setting value of $limit variable in code.
  • Option to go to First and Last pages. Only visible when [1] (pager 1) and [n](last pager) is not visible.
  •  Option to move next and previous page.
  •  Easy to edit code.

To setup this in your local you can download the demo and create a DB and a table name of your choice. ‘ajaxpage’ table is created for demo. Configure DB details and credentials in ‘db.php’ file. Let’s see some of main files for this script. ‘Index.php file doesn’t have anything except a <div> to display table data which will be returned from ‘dbmanupulate.php.

Lets see our code for script.js:

</p>
$(function(){
 $.ajax({
 url:"dbmanupulate.php",
 type:"POST",
 data:"actionfunction=showData&page=1",
 cache: false,
 success: function(response){

 $('#pagination').html(response);

 }

 });
 $('#pagination').on('click','.page-numbers',function(){
 $page = $(this).attr('href');
 $pageind = $page.indexOf('page=');
 $page = $page.substring(($pageind+5));

 $.ajax({
 url:"dbmanupulate.php",
 type:"POST",
 data:"actionfunction=showData&page="+$page,
 cache: false,
 success: function(response){

 $('#pagination').html(response);

 }

 });
 return false;
 });

});
<p style="text-align: justify;">

dbmanipulate.php file is responsible for pagination logic and returning what to display based on certain conditions. Below is the code of pagination function written in dbmanupulate.php file:

</p>
function pagination($limit,$adjacents,$rows,$page){
 $pagination='';
 if ($page == 0) $page = 1; //if no page var is given, default to 1.
 $prev = $page - 1; //previous page is page - 1
 $next = $page + 1; //next page is page + 1
 $prev_='';
 $first='';
 $lastpage = ceil($rows/$limit);
 $next_='';
 $last='';
 if($lastpage > 1)
 {

 //previous button
 if ($page > 1)
 $prev_.= "<a class='page-numbers' href=\"?page=$prev\">previous</a>";
 else{
 //$pagination.= "<span class=\"disabled\">previous</span>";
 }

 //pages
 if ($lastpage < 5 + ($adjacents * 2)) //not enough pages to bother breaking it up
 {
 $first='';
 for ($counter = 1; $counter <= $lastpage; $counter++)
 {
 if ($counter == $page)
 $pagination.= "<span class=\"current\">$counter</span>";
 else
 $pagination.= "<a class='page-numbers' href=\"?page=$counter\">$counter</a>";
 }
 $last='';
 }
 elseif($lastpage > 3 + ($adjacents * 2)) //enough pages to hide some
 {
 //close to beginning; only hide later pages
 $first='';
 if($page < 1 + ($adjacents * 2))
 {
 for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
 {
 if ($counter == $page)
 $pagination.= "<span class=\"current\">$counter</span>";
 else
 $pagination.= "<a class='page-numbers' href=\"?page=$counter\">$counter</a>";
 }
 $last.= "<a class='page-numbers' href=\"?page=$lastpage\">Last</a>";
 }

 //in middle; hide some front and some back
 elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
 {
 $first.= "<a class='page-numbers' href=\"?page=1\">First</a>";
 for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
 {
 if ($counter == $page)
 $pagination.= "<span class=\"current\">$counter</span>";
 else
 $pagination.= "<a class='page-numbers' href=\"?page=$counter\">$counter</a>";
 }
 $last.= "<a class='page-numbers' href=\"?page=$lastpage\">Last</a>";
 }
 //close to end; only hide early pages
 else
 {
 $first.= "<a class='page-numbers' href=\"?page=1\">First</a>";
 for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
 {
 if ($counter == $page)
 $pagination.= "<span class=\"current\">$counter</span>";
 else
 $pagination.= "<a class='page-numbers' href=\"?page=$counter\">$counter</a>";
 }
 $last='';
 }

 }
 if ($page < $counter - 1)
 $next_.= "<a class='page-numbers' href=\"?page=$next\">next</a>";
 else{
 //$pagination.= "<span class=\"disabled\">next</span>";
 }
 $pagination = "<div class=\"pagination\">".$first.$prev_.$pagination.$next_.$last;
 //next button

 $pagination.= "</div>\n";
 }

echo $pagination;
}
<p style="text-align: justify;">

You will find SQL query in the downloaded demo, that can be used to create table and insert some dummy data into it. Please comment your queries or any modifications that you would like to see in this script. Please share this with your friends and followers.

About sanjeev

sanjeev
Dreamer, Blogger and Thinker. I love to help people in solving their problems. You can also join me HERE

Related posts:

6 Responses so far.

  1. Hi Sanjeev,

    Thanks for providing this. I’m trying to add a variable items per page (see sample below), instead of your fixed variable of $limit.

    $pagination = ‘
    5
    10
    25
    50
    100
    All
    ‘;

    I thought it might be a useful update to you tutorial if you could share how to patch this feature in.

    1. Ok…the comment section didn’t allow my HTML markup. The list above was a simple SELECT with OPTION values.

  2. Hi I found this tutorial great.. I am adding a search functionality, but upon changing the limit of items to show per page an error occurs stating $counter variable is not defined on line 79.

    Please check..

Comments are closed.