9lessons programming blog
Loading Search
9lessons Bring Friends
Wall Script
Follow Me:
Monday, October 25, 2010

Pagination with Jquery, PHP , Ajax and MySQL.

Long days back I had posted an article Pagination with jQuery, MySQL and PHP with out refreshing page. Ravi Tamada modified old script and added extra features like First, Previous, Next and Last buttons. Use it and enrich your web applications. Demo link

Pagination jquery, ajax, php and Mysql

Download Script     Live Demo

About Author
Ravi Tamada
Ravi Tamada
Designer Developer & Freelance
Chennai, INDIA
androidhive.info
Database
MySQL messages table contains two columns msg_id and message
CREATE TABLE messages
(
msg_id INT PRIMARY KEY AUTO_INCREMENT,
message VARCHAR(150)
);

JavaScript Code
This script works like a data controller.
<script type="text/javascript" src="http://ajax.googleapis.com/
ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function()
{

function loading_show()
{
$('#loading').html("<img src='images/loading.gif'/>").fadeIn('fast');
}

function loading_hide()
{
$('#loading').fadeOut();
}

function loadData(page)
{
loading_show();
$.ajax
({
type: "POST",
url: "load_data.php",
data: "page="+page,
success: function(msg)
{
$("#container").ajaxComplete(function(event, request, settings)
{
loading_hide();
$("#container").html(msg);
});
}
});
}
loadData(1); // For first time page load default results
$('#container .pagination li.active').live('click',function(){
var page = $(this).attr('p');
loadData(page);
});
});

</script>

load_data.php
Contains PHP coding. Displaying data from the messages table.
<?php
include('db.php');
if($_POST['page'])
{
$page = $_POST['page'];
$cur_page = $page;
$page -= 1;
$per_page = 15; // Per page records
$previous_btn = true;
$next_btn = true;
$first_btn = true;
$last_btn = true;
$start = $page * $per_page;
include"db.php";
$query_pag_data = "SELECT msg_id,message from messages LIMIT $start, $per_page";
$result_pag_data = mysqli_query($query_pag_data);
$msg = "";
while ($row = mysqli_fetch_array($result_pag_data,MYSQLI_ASSOC))
{
$htmlmsg=htmlentities($row['message']); //HTML entries filter
$msg .= "<li><b>" . $row['msg_id'] . "</b> " . $htmlmsg . "</li>";
}
$msg = "<div class='data'><ul>" . $msg . "</ul></div>"; // Content for Data
/* -----Total count--- */
$query_pag_num = "SELECT COUNT(*) AS count FROM messages"; // Total records
$result_pag_num = mysqli_query($query_pag_num);
$row = mysqli_fetch_array($result_pag_num,MYSQLI_ASSOC);
$count = $row['count'];
$no_of_paginations = ceil($count / $per_page);
/* -----Calculating the starting and endign values for the loop----- */

//Some Code. Available in download script

}
?>

db.php
Database configuration file, modify username, password and database values.
<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'username');
define('DB_PASSWORD', 'password');
define('DB_DATABASE', 'database');
$db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
?>
Comments
{ 217 comments }
Anonymous said...

Very nice....

Please write tutorial Codeigniter and Jquery...

Kathir said...

Great!

Anonymous said...

good one brother

Vincent said...

Nice ;)

Buzzknow said...

Its nice script :)

thanks

anand selvaraj said...

very niceeeeeeeeeeeee..............

anand selvaraj said...

very nice....this type of paging i expected thanks.

Trollskoggens said...

Hi, thank a lot for this!
hope you some day write some implementation with codeigniter!

greetings!

Srinivas Tamada said...

Note : New Subscribers

new email data updating every 6 hours

mehul said...

nice and like it very much.

dskanth said...

Thanks a ton Srinivas... working awesome...
You rock :)

Anonymous said...

nice script ;)

Anonymous said...

Thanks..It's Good

Anonymous said...

Thanks

Anonymous said...

Hi srinivas
Firstly Thank For this nice script. But ı can't download this script Though I Subscribed my mail. When ı dowload it a warning comes on the screen as "1 Email Pending Verification. Please active or Subscribe" but I've activated my mail. what is wrong I dont know?

