December 23, 2013 8:30 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

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.

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

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

JQuery Required to handle AJAX functions:

loadData.php

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

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.

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:

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:
  • Mahbub Hossain

    Thanks bro

  • ugurpin

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

    • huzoorbux

      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.

  • ugurpin

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

    • huzoorbux

      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

        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)

        • Huzoor Bux

          Have you changed $count = 200; field value to 20?

  • Greg

    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

  • Greg

    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.

  • vivek

    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

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

      • vivek

        Glad to hear from you sir, i would really love to see such feature in your upcoming post. Thanks for reply.

  • satyendra

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

  • Avatar

    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?

    • huzoorbux

      Define empty value in $counter like this…

  • splen

    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.

  • rajendher

    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…………

    • huzoorbux

      Turn on errors reporting on loadData.php file then send me exact error which you are getting.

      • rajendher

        how to turn on .. actually i am new to php.

    • huzoorbux

      Write this line on top of the page

      • rajendher

        no sir, i am getting same message. The page cannot be displayed because an internal server error has occurred.

        • huzoorbux

          Without Error how can I help you try to show error are you using localhost this is the solution to show errors on localhost http://stackoverflow.com/questions/9862348/php-scripts-not-showing-any-error-on-localhost

          • rajendher

            ya… on the server side not localhost.

          • rajendher

            line number 19 sir

        • rajendher

          just now i have checked out basic pagination . that is working properly. that was awesome. and i am using the same table for advanced pagination. but not working….. please help me…

        • huzoorbux

          I can’t Help you until you show me errors.

          • rajendher

            i am sorry for the confusion. i have checked the code in online. there is no errors in the code. and i was testing it with a new table. now it is working very fine. anyway thanks for immediate reply. i would like say thanks for you and your website. for providing such scripts. it was great. thank you again.

  • rajendher

    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……

    • huzoorbux

      Use Responsive themes.

      • rajendher

        i want to display 10 results per page. how to do..???? give me example….. please

  • rajendher

    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

      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.

      • rajendher

        ok thank you………

  • Ashu

    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;

    ?>

  • Ashu

    Sir,

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

    • huzoorbux

      What error you are getting in it debug ajax in firebug and send me exact error.

  • Vishnu Priya

    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

  • Sebastian Herrera

    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!

    • huzoorbux

      Make sure that you have a valid connection link in 1st parameter.

      • Sebastian Herrera

        thanks for your reply!

  • ncknmk

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

  • kundan Singh

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

  • Nando

    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!

  • ជា ផានិត

    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

  • Prabhjot kaur

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

    • huzoorbux

      what email address you are using to download

  • Prabhjot kaur
    • huzoorbux

      Its working fine

  • Prabhjot kaur

    cn u mail me this comtent?pls

  • anant

    hello sir i want to search like google in php

  • Kannav Manchanda

    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!! 🙂

  • Sameer Quraishi

    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;

    }

  • Ashish Singh

    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 ?

  • Dion Fitzgerald

    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

  • Tony Korologos

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

  • jason bourne

    Your code is mixture of mysql and mysqli functions.

  • AsusAuditore

    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

  • AsusAuditore

    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

  • cyberking

    banchod