9lessons programming blog
Loading Search
9lessons Bring Friends
Wall Script
Follow Me:
Wednesday, September 9, 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

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
Comments
{ 144 comments }
Irmantas said...

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

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

Should be changed to

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

hacker2oo2 said...

looks good, nice tutorial

Lucifix said...

Great job!

There is one mistake in this tutorial:

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

should be:
var pageNum = this.id;


Thanks alot!

Lucifix said...

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

zarpio said...

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

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

Thanks

Anonymous said...

Very good stuff. Thanks a lot.

Miquel said...

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

Anonymous said...

i like that good job man !

w3 said...

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

Anonymous said...

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

Tim said...

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

Anonymous said...

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

could you share yout idea or any materials with me

regards
areef

Anonymous said...

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!

Its me said...

How could I add preview and next links?

Thanks for this great script.

Anonymous said...

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

Anonymous said...

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

Anonymous said...

how about with large data and lots page..

de4th said...

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!

Srinivas Tamada said...

@de4th

Thank you. It's helpfull

Anonymous said...

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

de4th said...

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

Srinivas Tamada said...

Thanks De4th

http://pastebin.com/f44dce6d

Anonymous said...

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

LuXo said...

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

test-test-test said...

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

Srinivas Tamada said...

@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);

Jamie H said...

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

Anonymous said...

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

Mike Brick said...

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

Plan-1130 said...

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 :)

Anonymous said...

$per_page=1;
its not show 1 id show only other

Anonymous said...

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

Umar Draz said...

Hi,

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

Plz check this.

Regards,

Umar

Kev said...

How to replace

1 2 3 4 5

With

1 2 .. 5

Please tell me

Kyle said...

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.

tarek said...

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

Anonymous said...

Thank you for nice tutorial.

Latest Gadget said...

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

JamaicaMan said...

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.

Anonymous said...

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

Anonymous said...

Good tutorial,

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

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

Anonymous said...

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

ClaudioBrother said...

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";

Anonymous said...

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.

Ricardo Pacheco said...

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.

Daniel said...

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 (:

tuub said...

Hi,

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

Regards,
Tuub

Download Viral Marketing Tools said...

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

tetrix said...

Upper case for SQL statements ffs!

Anonymous said...

Lucifix, you're a moron

Anonymous said...

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

Justin said...

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

Zbigniew said...

It's working. Thanks !

Web Design Company said...

Nice website :)

Anonymous said...

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

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

Thanks

Norant said...

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ú.

Anonymous said...

For animation, you can use jCarousel :-)

j hunk said...

Awesome stuff

Alternative Rush™ said...

thank you again for your hard work. appreciate.

vps hosting said...

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.

varun s said...

Nice tutorial Thanks.............................

phptechie said...

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

aparajayah said...

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

Suraj said...

There are no js files in download.

Admin said...

Very nice tutorial. Keep posting Sir.

:D

Web design Chennai said...

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

kassy said...

Cool Code , tutorial and thank .

http://www.kasanee.com

My little web

kassy :) :)

Social Networking Website Design said...

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

Paritus said...

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

Anonymous said...

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


Thank's a lot!

Anonymous said...

thanx a lot

a.jenk said...

thnkk you very much....^^

Anonymous said...

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

Rajinder said...

Hi,

Great Tutorial!

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


Thanks!

Rajinder said...

Hi,

Nice Article!

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

Thanks!

africa models said...

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

Anonymous said...

Gracias Gracias :)

gps said...

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

Jonas said...

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?

Mona said...

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

Syed Mohammed Ahmed said...

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

Anonymous said...

AWESUM DUDE..HATS OFF

aathira said...

Thanks..........

Anonymous said...

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

Anonymous said...

Multiple pagination?

1 2 3 4 5 6 7

and

1 2 3 4

etc

Suhasini said...

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

Anonymous said...

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 ?

Anonymous said...

thanks....

Shiva Prasad said...

It is highly informative for the page nation using PHP

Anonymous said...

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

Ab said...

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

Ab said...

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

alia said...

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

Anonymous said...

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

JSCR Inc said...

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

Madan Ray said...

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

Madan Ray said...

It's nice

Madan Ray said...

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

Jayps said...

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

NILESH KUMAR NAMDEV said...

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

i proud that you are Indian


may u live long. :)

NILESH KUMAR NAMDEV said...

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

i proud that you are Indian


may u live long. :)

akshay bhardwaj said...

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

sharad said...

good article

Anonymous said...

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?

Anonymous said...

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

Anonymous said...

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.

Origi Tuts said...

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

Mohammad Raihan Mazumder said...

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

Kian It said...

ThanX

sieuthigps.com said...

Thank!!

Anonymous said...

pagination with where clause not work so give solution for that

Trish said...

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

TorretO said...

its superb and thanks alot.. (:

web design company said...

Nice post,thanks for sharing the informative post.

Website Developement said...

Good Design and development this site

gps said...

Nice post. Thank Admin

Blair craig said...

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

Anonymous said...

Nice post. Thank Admin

Prakash's said...

It's working Good. Thanks So much!

Anonymous said...

Nice code.... Thanks a lot...

Anonymous said...

best bro

thinker said...

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

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

plz tel me....

Rodrigo Marques said...

How to replace

1 2 3 4 5

With

1 2 .. 5

Please tell me

cliknfind said...

nice tutorial . thanks

Anonymous said...

great tutorial. How can i limit the numbers?

1 2 3 4 5

With

1 2 .. 5

thanks!

Pgp said...

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

Vishal said...

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

Cheers !

Cybermales said...

Top Tutorial for begginer

Shahid Kayani said...

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

Anonymous said...

Thanks so much it very usefull

joomkb said...

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

Iwan said...

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

Anonymous said...

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

balu said...

How can we add search query in it...

Anonymous said...

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

MEHDI BOUSSARHANE said...

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

Ranveer Swami said...

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

ino Giot said...

thank uuuuuuu!!!!!!!!!!!!

Bhushan Dod said...

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

Anonymous said...

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.

Anonymous said...

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

Anonymous said...

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"

Anonymous said...

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

tomi said...

Good job Srinivas! Does it work with SQL WHERE ?

Post a Comment