Anonymous said...

For some reason this demo crash any website hosted in a basic Godaddy hosting.

Any fix for this?

Beben said...

its a great job...
thanks a lot ^^
salam bloglang \m/ :D

Tullio said...

Thank you it's nice, my social media is done but yours pagination i add.

Lori said...

Signup & active:
1.activated my mail

Download:
2.Sorry no email present subscribe below...

signup:
3.My Email is already subscribed to the mailing list of 9lessons.

Download again:
4.Sorry no email present subscribe below...

Download again,again and again:
5.Sorry no email present subscribe below...

help me?

Srinivas Tamada said...

@Lori

New subscriber emails updating every 12 hours

Lori said...

oic....thanks you so much...

seavolvox said...

Hi Ravi, sorry, but I've subscribed since this morning, and still cannot download it :S Need help!! :)

Norant said...

Very Nice, I implement this post using POO, PDO and MVC, Coming soon in my blog!!!
Thank you Ravi Tamada.

Ravi Tamada said...

@Norant

You are Welcome :)

row_fix said...

thanks for the nice tutorial.

Ravi, can you make another tutorial about how to apply pagination on search result.

MG said...

thanks for the nice script! I really love it, but it doesn't work on iOS Devices :-(
The navigation isn't clickable...

Anonymous said...

