Pagination with jQuery, MySQL and PHP.
Wall Script
Follow Me:
Wednesday, September 09, 2009

Pagination with jQuery, MySQL and PHP.

I received lot of requests from my readers that asked to me how to implement Pagination with jQuery, PHP and MySQL. so I had developed a simple tutorial. It's looks big but very simple script. Take a look at this live demo New updated post Pagination with Jquery, PHP , Ajax and MySQL.

Pagination with jQuery, MySQL and PHP.

The tutorial contains three PHP files and two js files includes jQuery plugin.

-config.php (Database Configuration)
-pagination.php
-pagination_data.php
-jquery.js
-jquery_pagination.js

Download Script     Live Preview

Database Table
CREATE TABLE messages
(
msg_id INT PRIMARY KEY AUTO_INCREMENT,
message TEXT
);



jquery_pagination.js
Contains javascript this script works like a data controller.
$(document).ready(function()
{
//Display Loading Image
function Display_Load()
{
$("#loading").fadeIn(900,0);
$("#loading").html("<img src="bigLoader.gif" />");
}
//Hide Loading Image
function Hide_Load()
{
$("#loading").fadeOut('slow');
};

//Default Starting Page Results
$("#pagination li:first")
.css({'color' : '#FF0084'}).css({'border' : 'none'});
Display_Load();
$("#content").load("pagination_data.php?page=1", Hide_Load());

//Pagination Click
$("#pagination li").click(function(){
Display_Load();
//CSS Styles
$("#pagination li")
.css({'border' : 'solid #dddddd 1px'})
.css({'color' : '#0063DC'});

$(this)
.css({'color' : '#FF0084'})
.css({'border' : 'none'});

//Loading Data
var pageNum = this.id;
$("#content").load("pagination_data.php?page=" + pageNum, Hide_Load());
});

});

config.php
You have to change hostname, username, password and databasename.
<?php
$mysql_hostname = "localhost";
$mysql_user = "username";
$mysql_password = "password";
$mysql_database = "database";
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password)
or die("Opps some thing went wrong");
mysql_select_db($mysql_database, $bd)
or die("Opps some thing went wrong");
?>

pagination.php
User interface page.
<?php
include('config.php');
$per_page = 9;

//Calculating no of pages
$sql = "select * from messages";
$result = mysql_query($sql);
$count = mysql_num_rows($result);
$pages = ceil($count/$per_page)
?>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/
libs/jquery/1.3.0/jquery.min.js
"></script>
<script type="text/javascript" src="jquery_pagination.js"></script>

<div id="loading" ></div>
<div id="content" ></div>
<ul id="pagination">
<?php
//Pagination Numbers
for($i=1; $i<=$pages; $i++)
{
echo '<li id="'.$i.'">'.$i.'</li>';
}
?>
</ul>

pagination_data.php
Simple php script display data from the messages table.
<?php
include('config.php');
$per_page = 9;
if($_GET)
{
$page=$_GET['page'];
}

$start = ($page-1)*$per_page;
$sql = "select * from messages order by msg_id limit $start,$per_page";
$result = mysql_query($sql);
?>
<table width="800px">
<?php
while($row = mysql_fetch_array($result))
{
$msg_id=$row['msg_id'];
$message=$row['message'];
?>
<tr>
<td><?php echo $msg_id; ?></td>
<td><?php echo $message; ?></td>
</tr>
<?php
}
?>
</table>

CSS Code
CSS code for page numbers.
#loading
{
width: 100%;
position: absolute;
}
li
{
list-style: none;
float: left;
margin-right: 16px;
padding:5px;
border:solid 1px #dddddd;
color:#0063DC;
}
li:hover
{
color:#FF0084;
cursor: pointer;
}


Related Articles
Latest 20 articles on 9lessons
Was this article helpful?
Thanks! Your feedback helps us to improve 9lessons.info


