February 11, 2024 5:01 am

How to create Advanced Pagination in PHP & MySQL with jQuery

We have already created 2 pagination[1][2] articles for PHP Developers and this one is the advanced version of pagination with “…” many readers requested me to make it with these dots, so today I am going to write this article on advance pagination with jQuery and show you how to show small list of numbers with dots in between.

ajax-pagination

[wpdm_file id=52]DEMO

Database design and table:
database name => phpgang
table name => pagination
column names => id, post, postlink
db.sql
Database file run in your mysql to create database and add data in table.

create database `phpgang`;
use `phpgang`;
CREATE TABLE `pagination` (
  `id` int(11) NOT NULL auto_increment,
  `post` varchar(250) NOT NULL,
  `postlink` varchar(250) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=48 DEFAULT CHARSET=latin1 AUTO_INCREMENT=48 ;

INSERT INTO `pagination` VALUES (1, 'How to Resize text using jQuery', 'https://www.phpgang.com/?p=312');
INSERT INTO `pagination` VALUES (2, 'How to Integrate live search in PHP and MySQL with jQuery', 'https://www.phpgang.com/?p=309');
INSERT INTO `pagination` VALUES (3, 'How to implement jquery Timeago with php', 'https://www.phpgang.com/?p=290');
INSERT INTO `pagination` VALUES (4, 'How to Mask an input box in jQuery', 'https://www.phpgang.com/?p=304');
INSERT INTO `pagination` VALUES (5, 'How to block Inappropriate content with javascript validation', 'https://www.phpgang.com/?p=301');
INSERT INTO `pagination` VALUES (6, 'How to Crop Image with jQuery and PHP', 'https://www.phpgang.com/?p=298');
INSERT INTO `pagination` VALUES (7, 'How to Integrate jQuery Scroll Paging with PHP', 'https://www.phpgang.com/?p=294');
INSERT INTO `pagination` VALUES (8, 'Bug Reporting with Windows Program Steps Recorder (PSR)', 'https://www.phpgang.com/?p=291');
INSERT INTO `pagination` VALUES (9, 'How to Configure Google Cloud API in PHP', 'https://www.phpgang.com/?p=288');
INSERT INTO `pagination` VALUES (10, 'How to convert text to MP3 Voice', 'https://www.phpgang.com/?p=284');

db.php
Database configuration file edit database name, user and password as per your configuration.

<?php
$connection = mysqli_connect('localhost','phpgang_usr','******','phpgang') or die(mysqli_error($connection));
?>

style.css
Contain styles for your pagination numbers and next and previous.

<style>
div.pagination {
padding: 3px;
margin: 3px;
text-align:center;
}

div.pagination a {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #AAAADD;

text-decoration: none; /* no underline */
color: #000099;
}
div.pagination a:hover, div.digg a:active {
border: 1px solid #000099;

color: #000;
}
div.pagination span.current {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #000099;

font-weight: bold;
background-color: #000099;
color: #FFF;
}
div.pagination span.disabled {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #EEE;

color: #DDD;
}

</style>

JQuery Required to handle AJAX functions:

<script type="text/javascript" src="jquery-1.8.0.min.js"></script>
<script type="text/javascript">
function changePagination(pageId){
     $(".flash").show();
     $(".flash").fadeIn(400).html
                ('Loading <img src="ajax-loader.gif" />');
     var dataString = 'pageId='+ pageId;
     $.ajax({
           type: "POST",
           url: "loadData.php",
           data: dataString,
           cache: false,
           success: function(result){
           $(".flash").hide();
                 $("#pageData").html(result);
           }
      });
}
</script>

loadData.php

This file contain complete Logic of the pagination and in index file we gather all js and css together:

<?php
include_once('db.php');

$query="select id from pagination order by id asc";
$res    = mysqli_query($connection,$query);
$count  = mysqli_num_rows($res);
$page = (int) (!isset($_REQUEST['pageId']) ? 1 :$_REQUEST['pageId']);
$page = ($page == 0 ? 1 : $page);
$recordsPerPage = 5;
$start = ($page-1) * $recordsPerPage;
$adjacents = "2";

$prev = $page - 1;
$next = $page + 1;
$lastpage = ceil($count/$recordsPerPage);
$lpm1 = $lastpage - 1;   
$pagination = "";
if($lastpage > 1)
    {   
        $pagination .= "<div class='pagination'>";
        if ($page > 1)
            $pagination.= "<a href=\"#Page=".($prev)."\" onClick='changePagination(".($prev).");'>&laquo; Previous&nbsp;&nbsp;</a>";
        else
            $pagination.= "<span class='disabled'>&laquo; Previous&nbsp;&nbsp;</span>";   
        if ($lastpage < 7 + ($adjacents * 2))
        {   
            for ($counter = 1; $counter <= $lastpage; $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<span class='current'>$counter</span>";
                else
                    $pagination.= "<a href=\"#Page=".($counter)."\" onClick='changePagination(".($counter).");'>$counter</a>";     

            }
        }   

        elseif($lastpage > 5 + ($adjacents * 2))
        {
            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 href=\"#Page=".($counter)."\" onClick='changePagination(".($counter).");'>$counter</a>";     
                }
                $pagination.= "...";
                $pagination.= "<a href=\"#Page=".($lpm1)."\" onClick='changePagination(".($lpm1).");'>$lpm1</a>";
                $pagination.= "<a href=\"#Page=".($lastpage)."\" onClick='changePagination(".($lastpage).");'>$lastpage</a>";   

           }
           elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
           {
               $pagination.= "<a href=\"#Page=\"1\"\" onClick='changePagination(1);'>1</a>";
               $pagination.= "<a href=\"#Page=\"2\"\" onClick='changePagination(2);'>2</a>";
               $pagination.= "...";
               for($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
               {
                   if($counter == $page)
                       $pagination.= "<span class='current'>$counter</span>";
                   else
                       $pagination.= "<a href=\"#Page=".($counter)."\" onClick='changePagination(".($counter).");'>$counter</a>";     
               }
               $pagination.= "..";
               $pagination.= "<a href=\"#Page=".($lpm1)."\" onClick='changePagination(".($lpm1).");'>$lpm1</a>";
               $pagination.= "<a href=\"#Page=".($lastpage)."\" onClick='changePagination(".($lastpage).");'>$lastpage</a>";   
           }
           else
           {
               $pagination.= "<a href=\"#Page=\"1\"\" onClick='changePagination(1);'>1</a>";
               $pagination.= "<a href=\"#Page=\"2\"\" onClick='changePagination(2);'>2</a>";
               $pagination.= "..";
               for($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
               {
                   if($counter == $page)
                        $pagination.= "<span class='current'>$counter</span>";
                   else
                        $pagination.= "<a href=\"#Page=".($counter)."\" onClick='changePagination(".($counter).");'>$counter</a>";     
               }
           }
        }
        if($page < $counter - 1)
            $pagination.= "<a href=\"#Page=".($next)."\" onClick='changePagination(".($next).");'>Next &raquo;</a>";
        else
            $pagination.= "<span class='disabled'>Next &raquo;</span>";

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

if(isset($_POST['pageId']) && !empty($_POST['pageId']))
{
    $id=$_POST['pageId'];
}
else
{
    $id='0';
}
$query="select post,postlink from pagination order by id asc
limit ".mysqli_real_escape_string($connection,$start).",$recordsPerPage";
//echo $query;
$res    =   mysqli_query($connection,$query);
$count  =   mysqli_num_rows($res);
$HTML='';
if($count > 0)
{
    while($row=mysqli_fetch_array($res))
    {
        $post=$row['post'];
        $link=$row['postlink'];
        $HTML.='<div>';
        $HTML.='<a href="'.$link.'" target="_blank">'.$post.'</a>';
        $HTML.='</div><br/>';
    }
}
else
{
    $HTML='No Data Found';
}
echo $HTML;
echo $pagination;
?>

Your request via AJAX function sent to that php file and it will process the request and return you paginated data.

This line of code return all numbers in the table and show count.

$query="select id from pagination order by id asc";
$res=mysql_query($query);
$count=mysql_num_rows($res);

It will show you 5 records on each page you can increase records and decrease by changing this value $start = ($page-1) * 5; to any number you want.

Index file complete code of css and jquery together:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>How to create pagination in PHP and MySQL with AJAX | PGPGang.com</title>
    <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
    <script type="text/javascript" src="jquery-1.8.0.min.js"></script>
<style>
div.pagination {
padding: 3px;
margin: 3px;
text-align:center;
}

div.pagination a {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #AAAADD;

text-decoration: none; /* no underline */
color: #000099;
}
div.pagination a:hover, div.digg a:active {
border: 1px solid #000099;

color: #000;
}
div.pagination span.current {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #000099;

font-weight: bold;
background-color: #000099;
color: #FFF;
}
div.pagination span.disabled {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #EEE;

color: #DDD;
}

</style>

<script type="text/javascript">
$(document).ready(function(){
changePagination('0');	
});
function changePagination(pageId){
     $(".flash").show();
     $(".flash").fadeIn(400).html
                ('Loading <img src="ajax-loader.gif" />');
     var dataString = 'pageId='+ pageId;
     $.ajax({
           type: "POST",
           url: "loadData.php",
           data: dataString,
           cache: false,
           success: function(result){
           $(".flash").hide();
                 $("#pageData").html(result);
           }
      });
}
</script>
  </head>
  <body>
    <h2>How to create pagination in PHP and MySQL with AJAX example.&nbsp;&nbsp;&nbsp;=> <a href="https://www.phpgang.com/">Home</a> | <a href="http://demo.phpgang.com/">More Demos</a></h2>

<div id="pageData"></div>
<span class="flash"></span>

</body>
</html>

Feel free to comment your suggestions and problems you are facing in regard this tutorial.

Author Huzoor Bux

I am Huzoor Bux from Karachi (Pakistan). I have been working as a PHP Developer from last 5+ years, and its my passion to learn new things and implement them as a practice. Basically I am a PHP developer but now days exploring more in HTML5, CSS and jQuery libraries.


Tutorial Categories:

64 responses to “How to create Advanced Pagination in PHP & MySQL with jQuery”

  1. Mahbub Hossain says:

    Thanks bro

  2. ugurpin says:

    thanks man but how can i change the number of pages it s stuck with 10

    • huzoorbux says:

      You have to increase number of your total records in this query i counted number of records.

      $query=”select id from pagination order by id asc”;
      $res=mysql_query($query);
      $count=mysql_num_rows($res);
      for dummy you can do it like that $count = 200; on line number 6 in loadData.php.

  3. ugurpin says:

    for instance i want my total number of pages are to be 5 what should i do ? (sorry for my english) :))

    • huzoorbux says:

      Read instruction given in this tutorial (It will show you 5 records on each page you can increase records and decrease by changing this value $start = ($page-1) * 5; to any number you want.)

      • ugurpin says:

        i think that in your database you had 50 rows that s why the navigation bar ended with 10 pages, so i tried to impliment this in my database which has 20 rows but it didn t work so i changed some numbers for instance i divided $count by 2 so it worked but it continued to print 10 pages however i was only 5 page result at all (20 rows 5 results for each page),

        i tried to discover its arithmetic but i didn t find it so how can i modify it ?

        thank you (i can send my loadData.php if you want)

  4. Greg says:

    This code is appallingly bad. There is no clear separation of concerns, the database functions being used have been deprecated and have far better and safer equivalents (PDO, for example) and the code is needlessly verbose and haphazard. It has all the hallmarks of an amateur developer’s code. Sure it’ll work but it is utterly unmaintainable.

    To suggest this is in any way “advanced” is laughable

  5. Greg says:

    It is poor form to delete comments from people who point out the flaws in your articles. Address the issues like a grown up, or don’t open a forum for comments at all.

  6. vivek says:

    Hello sir, i have seen this post and i must agree this is awesome. The thing i want to mention here is that when we change the page content by clicking the number the next page result appear and simultaneously the url changes, up till this sound good but when i click on browsers back button only the url get changes not the page content, any solution to change the content when user click on back button, will make this example much advance. Thanks n regards : vivek

    • huzoorbux says:

      @disqus_0kqNLfcZZj:disqus Thnks for your comment I am working on this feature and hopefully publish it very soon.

  7. satyendra says:

    hello sir i have used this code its work properly but when rewrite URL using .htaccesses i am faced problem.

  8. Avatar says:

    Hello sir, I have some problem with file “loadData.php” it shows me an error
    Undefined variable: counter in /Paginaion/loadData.php on line 71, the exact string is if($page < $counter – 1)
    how can i fix it?

  9. splen says:

    Can you pls. give an explanation re the constants that you used. For example, the number 4 in line 29 of loadData.php. Or the number 5 that is frequently appearing. Is it equal to the numbers of rows to be displayed. Thank you.

  10. rajendher says:

    sir,
    i am getting following error message in “loadData.php” why ?
    The page cannot be displayed because an internal server error has occurred.
    there is no error in database connection but not getting please help sir…………

  11. rajendher says:

    sir,
    i want to display 10 results per page.

    and in mobile also page numbers are displaying in a single row like this « Previous 12…34567..910Next »
    i need

    « Previous 12…
    34567..910Next » like this depending upon the device width.
    can you help me……

  12. rajendher says:

    sir,
    i was changed line no. 88 limit “.mysqli_real_escape_string($connection,$start).”,5″;
    i.e, limit “.mysqli_real_escape_string($connection,$start).”,10″; . now which is displaying 10 results. but the data is repeating again. (i have 50 records only)

    and always 10 page numbers were displayed. where should i modify it. please reply……

    • huzoorbux says:

      There is a mistake in code I have fixed it no please download source code again and use it.
      I have added a variable $recordsPerPage = 5; change value of that variable to increase/decrease number of record per page.

  13. Ashu says:

    Sir,

    my page links are not working, it only displays page 1 content but clicks on page 2 or next is not working, here is a code:

    1)

    {

    $pagination .= “”;

    if ($page > 1)

    $pagination.= “« Previous  “;

    else

    $pagination.= “« Previous  “;

    if ($lastpage < 7 + ($adjacents * 2))

    {

    for ($counter = 1; $counter <= $lastpage; $counter++)

    {

    if ($counter == $page)

    $pagination.= "$counter“;

    else

    $pagination.= “$counter“;

    }

    }

    elseif($lastpage > 5 + ($adjacents * 2))

    {

    if($page < 1 + ($adjacents * 2))

    {

    for($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)

    {

    if($counter == $page)

    $pagination.= "$counter“;

    else

    $pagination.= “$counter“;

    }

    $pagination.= “…”;

    $pagination.= “$lpm1“;

    $pagination.= “$lastpage“;

    }

    elseif($lastpage – ($adjacents * 2) > $page && $page > ($adjacents * 2))

    {

    $pagination.= “1“;

    $pagination.= “2“;

    $pagination.= “…”;

    for($counter = $page – $adjacents; $counter <= $page + $adjacents; $counter++)

    {

    if($counter == $page)

    $pagination.= "$counter“;

    else

    $pagination.= “$counter“;

    }

    $pagination.= “..”;

    $pagination.= “$lpm1“;

    $pagination.= “$lastpage“;

    }

    else

    {

    $pagination.= “1“;

    $pagination.= “2“;

    $pagination.= “..”;

    for($counter = $lastpage – (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)

    {

    if($counter == $page)

    $pagination.= "$counter“;

    else

    $pagination.= “$counter“;

    }

    }

    }

    if($page < $counter – 1)

    $pagination.= "Next »“;

    else

    $pagination.= “Next »“;

    $pagination.= “”;

    }

    if(isset($_POST[‘pageId’]) && !empty($_POST[‘pageId’]))

    {

    $id=$_POST[‘pageId’];

    }

    else

    {

    $id=’0′;

    }

    $query=”select * from jobs order by date desc

    limit “.mysqli_real_escape_string($connection,$start).”,$recordsPerPage”;

    //echo “$query”;

    $res = mysqli_query($connection,$query);

    $count = mysqli_num_rows($res);

    $HTML=”;

    if($count > 0)

    {

    while($rows=mysqli_fetch_array($res))

    {

    echo “”;

    echo “”.$rows[‘position’].””;

    echo “Experience:“.$rows[‘experience’].””;

    echo “Location:“.$rows[‘location’].””;

    echo “”;

    echo “”;

    }

    }

    else

    {

    $HTML=’No Data Found’;

    }

    echo $HTML;

    echo $pagination;

    ?>

  14. Ashu says:

    Sir,

    Ajax part of code is not working. Tell what to change for it.

  15. Vishnu Priya says:

    Glad to see the article,as its very helpful to me and i got clear information on advanced version of pagination.php developers can have more information on this in php projects

  16. Sebastian Herrera says:

    Im receiving this error: “Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:Program Files (x86)ZendApache2htdocsokasloadData.php on line 102 No Data Found”. Weird because its the same query I have in line 4 and it works.

    i use a comment to check if the query in line 4 works and results were displayed, despite the fact that pagination appear but didnt work. any help appreciated. brgds!

  17. ncknmk says:

    your code is not working found many errors… its to bad code
    fuck

  18. kundan Singh says:

    thanks for the tutorial. It is woking fine…love your mailing list.

  19. Nando says:

    Hi phpgang, it’s a nice tutorial. I was able to use it on my codes, but when I click the next page, it loads to blank page. I know it must be somethign to do with the AJAX. I tried to change the URL as well as the class and id, but when it loads, it goes to blank page. Help, please? Thanks!

  20. ជា ផានិត says:

    Hi, it works find, but when i refresh page it always return to page first page. I want to know how to keep current page when reloaded

  21. Prabhjot kaur says:

    i want to download this script ….but i cnt
    i subscribed my id bt i cnt ..rply asap

  22. Prabhjot kaur says:

    cn u mail me this comtent?pls

  23. anant says:

    hello sir i want to search like google in php

  24. Kannav Manchanda says:

    i have tried implementing your live search tutorial. but i am having a little trouble. the trouble is that, the result div is displaying bydefault whenever the page is opened and goes when i click anywhere else on the page. please help!! 🙂

  25. Sameer Quraishi says:

    Assalam Alikum Huzoor brother,

    How to use below command with where condition,

    $query=”select post,postlink from pagination order by id asc

    limit “.mysqli_real_escape_string($connection,$start).”,$recordsPerPage”;

    as i have drop down list with dependency serach of items.

    my code as follow.

    else {

    $sql = “SELECT * FROM “.$SETTINGS[“city_table”].” where list_id>0″.$search_string.$search_city;

    }

  26. Ashish Singh says:

    In your logic you have used:

    if ($lastpage 5 + ($adjacents * 2)) //enough pages to hide some
    {

    …..
    }

    Why do you need the elseif part, just use else ?

  27. Dion Fitzgerald says:

    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:xampphtdocspage.php on line 84

    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:xampphtdocspage.php on line 180

    No Data Found

  28. Tony Korologos says:

    I’m trying this for a photo gallery so I’ve set the number of results to 1. How can I change the code to ONLY show <>?

  29. jason bourne says:

    Your code is mixture of mysql and mysqli functions.

  30. AsusAuditore says:

    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:AppServwwwcocloadData.php on line 6

    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:AppServwwwcocloadData.php on line 102
    No Data Found

  31. AsusAuditore says:

    Hello
    nice tutorial, …

    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:AppServwwwcocloadData.php on line 6

    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:AppServwwwcocloadData.php on line 101
    No Data Found

    Help x.x

  32. cyberking says:

    banchod

  33. Bobic says:

    I don’t usually care to leave a comment but dis was one hell of Tutorial dat was so irresistible. First: Whoever made it is a very generous person with an open heart. Kudos

  34. Patricia says:

    Hello I am implementing it for a shopping cart and it works, only the detail is that it does not work with a search engine, that is, with a form that I have and send the data by post to the same index, any proposal?

    • Patricia says:

      Sorry. this is my code for search

      if($_POST){

      $busqueda = trim($_POST[‘buscar’]);

      $entero = 0;

      if (empty($busqueda)){
      $products = $con->query(“select * from alma where CANTIDAD = ‘1’ “);
      }else{
      $products = $con->query(“select * from alma where (SERVICE_TAG LIKE’%$busqueda%’ OR MARCA LIKE’%$busqueda%’ OR DESCRIPCION LIKE’%$busqueda%’
      OR ARTICULO LIKE’%$busqueda%’ OR BARRAS LIKE’%$busqueda%’ OR MODELO LIKE’%$busqueda%’ OR SERIE LIKE’%$busqueda%’ OR MARCA LIKE’%$busqueda%’
      OR PROVEEDOR LIKE’%$busqueda%’ OR RESPONZABLE LIKE’%$busqueda%’ OR FACTURA LIKE’%$busqueda%’ OR OC LIKE’%$busqueda%’
      OR STATUS LIKE’%$busqueda%’ OR DESTINO LIKE’%$busqueda%’ OR UBICACION LIKE’%$busqueda%’ OR OBSERVACIONES LIKE’%$busqueda%’) AND CANTIDAD = ‘1’ “);
      unset($_POST[‘buscar’]);
      }

      }

  35. Coevote says:

    Hello Your PHP Pagination demo not Working . May be issue of database connectivity .

Leave a Reply

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