Cannot enter a page number with Firefox :(

web marketing melbourne said...

Brilliant posting you have done great job, it’s so great helpful for me. And I am looking forward of this post thank you.

Indian Girl said...

Hi,
The page numbers are flashing on mouseover and I am unable to see any changes on clicking them.

I am using Google Chrome, any idea what's wrong?

Thanks

Chris Brocklesby said...

Just in case anyone's having a problem with iOSX devices (iphone,ipads) you need add:

$('#pagination .pagination li.active').live('touchend',function(){
var page = $(this).attr('p');
loadData(page);
});

in the JavaScript for iOSX Support as these devices do not support 'click' ...

Great Script, Many Thanks...

Ravi Tamada said...

@Chris : Thanks for the Great tip!

santosh said...

Thanks for sharing such a nice stuff. Please share some more stuff of AJAX with php/mysql.

Thanks beforehand.

majid said...

man this is so cool i love your pagination tutorial, keep it coming.

Anonymous said...

THANKS

Srinivas Tamada said...

Subscribers emails updated..

Anonymous said...

thanks for this nice tutorial. a very big help to me. btw, how can I pass a parameter into the load_data.php script? e.g. search result

Anonymous said...

ur site is my first reference now..

Andrey24may said...

The Best Tutorial. Thank you.

Marty said...

It's really great. Thanks ~

Anonymous said...

Nice work bro..its really good..

Alex said...

I know this may sound stupid but, how can I add the tables already on my website to my mysql database named as messages since this program needs them to be called messages. Thank You

Anonymous said...

this tutorial is great. One suggestion though. it would be very much helpful if can also advice how to make this AJAX more SEO friendly.. please assist someone..

phps said...

good

Anonymous said...

bug: on refresh goes to page 1: How do I fix that?

Srinivas Tamada said...

This is ajax pagination. Once page refresh It will show fist page.

Anton said...

good work

how to apply pagination on search result.

Anonymous said...

urgent
help me,

I need a search model

I've modified the file load_data.php

$query_pag_data = "SELECT msg_id,message from messages";
$query_pag_data = $query_pag_data ."where msg_id is not null ";

if (isset($_POST['txt_search'])) {
$txt_search = $_POST['txt_search'];
$query_pag_data = $query_pag_data ."and message like '%$txt_search%'";
}

$query_pag_data = $query_pag_data ."LIMIT $start, $per_page ";


file pagination.php

function loadData(page){
loading_show();
var txt_search = $("input#txt_search").val();
$.ajax
({
type: "POST",
url: "load_data.php",
data: "page="+page+txt_search,
success: function(msg)
{
$("#container").ajaxComplete(function(event, request, settings)
{
loading_hide();
$("#container").html(msg);
});
}
});
}


not work

anyone can help me

thank you

David Nemes said...

Thank you.

salmony22 said...

It is really seemed a good tutorial , but i need to download it.
I subscribe but nothing happened to download the script

Anonymous said...

Can anyone demonstrate a classic asp version of this?

Srinivas Tamada said...

Emails updated

Anonymous said...

I not Javascript programmer but found this part of the code would load in an increment times each times I click the next or previous page, which mean:

$("#container").ajaxComplete(function(event, request, settings) { alert("load"); ... }

it seem to load with one alert prompt, when I click "page 2", it would prompt 2 times,
click "page 5", it would prompt 3 times,
click "page 1", it would prompt 4 times

if I click 50 pages, am I expect it to prompt 50 times rather than once? I not sure where is the problem. I would appreciate to know if anyone can solve this bug.

Anonymous said...

Hi,

Thanks for the code.

Could you please update the emails database ?

Thanks!

Paul Hanks said...

Yep, looks very nice. Unfortunately, I can't download the script even if I confirmed my subscription.

Can you update the email database please?

Paul Hanks said...

Oups, just see, database update every 18 hours, thks.

Anonymous said...

Hi, Need email id, i want to download the file..

Can u update the email db please?

Oink said...

nice tut's Ravi,

and i think i'm already get 18 hours but why still can't reach the data for download,

please update the database,..thx

Oink

Martin said...

excellent, thanks.

Aqeel shamas said...

Excellent script, I used this script in my codeigniter project...

Thanks for sharing this kind of stuff.

Anonymous said...

Why are you mixing presentation with business layer?

Anonymous said...

please Update your database as soon as possible as i want to download script

Madmath said...

Hi,
thaks for this script but I have a problem using with fancybox.this lines
$("#container").ajaxComplete(function(event, request, settings)
{
loading_hide();
$("#container").html(msg);
});

are not compatible with Fancybox call.

Do you have any solutions?

Thanks

Anonymous said...

Nice tutorial, but I've a problem with loading icon. In first load appears there for ever unless if you click to move to another page. Then works fine (IE8). Also, how I can change the mouse pointer?

Anonymous said...

This is *great*. Thank you very much.

Cheers.

Anonymous said...

Awesome - can you refresh the email list?

Anonymous said...

can u provide me the pagination.php page ?????

Anonymous said...

How can I sort the results?

Anonymous said...

Awesome - can you refresh the email list?

fantomas said...

Hi, nice script, but I have problem with it in ie (all version).

when I have more then 1 page it doesn´t work - I mean it doesn´t show comments just pagination without style etc.

don´t you know where is mistake?

thank for your answer.

sumon said...

thanks it a great help

Mike said...

dude, seriously? you're making people subscribe before they can dl the tutorial? and then you have an 18 hour wait?!? not cool.

Srinivas Tamada said...

@Mike

We are not charging anything, if you want this script wait sometime.

Reet said...

hey man you are great !!!!

Anonymous said...

Thanx...

Mohit Bumb said...

Wow, I just Love This Man
You Rocks
jQuery Rocks

Anonymous said...

can i ask if we can add search for a record. and can we jump for a specific page because i have a large database thanks

mordomiamil said...

Thanks

Anonymous said...

Thanks, but i am waiting forever for download code....

Andre said...

also to fix multiple requests when click add
return false; after loadData(page)

nha hang tiec cuoi said...

excellent, thanks.

ysetiawan said...

Hi Srinivas,

Any update about this:

Quote:
I not Javascript programmer but found this part of the code would load in an increment times each times I click the next or previous page, which mean:

$("#container").ajaxComplete(function(event, request, settings) { alert("load"); ... }

it seem to load with one alert prompt, when I click "page 2", it would prompt 2 times,
click "page 5", it would prompt 3 times,
click "page 1", it would prompt 4 times

if I click 50 pages, am I expect it to prompt 50 times rather than once? I not sure where is the problem. I would appreciate to know if anyone can solve this bug.

Thanks

Unknown said...

Nice!

Anonymous said...

How can i run multiple instances of this on a page? i have a page with tabs showing different content that i want to paginate.

jim said...

Hello friend, very nice tutorial.
I have a problem. I cannot display Greek characters from my db. It shows "?? ????? SEO?".
i try this on db.php "mysql_query("SET CHARACTER SET utf8");" but nothing.
Any Help?

Anonymous said...

Help! Need emails update! I want to download it!

Anonymous said...

Does this supports Arabic ?

Anonymous said...

greate bro...

minniedaminx said...

Great script. Just one suggestion for future revision - and this is common to a lot of AJAX pagination scripts: hard-coding the page size into the script may not be helpful for all applications. I use a variation of this script with a database which returns anything from 1 - 132 results, depending on the search term. For a majority of search terms, a page with 10 results is fine (and more importantly, keeps all the important information in view on even a smallish desktop), but not so useful for the small number of search terms returning a lot of results.

jotbe said...

hi,

im beginer in developing... How can i add another column to this scrpit. I would like to display id image and description. Thanks:)

Anonymous said...

thanks for this nice tutorial. But don't work for me. show object not found and below pagination buttons only. btw, how can I pass a parameter into the load_data.php script? e.g. search result using php array to javascript

agyrágóbogár said...

hopefully you still update the emails :) I liked one of your old tutorials a lot! Hopefully this will shed some lights in my head!

Vishal said...

Hi,
My Name is vishal from last some days i was searching the code of paging
....and i want to tell you thank you this code solved my all problems

Anonymous said...

hi Ravi. What is the reason to check for Emails not in the database of the subscripers? I can't see any advantage "updating" the database every 18 hours.

sona said...

it dont have first, last , prev and next link...

Anonymous said...

some visitors comments insert in different language, e.g. Russian (in Cyrillic) so i have problem with encoding, i have database with utf-8 coding and i changed charset to utf-8 but with no success. Any advice ?

Anonymous said...

hiiiiiiiiii

your script is not downloaded by.I m already subscribed.plz tell me how it is poassible

gagan said...

nice work done appreciated!! :-)

