Pagination with jQuery, MySQL and PHP.
Wall Script
MailxEngine
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. looks good, nice tutorial

    ReplyDelete
  3. Great job!

    There is one mistake in this tutorial:

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

    should be:
    var pageNum = this.id;


    Thanks alot!

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

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

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

    Thanks

    ReplyDelete
  6. Very good stuff. Thanks a lot.

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

    ReplyDelete
  8. i like that good job man !

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

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

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

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

    could you share yout idea or any materials with me

    regards
    areef

    ReplyDelete
  13. 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
  14. How could I add preview and next links?

    Thanks for this great script.

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

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

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

    ReplyDelete
  18. 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
  19. @de4th:
    example code will greatfull for the beginner..

    ReplyDelete
  20. 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
  21. 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
  22. 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
  23. Any solution for mysql_fetch_array(): supplied argument is not a valid MySQL result resource in??

    ReplyDelete
  24. @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
  25. 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
  26. 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
  27. what is the paging.js file for? I do not see it referenced for this tutorial?

    ReplyDelete
  28. 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
  29. $per_page=1;
    its not show 1 id show only other

    ReplyDelete
  30. 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
  31. Hi,

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

    Plz check this.

    Regards,

    Umar

    ReplyDelete
  32. How to replace

    1 2 3 4 5

    With

    1 2 .. 5

    Please tell me

    ReplyDelete
  33. 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
  34. 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
  35. Thank you for nice tutorial.

    ReplyDelete
  36. 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
  37. 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
  38. Code doesn't pass w3 validation. You are starting an "id" attribute with a number.

    ReplyDelete
  39. Good tutorial,

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

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

    ReplyDelete
  40. 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
  41. 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
  42. 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
  43. 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
  44. 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
  45. Hi,

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

    Regards,
    Tuub

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

    ReplyDelete
  47. Upper case for SQL statements ffs!

    ReplyDelete
  48. Lucifix, you're a moron

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

    ReplyDelete
  50. 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
  51. Please provide correct "Download Script" as in "Live Preview".

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

    Thanks

    ReplyDelete
  52. 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
  53. For animation, you can use jCarousel :-)

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

    ReplyDelete
  55. 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
  56. Nice tutorial Thanks.............................

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

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

    ReplyDelete
  59. There are no js files in download.

    ReplyDelete
  60. Very nice tutorial. Keep posting Sir.

    :D

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

    ReplyDelete
  62. Cool Code , tutorial and thank .

    http://www.kasanee.com

    My little web

    kassy :) :)

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

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

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


    Thank's a lot!

    ReplyDelete
  66. 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
  67. Hi,

    Great Tutorial!

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


    Thanks!

    ReplyDelete
  68. Hi,

    Nice Article!

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

    Thanks!

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

    ReplyDelete
  70. Gracias Gracias :)

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

    ReplyDelete
  72. 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
  73. 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
  74. 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
  75. AWESUM DUDE..HATS OFF

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

    ReplyDelete
  77. Multiple pagination?

    1 2 3 4 5 6 7

    and

    1 2 3 4

    etc

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

    ReplyDelete
  79. 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
  80. It is highly informative for the page nation using PHP

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

    ReplyDelete
  82. 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
  83. Undefined index: page
    and
    Warning: mysql_fetch_array() expects parameter 1 to be resource, string given
    two error show how to solve it

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

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

    ReplyDelete
  86. 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
  87. 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
  88. 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
  89. 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
  90. good Shrinivas ......
    you are a very genius engineer

    i proud that you are Indian


    may u live long. :)

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

    i proud that you are Indian


    may u live long. :)

    ReplyDelete
  92. 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
  93. 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
  94. $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
  95. 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
  96. It gives me that error:
    Undefined variable: page in C:\xampp\htdocs\admin\scripts\pagination_display.php on line 9

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

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

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

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

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

    ReplyDelete
  102. 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
  103. Nice post. Thank Admin

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

    ReplyDelete
  105. Nice code.... Thanks a lot...

    ReplyDelete
  106. 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
  107. How to replace

    1 2 3 4 5

    With

    1 2 .. 5

    Please tell me

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

    1 2 3 4 5

    With

    1 2 .. 5

    thanks!

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

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

    Cheers !

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

    ReplyDelete
  112. Thanks so much it very usefull

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

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

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

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

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

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

    ReplyDelete
  119. 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
  120. 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
  121. 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
  122. 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
  123. 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
  124. What if there are too many data? hows the page number behavior? Thank you :)

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

    ReplyDelete
  126. 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
  127. thank you for this tutorial.
    accents do not display correctly. How can we solve this problem?
    Thank you

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

    ReplyDelete
  129. 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
  130. Привет тут... надо-бы новую версию скрипта добавить !!!

    ReplyDelete
  131. 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