154 comments:

  1. Why in page 6 there are no messages? I think line:

    $start = ($page-1)*10;

    Should be changed to

    $start = ($page-1)*$per_page;

    ReplyDelete
  2. Great job!

    There is one mistake in this tutorial:

    this:
    var pageNum = $ ( this ) . id;

    should be:
    var pageNum = this.id;


    Thanks alot!

    ReplyDelete
  3. Oh one more thing, is it possible to display new page with fadeIn effect?

    ReplyDelete
  4. Please provide correct "Download Script" as in "Live Preview".

    Tutorial as shown above isn't matching with "Download Script(pagination.zip)"

    Thanks

    ReplyDelete
  5. Very good stuff. Thanks a lot.

    ReplyDelete
  6. Thanks!!... just what I needed ...
    Greetings from Perú

    ReplyDelete
  7. i like that good job man !

    ReplyDelete
  8. very useful script. I am translate your tutorial for my blog in Russian
    http://w3forme.ru/всплывающие-со…ния-об-ошибках/
    added link in your blog

    ReplyDelete
  9. oops)
    http://w3forme.ru/разбиениенумерация-страниц/

    ReplyDelete
  10. When i run it i doesn't work. I recieve a mysql_fetch_array(): supplied argument is not a valid error

    ReplyDelete
  11. i want to do pagin with jsp, is it possible.

    could you share yout idea or any materials with me

    regards
    areef

    ReplyDelete
  12. Nice, i like it, i managed to include this into codeigniter project using the query string feature ON with $_GET variables and on a search form. For example you have form with one input text and one select box, you type in something, select feature from the select box, click search and you get the first 10 results with your pagination and i put the $_POST variables within hidden inputs, so when i make ajax calls to the server i am using the hidden variables to pass to ajax and it came out really good. Thank you very much for your idea though!

    ReplyDelete
  13. How could I add preview and next links?

    Thanks for this great script.

    ReplyDelete
  14. Is there any way preview and next links can be added with disabled links as well? Or is it too hard to implement?

    ReplyDelete
  15. yeah.. first page, next page or last page button.. is there any solution..

    ReplyDelete
  16. how about with large data and lots page..

    ReplyDelete
  17. Hello, I have a little hack for this.
    Replace the for loop with your own links.

    For the 1st page link just add '1',
    for the last add the var $pages.
    Now for Prev and Next you have to add 2 hidden inputs, one for currentPage and one for lastPage.

    The next link will be currentPage+1 and the prev currentPage-1.
    Then write a code to disable the next and the last links in the last page, the same for the first page and then you are ready!

    I hope this helps you out!

    ReplyDelete
  18. @de4th:
    example code will greatfull for the beginner..

    ReplyDelete
  19. I can't post code to comments.
    I pasted it to pastebin here:
    http://pastebin.com/f44dce6d

    Better to be an addition to this tutorial.

    When I find some free time I'll create a blog for that stuff...

    ReplyDelete
  20. I got following error


    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/filmsadd/public_html/pagination_data.php on line 17

    ReplyDelete
  21. Thanks .. is a great job....

    just i've a question..

    How can i do a list like:

    First Previous 1 2 3 4 5 ..... Next Last

    .. The list show all registers of db.


    Thanks for help.!

    ReplyDelete
  22. Any solution for mysql_fetch_array(): supplied argument is not a valid MySQL result resource in??

    ReplyDelete
  23. @test-test

    mysql_fetch_array() supplied argumenet in not a valid MySQL result?

    You have to check the SQL statement.

    eg: $sql="Sql Statement";
    $row=mysql_fetch_array($sql);

    ReplyDelete
  24. Really great tutorials dude, they have helped me massively :)

    So as asked before any ideas how we could add "First Previous 1 2 3 4 5 ..... Next Last" to this already great little web app?

    Cheers Jamie

    ReplyDelete
  25. Actually i am calling a php file through ajax in a div when i click on link.I want to use paging in particular div with First Previous 1 2 3 4 5 ..... Next Last.
    Is this possible from this script.

    Actually i am new in php.Please help me its very urgent.

    Thanks in advance

    ReplyDelete
  26. what is the paging.js file for? I do not see it referenced for this tutorial?

    ReplyDelete
  27. Replace:

    //Calculating no of pages
    $sql = "select * from messages";
    $result = mysql_query($sql);
    $count = mysql_num_rows($result);
    $pages = ceil($count/$per_page);

    with:

    //Calculating no of pages
    $sql = "select count(*) from messages";
    $result = mysql_query($sql);
    $count = mysql_fetch_row($result);
    $pages = ceil($count[0]/$per_page);

    because:
    Letting MySQL do the counting is SIGNIFICANTLY faster, as it only counts the entries rather than parsing all data, AND only returns a very small block of data :)

    ReplyDelete
  28. $per_page=1;
    its not show 1 id show only other

    ReplyDelete
  29. Great tutorial. Thank you.
    I need one more thing. Can you tell me how can we use next prev buttons instead of page numbers.

    ReplyDelete
  30. Hi,

    There is a bug. Whenever i refresh the page. The page back to page 1.

    Plz check this.

    Regards,

    Umar

    ReplyDelete
  31. How to replace

    1 2 3 4 5

    With

    1 2 .. 5

    Please tell me

    ReplyDelete
  32. I'm using this solution (though quite modified) on one of my sites. In regards to a previous question, I am paging through 55,000+ records (2,000+ pages) and not having any issues. So long as the data PER PAGE is small enough to not eat all of PHP's memory, you'll be fine.

    ReplyDelete
  33. Does anyone have any suggestions on how to make this degrade gracefully (i.e., still work to some degree when javascript is disabled)?

    tarek
    technotarek.com

    ReplyDelete
  34. Thank you for nice tutorial.

    ReplyDelete
  35. Mate must say thank to you the way you have explain the post. I am going to use this jquery example and then use in my office project

    ReplyDelete
  36. Hello Srinivas Tamada:
    Thanks for the script! As mentioned earlier this is a very good tool. As also mentioned earlier it would be of great value if you could add or show us how to add the NEXT and PREVIOUS prompts.
    Nothing fancy, just the ability to advance or retreat. I was able to write a successful PREVIOUS routine for this but I can’t find a way to find the maximum number of pages to implement a trap for the NEXT routine.
    Thanks in advance for support.

    ReplyDelete
  37. Code doesn't pass w3 validation. You are starting an "id" attribute with a number.

    ReplyDelete
  38. Good tutorial,

    Never use digits starting and id like id="1" in html,

    you have to add a prefix, like id="page1"

    ReplyDelete
  39. nice tutorial but when i executed only the page number is displaying and when i hover the numbers i ddont see any link in the browser

    ReplyDelete
  40. Great tutorial!

    But there is an error that blocks the results.

    Simply replace this code:
    limit $start, $per_page";

    For this code:
    limit $per_page, $start";

    ReplyDelete
  41. Can someone give an example of how we can handle multiple results in pagination. like in example, what if we have 100 pages. how can i only show the first 10 results and the last, and when i click on page 10 seeing the next 10 and the last and so on.

    ReplyDelete
  42. How can I have that same system using only the links "next" and "previous" in this style ( ), ie, no numbers, and the navigation function in the same way the tutorial?

    question translated by google translator, excuse any gramatial errors , hehe.

    ReplyDelete
  43. Thanks but.... There would be alot of numbers if i manage thousands of pages can You make a TUTORIAL on how to make pagination like:

    [1] 2 3 4 5 ... 82

    PLEASE!!!!
    using php and css.... PLEASE Srinivas Tamada, you are a great programmer. thanks in advance (:

    ReplyDelete
  44. Hi,

    Thanks!
    I just made a small modification in De4th's code: http://pastebin.com/9RFv0ye7

    Regards,
    Tuub

    ReplyDelete
  45. Thanks, saved me a few minutes with this one :)

    ReplyDelete
  46. Upper case for SQL statements ffs!

    ReplyDelete
  47. Lucifix, you're a moron

    ReplyDelete
  48. Can any one please give me idia how to create User interface with this query.

    ReplyDelete
  49. I am trying to use tomahawk t:dataScroller for pagination on my jsf page. Data is rendered properly for the first page but whenever I try to click any button to go to next page, java script error is thrown as below

    form is undefined
    var oldTarget = form.target;

    Any solution to this will be quite helpful.

    I am using tomahawk12-1.1.9 lib with JSF2.0

    ReplyDelete
  50. Please provide correct "Download Script" as in "Live Preview".

    Tutorial as shown above isn't matching with "Download Script(pagination.zip)"

    Thanks

    ReplyDelete
  51. very nice script, with all, so use PDO for PHP and use bind of variables style JAVA prepared statements.
    Thank you so match from Perú.

    ReplyDelete
  52. For animation, you can use jCarousel :-)

    ReplyDelete
  53. thank you again for your hard work. appreciate.

    ReplyDelete
  54. Thanks for publishing this article. You helped me to open up my eyes. Thanks for publishing and I'm looking forward to your new posts.

    ReplyDelete
  55. Nice tutorial Thanks.............................

    ReplyDelete
  56. this coding really helps me a lots...
    please update more and new posts

    ReplyDelete
  57. very useful tutorial...
    awesome thanks for sharing this post...

    ReplyDelete
  58. There are no js files in download.

    ReplyDelete
  59. Very nice tutorial. Keep posting Sir.

    :D

    ReplyDelete
  60. Thanks for Awesome tutorial. i hope this will really help so many people to learn something new..

    ReplyDelete
  61. Cool Code , tutorial and thank .

    http://www.kasanee.com

    My little web

    kassy :) :)

    ReplyDelete
  62. This is great and I appreciate it that you got shared so handy and useful post.

    ReplyDelete
  63. jQuery, MySQL and PHP.
    I was doing research on this topic. was super.

    ReplyDelete
  64. One of the BEST Tutorial I've ever seen..


    Thank's a lot!

    ReplyDelete
  65. Hi ,
    I have tried to use that code but it shows an empty page with 1,2 but no data are showing..why !!

    Also do I need to create CSS file for the last code part !!

    Thanks

    ReplyDelete
  66. Hi,

    Great Tutorial!

    Note:Please change the Table name in Query on line16(i.e message to messages) in "pagination_data.php".


    Thanks!

    ReplyDelete
  67. Hi,

    Nice Article!

    Please change Table name(i.e message to messages)on line 16 in pagination_data.php

    Thanks!

    ReplyDelete
  68. actually sincerely speaking this is the best php tutorials i've ever stumbled upon i like it.
    9lessons.info the best!

    ReplyDelete
  69. Gracias Gracias :)

    ReplyDelete
  70. Good plugin!
    But how can I use it with difference anchor markup (better in SEO). Can you show me?
    Thanks in advance!

    ReplyDelete
  71. Hi! I am using qTip to view image preview in the list. It works on first page of results, but on page 2 and beyond the tooltip is no longer applying.

    Any ideas about this?

    ReplyDelete
  72. Good effort.. Great Article. Have some more pagination at http://websmine.com/web-development/jquery/jpaginate-a-fantasy-jquery-pagination-plugin/
    Thanks for sharing...!!!

    ReplyDelete
  73. guys i am really stuck in this tried couples of ways but didnt get it right please help.My pagiantion is working fine and great untill i specfies WHERE CLAUSE in it.I am trying to have a text field so that if a user enter the interger for class search e.g 8.Pagination search records WHERE class is 8 BUT it show me record only on the first page but when i turn to second page it show a empty page.Please Help me

    ReplyDelete
  74. AWESUM DUDE..HATS OFF

    ReplyDelete
  75. for a reason i cant download this...and it does not work wen i copy and paste script.

    ReplyDelete
  76. Multiple pagination?

    1 2 3 4 5 6 7

    and

    1 2 3 4

    etc

    ReplyDelete
  77. Well this is so very helpful and i really needed guidance on this. Thank you .

    ReplyDelete
  78. Hey this is good.....but i want to take data from 2 tables.............is this code is suitable to take data from two database tables ?

    ReplyDelete
  79. It is highly informative for the page nation using PHP

    ReplyDelete
  80. the reload gif ..how to make it display at the center of the tables?

    ReplyDelete
  81. Undefined index: page
    and
    Warning: mysql_fetch_array() expects parameter 1 to be resource, string given
    two type error show how to slove it

    ReplyDelete
  82. Undefined index: page
    and
    Warning: mysql_fetch_array() expects parameter 1 to be resource, string given
    two error show how to solve it

    ReplyDelete
  83. if possible can you tell us how to do pagination with
    1 2 3 .... 15 :)))))))))))))

    ReplyDelete
  84. file:
    -jquery.js
    -jquery_pagination.js
    are missing..!!

    ReplyDelete
  85. Just a Question, some of your php demos dont seem to be working, I was kindof disappointed since i was searching through here for inspiration

    ReplyDelete
  86. Yes it's nice but can you one more thing to this tutorial that if i move 1 page to 2 and 2 to 3 and reload the page it should stay at page no 3 not to go on default page.

    Madan Kumar

    ReplyDelete
  87. Yest it's nice, when i move page 1 to 2 or 3 page and reloading the page it's going back to default page......it's should not be

    ReplyDelete
  88. Does the pagination_data must be on a separate page? I'd like to embed the code on the page with the jquery. can I refer to a php function in: $("#content").load("pagination_data.php?page=" + pageNum, Hide_Load());?

    thanks

    ReplyDelete
  89. good Shrinivas ......
    you are a very genius engineer

    i proud that you are Indian


    may u live long. :)

    ReplyDelete
  90. good Shrinivas ......
    you are a very genius engineer

    i proud that you are Indian


    may u live long. :)

    ReplyDelete
  91. Notice: Undefined variable: page in C:\xampp\htdocs\self\pagination_data.php on line 8

    Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\self\pagination_data.php on line 14

    Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\self\productlist.php on line

    ReplyDelete
  92. Can anyone help with variables in the mysql statement? For example: instead of just

    $query_pag_data = "SELECT * FROM products LIMIT $start, $per_page";

    I would like to use

    $query_pag_data = "SELECT * FROM products WHERE category='$category' LIMIT $start, $per_page";

    But because the url does not change its just doesn't work, can anyone offer a solution?

    ReplyDelete
  93. $per_page = 9;
    if(isset($_POST['page']))
    {
    $page=$_POST['page'];
    }

    $start = ($page-1)*$per_page;

    $sql = "SELECT * FROM video ORDER BY id DESC LIMIT $start, $per_page";



    Error: Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\jquery\index_video.php on line 21

    If Raplace $start=0, will run good but don't pagination

    Please help me!!!

    ReplyDelete
  94. is it possible to paginate along with filter / search box result on the same file
    ie. i want pagination to be done for search result also for values fetched from db in mysql.

    ReplyDelete
  95. It gives me that error:
    Undefined variable: page in C:\xampp\htdocs\admin\scripts\pagination_display.php on line 9

    ReplyDelete
  96. thanx dude.. really difficult for me to understand it. :)

    ReplyDelete
  97. pagination with where clause not work so give solution for that

    ReplyDelete
  98. It's not working for me, please help :(

    ReplyDelete
  99. its superb and thanks alot.. (:

    ReplyDelete
  100. Nice post,thanks for sharing the informative post.

    ReplyDelete
  101. Valuable advice and accomplished architecture you got here! I would like to acknowledge you for administration your thoughts into the being you post!! Thumbs up

    ReplyDelete
  102. Nice post. Thank Admin

    ReplyDelete
  103. It's working Good. Thanks So much!

    ReplyDelete
  104. Nice code.... Thanks a lot...

    ReplyDelete
  105. if($_GET)
    {
    $page=$_GET['page'];
    }

    Where did i get 'page' in db
    and i can't able to unserstand that 'page'

    plz tel me....

    ReplyDelete
  106. How to replace

    1 2 3 4 5

    With

    1 2 .. 5

    Please tell me

    ReplyDelete
  107. great tutorial. How can i limit the numbers?

    1 2 3 4 5

    With

    1 2 .. 5

    thanks!

    ReplyDelete
  108. Hello,
    I just wanted to take a minute to tell you that you have a great site! Keep up the good work.

    ReplyDelete
  109. Thanks man. Exactly what I was looking for my matrimonial site. It was very helpful.

    Cheers !

    ReplyDelete
  110. thanks , that'ts great ... keep posting . it was really helpfullllllll .

    ReplyDelete
  111. Thanks so much it very usefull

    ReplyDelete
  112. Wonderful job man. Thank you very much. Its very useful for us.

    ReplyDelete
  113. Why this isn't working on Chrome. May somebody give me a solution?

    ReplyDelete
  114. hey its wonderful, the missing is sortin which will make it perfect

    ReplyDelete
  115. How can we add search query in it...

    ReplyDelete
  116. just tell us what the js code to make the output 1 2 3 4 5
    With
    1 2 .. 5
    an thanks

    ReplyDelete
  117. good, but when there is lot of data...take lot of time to load
    thank you

    ReplyDelete
  118. i want a where clause in my query and when i go on the next page then $_POST value is null and next page record not display

    ReplyDelete
  119. Really ..this is very awesome side..I'm new in PHP ..but really whatever doubt i have i a=had cleared it from here..thank u so much

    ReplyDelete
  120. There's no point using this if you want google to index your content because it's completely invisible to google, all it sees are the header and footer of your pages.

    ReplyDelete
  121. Hi I always visit your blog and get some resourses here...But now im having a big problem on this pagination..I used it but i noticed that when ever you reload the page it goes back to page 1 instead to the current page recently selected...I i will implement this ofcourse the user might get frustrated repeating the same method done....so please give me a help on this...

    ReplyDelete
  122. please can u help me do the type of pagination that facebook use in their comments for their mobile site. it has one link " previous cooments"

    ReplyDelete
  123. What if there are too many data? hows the page number behavior? Thank you :)

    ReplyDelete
  124. Good job Srinivas! Does it work with SQL WHERE ?

    ReplyDelete
  125. Hi, The script works great in all browsers except IE(Internet Explorer). In IE I have to manually click the browser refresh button for new pagination page information to display. Anyone know how to fix this in IE?

    Thanks

    ReplyDelete
  126. thank you for this tutorial.
    accents do not display correctly. How can we solve this problem?
    Thank you

    ReplyDelete
  127. this is great. thank you very much. you save my life

    ReplyDelete
  128. This is a great tutorial i have ever seen so precise and simple no doubt at all. But the problem is that there is been too much concern enough to modify the script to include the next and previous links, if for nothing but for the sake of the beginners. All through the post every body seemed to care about that. Please do include it in the download zip file or at least do a little write up on how to do it along with rest of the code and thanks greatly for the tutorial,

    ReplyDelete
  129. Привет тут... надо-бы новую версию скрипта добавить !!!

    ReplyDelete
  130. Please Send php code.click a href and access id on next page and id not show on url how to create code That id not show in url bar.

    ReplyDelete

Make in India