domator said...

Hi,
What should I change to the content to be visible in the source code to the script was more SEO friendly. Any ideas?

clchuong7913 said...

Hi,
I want to download script but i can't, please help me??

Cywel said...

This one is great, I wonder if you could make it stay on the same page every time you refresh?

Duc NM said...

Hi,
Thanks for the code. It's very nice
Could you please update the emails database ? I want to download scripts
Thanks!

Anonymous said...

great script. a lot of the jquery/mysql pagination tutorials ive come across just leave out the first/last buttons, which are pretty important imo, especially when there are lots of pages

Hans said...

thanks is amazing i can do it

Anonymous said...

Thank you very much!!!

phpOberon said...

good good :_)

prashant sharma said...

great

Sinan said...

hi how can i pass another value with the page value in this, i tried but failed to
function loadData(page)

i want to pass another value like

function loadData(page, catid)

coz it will be filtered for catid

suga said...

nice

t said...

nice

Akhil said...

gr8

aipod said...

@Anonymous @ysetiawan
i'm not good at programming but, to avoid those recurring alert box, try to change

$("#container").ajaxComplete(function(event, request, settings)
{
loading_hide();
$("#container").html(msg);
});

to just this

loading_hide();
$("#container").html(msg);

when i tried it, alert box only appears once each click..

@anonymous

to get the css hover, and pointer works correclty in IE 8,9 just like firefox and chrome (haven't tested it in IE6 or IE7)

you can change the DOCTYPE to this
!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">"

Anonymous said...

good

Anonymous said...

Hi,

Thanks for your script.

George Luiz

Anonymous said...

You have done very nice pagenation

parttimejob said...

where i can download? trying last two days??

Anonymous said...

Thanks for your pagination code which meet my requirement. I subscribed with email.Please update the database to download the script.

Thanks.

Anonymous said...

hi good code.
please suggest me how can i implement seralise() method in this

Anonymous said...

thanks man :)

Jagan Krishnaraj said...

Thanks for the script. I use this script in all my coding. Many Thanks.

Prashanth Mallavarapu said...

i am struck coz, i need to seriailze the data sent. could you please tell me how to do that

Anonymous said...

Great tut but would love it even better if you'd indented your code!

Anonymous said...

great job

winracer said...

nice job!!!!!

Payment Gateway Integration said...

Very good coding, I found this article useful. Thanks

Anonymous said...

I'm not giving my email just to obtain your code. Nice try.

rajith89 said...

Great work , Sir . Can you please update the Subscriber info.People like you are essential for the well being of open-source community. Good luck.

Anonymous said...

Wow, Absolutely Fantastic... Thank you very much sir.... It helps me a lot.

Website Designer in Mumbai said...

Thnx.. a lot for the awesome code!!!!
its really useful for me.

Anonymous said...

Thanks bro.... This is really a nice script. Like it.. Thanks for sharing such a nice code with us !!!!!!!!!!!!!!

Chandan Bora said...

Hey Srinivas,
well thanks for this nice post, i need a urgent help on this topic.
I am using this script in one of my project, i need a slide effect in each page click, so i am using bxslider for this. but its not working over it.. can you help me to sort out this..

Nathan said...

Thanks a lot for this script, can't wait to download and use it.

But when will the subscriber info be updated? Why can't it just be an hour or something?

Anonymous said...

Please update the subscriber email list -- THX --- Awesome script!!!

Will Earp said...

Use SQL_CALC_FOUND_ROWS in the first statement to prevent having to COUNT(*) the results afterwards. Then retrieve the total number of rows with 'SELECT FOUND_ROWS() AS `count`;'

herc1983 said...
This comment has been removed by the author.
herc1983 said...

Hello Srinivas,
first of all i would like to thank you for your very helpful script.
I made the script to work perfect on all browsers but on iphone,ipads and android browsers the pagination bar(numbers,first,last) doesn't show at all.
Is there a hint that could help me fix it?

siva said...

i want this coding.. but i can't..

Anonymous said...

Hi..this demo looks really nice! thanks for sharing..

stg said...

i found what i'm looking for......thnx

stg said...

i found what i'm looking for......thnx

Anonymous said...

How do i pass along GET variabel ?
like

index.php?categori=".$categori."categori_sub=".$categori_sub etc ?

some exp ? pls

Anonymous said...

How do i pass along GET variabel ?
like

index.php?categori=".$categori."categori_sub=".$categori_sub etc ?

some exp ? pls

I mean like this
--------------------------
URL :index.php?categori=2&categori_sub=12&cat_options=3

Ramesh said...

Hi, i need to show the search results based on some parameters. can you let me know how i pass those variables from pagination.php to pagination_data.php.

-Thanks

Tejal Patel said...

it's working perfectly in every combination, but i want to user group by in 'query_pag_num'.

when i use group by it will not working correctly.

for example : SELECT COUNT(*) AS count ,members.m_id as m_id,product.productname as pname,product.p_id as p_id,product.pimage FROM members left join product ON members.m_id=product.m_id where members.status='1' AND members.ccity LIKE '%".$_POST['city']."%' AND ( members.companyname LIKE '%".$_POST['name']."%' OR product.productname LIKE '%".$_POST['name']."%') group by members.m_id

plz tell me solution.

Anonymous said...

thanks

Anonymous said...

How to download the COde

Anonymous said...

but i need to download it, I subscribe but nothing happened to download the script

Anonymous said...

Why do you need someones email address or to subscribe for something he does not want to. If you put some tutorial then leave it to the people if they want to give you their email address so you can sell it.

Khắc Điệp Phạm said...

This is greate tut ! Thank so much !

Anonymous said...

if we pass negative no to "go" option, we get below message:

--Pagination with jquery, Ajax and PHP(tutorial link)--.
MySql ErrorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-45, 15' at line 1

Trần Công Nghiệp said...

nice tip

Anonymous said...

Grt Wrk.. Thanks So much

Arcade Games Script said...

I was looking for tutorial which shows pagination results on the same page without refreshing the page. You tutorial was helpful for me.

Anonymous said...

nice tutorial, please update the records, i need download the source code.

tks

Anonymous said...

the subscription is the most annoying thing you can put your visitors in front of... thanks for not being able to check the code...

Yogesh Gurjar said...

Great script. Just one suggestion for future revision - and this is common to a lot of AJAX pagination scripts: hard-coding the page size into the script may not be helpful for all applications. I use a variation of this script with a database which returns anything from 1 - 132 results, depending on the search term. For a majority of search terms, a page with 10 results is fine (and more importantly, keeps all the important information in view on even a smallish desktop), but not so useful for the small number of search terms returning a lot of results.

Anonymous said...

please update the records, i need download the source code.

Thanks....

dimarios said...

thanks a lot.. is very good!

Anonymous said...

I have a form on pagination.php page and it have 3 fields name "fromdate" "todate" and "called_number" when i submit the form 3 different-2 mysql queries executes.
so i need pagination in this case.
please help me.

Anonymous said...

good

Marlou said...

Hi! Can i see a preview?

Marlou said...

Sorry, it's on the page :)I saw it just now.

sagar patil said...

Thanks for the code. It's very nice
Could you please update the emails database ? I want to download scripts

Amaan Sattar said...

Thanks For The Code Man i have a Question i use your code with my Rewrtie Url its Not Working Can you tell me how can it Works ? Thanks in advance :)

Anonymous said...

THANKS FOR THIS CODE

Ruffy said...

Exactly what I'm looking for...
Thanks a lot my friend

Sudarsh said...

Thank you so much.
You made my day. :)

Omprakash Bagrao said...

Can you please try to minimize new subscriber updation time? I can wait more for this script really loving this script by live link.

Naseem said...

Hello,

It is NOT working with touch devices (Like smart phones). Do you have any idea to activate Touch ?
Please let us know

Naseem

Anonymous said...

Thank you so much. This kind of code is hard to find.

Stay well.

joseph bupe

Naseem said...

Got an idea.. use click + tap, so it will work with touch mobiles.
See this
$('#container .pagination li.active').live('click tap',function()

Anonymous said...

I think it`s better to calculate the total pages first and then validate the requested page number to be >= 1 and <= totalPages and change it to 1 or totalPages if it is not valid

Dove dreams said...

hi i got following error


MySql ErrorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2, 2' at line 1

niteshhardia said...

pagination is not working on mobile device
can you please give solution here

Anonymous said...

your live demo and download buttons are epic now

Anonymous said...

How do i make thise code working for MSSQL ? through ODBC ?

saravanababu v said...

how to limit records in sql server 2008

Anonymous said...

excellent thank you man

ThanhTran said...

Error with Jquery 1.9.0 .How to fix ?Ravi

Anonymous said...

Nice !

RIM said...

This is a great post, and helping me in understanding and resolving one of my wishlist for a long time. However, I have a small glitch in downloading the content. It is kind of going in a loop. Not sure what is going on ... even after registering and confirming over 48 hours. Please let us know the current download for this post works. Thanks you.

Bala Goud said...

its excelent and thanks for your script Mr Srinivas
But I would like to ask you that how to impiment search functionality for this

Anonymous said...

on refresh should come on sane page

Okan Vurdu said...

great post, thanks for sharing.

Anonymous said...

Good day nice tutorial :)

Anyways I was just wondering since you've put here an update link that says "Update :Pagination with jQuery (Ver 2.0)" on this link http://www.9lessons.info/2009/09/pagination-with-jquery-mysql-and-php.html and then I was redirected here, and I noticed that you're STILL using jquery 1.4.2 version

But then again, thanks for sharing ;)
more power, cheers!!!

Abegail Madayag said...

very nice .... i love it

gagan said...

i want this script i also subscribed from so many months back but unable to get this script can any body help me

Anonymous said...

Suppose there are 17 rows, won't this show an error for second page(in error logs)?

abc said...

Great Work.

Anonymous said...

thank you mr srinivas you are a true lifesaver, we learn a lot from your post, thank you thank you thank you

Ahmad Ryo Shobirien said...

Terima Kasih :D

kavinthan Uruthiramoorthy said...

Thankyou Sir....
What an awesome post

felix santoso said...

Awesomeness redefined

For Truth Travelers said...

Here Codeigniter Version------>>>> https://github.com/senanayaka/codeigner.com/blob/master/README.md

Jam said...

Thanks a lot, simple, clear and useful.

Jee92 said...

Can you share this script but for XML data????? Thanks

Anonymous said...

Great Tutorial...
appreciable Man

Anonymous said...

great work

wajid said...

very nice script you have

marc13 said...

demo in great. just waiting for subscriber update now.
thanks

Nasim Bahar said...

thanks for great demo...

Post a Comment