How to create Advanced Pagination in PHP & MySQL with jQuery
We have already created 2 pagination[1][2] articles for PHP Developers and this one is the advanced version of pagination with “…” many readers requested me to make it with these dots, so today I am going to write this article on advance pagination with jQuery and show you how to show small list of numbers with dots in between.
[wpdm_file id=52]DEMO
Database design and table:
database name => phpgang
table name => pagination
column names => id, post, postlink
db.sql
Database file run in your mysql to create database and add data in table.
create database `phpgang`; use `phpgang`; CREATE TABLE `pagination` ( `id` int(11) NOT NULL auto_increment, `post` varchar(250) NOT NULL, `postlink` varchar(250) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=48 DEFAULT CHARSET=latin1 AUTO_INCREMENT=48 ; INSERT INTO `pagination` VALUES (1, 'How to Resize text using jQuery', 'https://www.phpgang.com/?p=312'); INSERT INTO `pagination` VALUES (2, 'How to Integrate live search in PHP and MySQL with jQuery', 'https://www.phpgang.com/?p=309'); INSERT INTO `pagination` VALUES (3, 'How to implement jquery Timeago with php', 'https://www.phpgang.com/?p=290'); INSERT INTO `pagination` VALUES (4, 'How to Mask an input box in jQuery', 'https://www.phpgang.com/?p=304'); INSERT INTO `pagination` VALUES (5, 'How to block Inappropriate content with javascript validation', 'https://www.phpgang.com/?p=301'); INSERT INTO `pagination` VALUES (6, 'How to Crop Image with jQuery and PHP', 'https://www.phpgang.com/?p=298'); INSERT INTO `pagination` VALUES (7, 'How to Integrate jQuery Scroll Paging with PHP', 'https://www.phpgang.com/?p=294'); INSERT INTO `pagination` VALUES (8, 'Bug Reporting with Windows Program Steps Recorder (PSR)', 'https://www.phpgang.com/?p=291'); INSERT INTO `pagination` VALUES (9, 'How to Configure Google Cloud API in PHP', 'https://www.phpgang.com/?p=288'); INSERT INTO `pagination` VALUES (10, 'How to convert text to MP3 Voice', 'https://www.phpgang.com/?p=284');
db.php
Database configuration file edit database name, user and password as per your configuration.
<?php
$connection = mysqli_connect('localhost','phpgang_usr','******','phpgang') or die(mysqli_error($connection));
?>style.css
Contain styles for your pagination numbers and next and previous.
<style>
div.pagination {
padding: 3px;
margin: 3px;
text-align:center;
}
div.pagination a {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #AAAADD;
text-decoration: none; /* no underline */
color: #000099;
}
div.pagination a:hover, div.digg a:active {
border: 1px solid #000099;
color: #000;
}
div.pagination span.current {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #000099;
font-weight: bold;
background-color: #000099;
color: #FFF;
}
div.pagination span.disabled {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #EEE;
color: #DDD;
}
</style>JQuery Required to handle AJAX functions:
<script type="text/javascript" src="jquery-1.8.0.min.js"></script>
<script type="text/javascript">
function changePagination(pageId){
$(".flash").show();
$(".flash").fadeIn(400).html
('Loading <img src="ajax-loader.gif" />');
var dataString = 'pageId='+ pageId;
$.ajax({
type: "POST",
url: "loadData.php",
data: dataString,
cache: false,
success: function(result){
$(".flash").hide();
$("#pageData").html(result);
}
});
}
</script>loadData.php
This file contain complete Logic of the pagination and in index file we gather all js and css together:
<?php
include_once('db.php');
$query="select id from pagination order by id asc";
$res = mysqli_query($connection,$query);
$count = mysqli_num_rows($res);
$page = (int) (!isset($_REQUEST['pageId']) ? 1 :$_REQUEST['pageId']);
$page = ($page == 0 ? 1 : $page);
$recordsPerPage = 5;
$start = ($page-1) * $recordsPerPage;
$adjacents = "2";
$prev = $page - 1;
$next = $page + 1;
$lastpage = ceil($count/$recordsPerPage);
$lpm1 = $lastpage - 1;
$pagination = "";
if($lastpage > 1)
{
$pagination .= "<div class='pagination'>";
if ($page > 1)
$pagination.= "<a href=\"#Page=".($prev)."\" onClick='changePagination(".($prev).");'>« Previous </a>";
else
$pagination.= "<span class='disabled'>« Previous </span>";
if ($lastpage < 7 + ($adjacents * 2))
{
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<span class='current'>$counter</span>";
else
$pagination.= "<a href=\"#Page=".($counter)."\" onClick='changePagination(".($counter).");'>$counter</a>";
}
}
elseif($lastpage > 5 + ($adjacents * 2))
{
if($page < 1 + ($adjacents * 2))
{
for($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if($counter == $page)
$pagination.= "<span class='current'>$counter</span>";
else
$pagination.= "<a href=\"#Page=".($counter)."\" onClick='changePagination(".($counter).");'>$counter</a>";
}
$pagination.= "...";
$pagination.= "<a href=\"#Page=".($lpm1)."\" onClick='changePagination(".($lpm1).");'>$lpm1</a>";
$pagination.= "<a href=\"#Page=".($lastpage)."\" onClick='changePagination(".($lastpage).");'>$lastpage</a>";
}
elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
{
$pagination.= "<a href=\"#Page=\"1\"\" onClick='changePagination(1);'>1</a>";
$pagination.= "<a href=\"#Page=\"2\"\" onClick='changePagination(2);'>2</a>";
$pagination.= "...";
for($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
{
if($counter == $page)
$pagination.= "<span class='current'>$counter</span>";
else
$pagination.= "<a href=\"#Page=".($counter)."\" onClick='changePagination(".($counter).");'>$counter</a>";
}
$pagination.= "..";
$pagination.= "<a href=\"#Page=".($lpm1)."\" onClick='changePagination(".($lpm1).");'>$lpm1</a>";
$pagination.= "<a href=\"#Page=".($lastpage)."\" onClick='changePagination(".($lastpage).");'>$lastpage</a>";
}
else
{
$pagination.= "<a href=\"#Page=\"1\"\" onClick='changePagination(1);'>1</a>";
$pagination.= "<a href=\"#Page=\"2\"\" onClick='changePagination(2);'>2</a>";
$pagination.= "..";
for($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
{
if($counter == $page)
$pagination.= "<span class='current'>$counter</span>";
else
$pagination.= "<a href=\"#Page=".($counter)."\" onClick='changePagination(".($counter).");'>$counter</a>";
}
}
}
if($page < $counter - 1)
$pagination.= "<a href=\"#Page=".($next)."\" onClick='changePagination(".($next).");'>Next »</a>";
else
$pagination.= "<span class='disabled'>Next »</span>";
$pagination.= "</div>";
}
if(isset($_POST['pageId']) && !empty($_POST['pageId']))
{
$id=$_POST['pageId'];
}
else
{
$id='0';
}
$query="select post,postlink from pagination order by id asc
limit ".mysqli_real_escape_string($connection,$start).",$recordsPerPage";
//echo $query;
$res = mysqli_query($connection,$query);
$count = mysqli_num_rows($res);
$HTML='';
if($count > 0)
{
while($row=mysqli_fetch_array($res))
{
$post=$row['post'];
$link=$row['postlink'];
$HTML.='<div>';
$HTML.='<a href="'.$link.'" target="_blank">'.$post.'</a>';
$HTML.='</div><br/>';
}
}
else
{
$HTML='No Data Found';
}
echo $HTML;
echo $pagination;
?>Your request via AJAX function sent to that php file and it will process the request and return you paginated data.
This line of code return all numbers in the table and show count.
$query="select id from pagination order by id asc"; $res=mysql_query($query); $count=mysql_num_rows($res);
It will show you 5 records on each page you can increase records and decrease by changing this value $start = ($page-1) * 5; to any number you want.
Index file complete code of css and jquery together:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>How to create pagination in PHP and MySQL with AJAX | PGPGang.com</title>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<script type="text/javascript" src="jquery-1.8.0.min.js"></script>
<style>
div.pagination {
padding: 3px;
margin: 3px;
text-align:center;
}
div.pagination a {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #AAAADD;
text-decoration: none; /* no underline */
color: #000099;
}
div.pagination a:hover, div.digg a:active {
border: 1px solid #000099;
color: #000;
}
div.pagination span.current {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #000099;
font-weight: bold;
background-color: #000099;
color: #FFF;
}
div.pagination span.disabled {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #EEE;
color: #DDD;
}
</style>
<script type="text/javascript">
$(document).ready(function(){
changePagination('0');
});
function changePagination(pageId){
$(".flash").show();
$(".flash").fadeIn(400).html
('Loading <img src="ajax-loader.gif" />');
var dataString = 'pageId='+ pageId;
$.ajax({
type: "POST",
url: "loadData.php",
data: dataString,
cache: false,
success: function(result){
$(".flash").hide();
$("#pageData").html(result);
}
});
}
</script>
</head>
<body>
<h2>How to create pagination in PHP and MySQL with AJAX example. => <a href="https://www.phpgang.com/">Home</a> | <a href="http://demo.phpgang.com/">More Demos</a></h2>
<div id="pageData"></div>
<span class="flash"></span>
</body>
</html>Feel free to comment your suggestions and problems you are facing in regard this tutorial.
Tutorial Categories:
Thanks bro
thanks man but how can i change the number of pages it s stuck with 10
You have to increase number of your total records in this query i counted number of records.
$query=”select id from pagination order by id asc”;
$res=mysql_query($query);
$count=mysql_num_rows($res);
for dummy you can do it like that $count = 200; on line number 6 in loadData.php.
for instance i want my total number of pages are to be 5 what should i do ? (sorry for my english) :))
Read instruction given in this tutorial (It will show you 5 records on each page you can increase records and decrease by changing this value $start = ($page-1) * 5; to any number you want.)
i think that in your database you had 50 rows that s why the navigation bar ended with 10 pages, so i tried to impliment this in my database which has 20 rows but it didn t work so i changed some numbers for instance i divided $count by 2 so it worked but it continued to print 10 pages however i was only 5 page result at all (20 rows 5 results for each page),
i tried to discover its arithmetic but i didn t find it so how can i modify it ?
thank you (i can send my loadData.php if you want)
Have you changed $count = 200; field value to 20?
This code is appallingly bad. There is no clear separation of concerns, the database functions being used have been deprecated and have far better and safer equivalents (PDO, for example) and the code is needlessly verbose and haphazard. It has all the hallmarks of an amateur developer’s code. Sure it’ll work but it is utterly unmaintainable.
To suggest this is in any way “advanced” is laughable
It is poor form to delete comments from people who point out the flaws in your articles. Address the issues like a grown up, or don’t open a forum for comments at all.
Hello sir, i have seen this post and i must agree this is awesome. The thing i want to mention here is that when we change the page content by clicking the number the next page result appear and simultaneously the url changes, up till this sound good but when i click on browsers back button only the url get changes not the page content, any solution to change the content when user click on back button, will make this example much advance. Thanks n regards : vivek
@disqus_0kqNLfcZZj:disqus Thnks for your comment I am working on this feature and hopefully publish it very soon.
Glad to hear from you sir, i would really love to see such feature in your upcoming post. Thanks for reply.
hello sir i have used this code its work properly but when rewrite URL using .htaccesses i am faced problem.
Hello sir, I have some problem with file “loadData.php” it shows me an error
Undefined variable: counter in /Paginaion/loadData.php on line 71, the exact string is if($page < $counter – 1)
how can i fix it?
Define empty value in $counter like this…
Can you pls. give an explanation re the constants that you used. For example, the number 4 in line 29 of loadData.php. Or the number 5 that is frequently appearing. Is it equal to the numbers of rows to be displayed. Thank you.
sir,
i am getting following error message in “loadData.php” why ?
The page cannot be displayed because an internal server error has occurred.
there is no error in database connection but not getting please help sir…………
Turn on errors reporting on loadData.php file then send me exact error which you are getting.
how to turn on .. actually i am new to php.
Write this line on top of the page
ini_set("display_errors",1);no sir, i am getting same message. The page cannot be displayed because an internal server error has occurred.
Without Error how can I help you try to show error are you using localhost this is the solution to show errors on localhost http://stackoverflow.com/questions/9862348/php-scripts-not-showing-any-error-on-localhost
ya… on the server side not localhost.
line number 19 sir
just now i have checked out basic pagination . that is working properly. that was awesome. and i am using the same table for advanced pagination. but not working….. please help me…
I can’t Help you until you show me errors.
i am sorry for the confusion. i have checked the code in online. there is no errors in the code. and i was testing it with a new table. now it is working very fine. anyway thanks for immediate reply. i would like say thanks for you and your website. for providing such scripts. it was great. thank you again.
sir,
i want to display 10 results per page.
and in mobile also page numbers are displaying in a single row like this « Previous 12…34567..910Next »
i need
« Previous 12…
34567..910Next » like this depending upon the device width.
can you help me……
Use Responsive themes.
i want to display 10 results per page. how to do..???? give me example….. please
sir,
i was changed line no. 88 limit “.mysqli_real_escape_string($connection,$start).”,5″;
i.e, limit “.mysqli_real_escape_string($connection,$start).”,10″; . now which is displaying 10 results. but the data is repeating again. (i have 50 records only)
and always 10 page numbers were displayed. where should i modify it. please reply……
There is a mistake in code I have fixed it no please download source code again and use it.
I have added a variable $recordsPerPage = 5; change value of that variable to increase/decrease number of record per page.
ok thank you………
Sir,
my page links are not working, it only displays page 1 content but clicks on page 2 or next is not working, here is a code:
1)
{
$pagination .= “”;
if ($page > 1)
$pagination.= “« Previous “;
else
$pagination.= “« Previous “;
if ($lastpage < 7 + ($adjacents * 2))
{
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "$counter“;
else
$pagination.= “$counter“;
}
}
elseif($lastpage > 5 + ($adjacents * 2))
{
if($page < 1 + ($adjacents * 2))
{
for($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if($counter == $page)
$pagination.= "$counter“;
else
$pagination.= “$counter“;
}
$pagination.= “…”;
$pagination.= “$lpm1“;
$pagination.= “$lastpage“;
}
elseif($lastpage – ($adjacents * 2) > $page && $page > ($adjacents * 2))
{
$pagination.= “1“;
$pagination.= “2“;
$pagination.= “…”;
for($counter = $page – $adjacents; $counter <= $page + $adjacents; $counter++)
{
if($counter == $page)
$pagination.= "$counter“;
else
$pagination.= “$counter“;
}
$pagination.= “..”;
$pagination.= “$lpm1“;
$pagination.= “$lastpage“;
}
else
{
$pagination.= “1“;
$pagination.= “2“;
$pagination.= “..”;
for($counter = $lastpage – (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
{
if($counter == $page)
$pagination.= "$counter“;
else
$pagination.= “$counter“;
}
}
}
if($page < $counter – 1)
$pagination.= "Next »“;
else
$pagination.= “Next »“;
$pagination.= “”;
}
if(isset($_POST[‘pageId’]) && !empty($_POST[‘pageId’]))
{
$id=$_POST[‘pageId’];
}
else
{
$id=’0′;
}
$query=”select * from jobs order by date desc
limit “.mysqli_real_escape_string($connection,$start).”,$recordsPerPage”;
//echo “$query”;
$res = mysqli_query($connection,$query);
$count = mysqli_num_rows($res);
$HTML=”;
if($count > 0)
{
while($rows=mysqli_fetch_array($res))
{
echo “”;
echo “”.$rows[‘position’].””;
echo “Experience:“.$rows[‘experience’].””;
echo “Location:“.$rows[‘location’].””;
echo “”;
echo “”;
}
}
else
{
$HTML=’No Data Found’;
}
echo $HTML;
echo $pagination;
?>
Sir,
Ajax part of code is not working. Tell what to change for it.
What error you are getting in it debug ajax in firebug and send me exact error.
Glad to see the article,as its very helpful to me and i got clear information on advanced version of pagination.php developers can have more information on this in php projects
Im receiving this error: “Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:Program Files (x86)ZendApache2htdocsokasloadData.php on line 102 No Data Found”. Weird because its the same query I have in line 4 and it works.
i use a comment to check if the query in line 4 works and results were displayed, despite the fact that pagination appear but didnt work. any help appreciated. brgds!
Make sure that you have a valid connection link in 1st parameter.
thanks for your reply!
your code is not working found many errors… its to bad code
fuck
thanks for the tutorial. It is woking fine…love your mailing list.
Hi phpgang, it’s a nice tutorial. I was able to use it on my codes, but when I click the next page, it loads to blank page. I know it must be somethign to do with the AJAX. I tried to change the URL as well as the class and id, but when it loads, it goes to blank page. Help, please? Thanks!
Hi, it works find, but when i refresh page it always return to page first page. I want to know how to keep current page when reloaded
i want to download this script ….but i cnt
i subscribed my id bt i cnt ..rply asap
what email address you are using to download
[email protected]
Its working fine
cn u mail me this comtent?pls
hello sir i want to search like google in php
i have tried implementing your live search tutorial. but i am having a little trouble. the trouble is that, the result div is displaying bydefault whenever the page is opened and goes when i click anywhere else on the page. please help!! 🙂
Assalam Alikum Huzoor brother,
How to use below command with where condition,
$query=”select post,postlink from pagination order by id asc
limit “.mysqli_real_escape_string($connection,$start).”,$recordsPerPage”;
as i have drop down list with dependency serach of items.
my code as follow.
else {
$sql = “SELECT * FROM “.$SETTINGS[“city_table”].” where list_id>0″.$search_string.$search_city;
}
In your logic you have used:
if ($lastpage 5 + ($adjacents * 2)) //enough pages to hide some
{
…..
}
Why do you need the elseif part, just use else ?
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:xampphtdocspage.php on line 84
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:xampphtdocspage.php on line 180
No Data Found
I’m trying this for a photo gallery so I’ve set the number of results to 1. How can I change the code to ONLY show <>?
Your code is mixture of mysql and mysqli functions.
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:AppServwwwcocloadData.php on line 6
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:AppServwwwcocloadData.php on line 102
No Data Found
Hello
nice tutorial, …
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:AppServwwwcocloadData.php on line 6
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:AppServwwwcocloadData.php on line 101
No Data Found
Help x.x
banchod
sir database site pr show nhi ho rha
https://uploads.disquscdn.com/images/f5d9175e78da528e31caec0fe613bfdb0e1469f2b247dad777027be1b0ad711c.png
I don’t usually care to leave a comment but dis was one hell of Tutorial dat was so irresistible. First: Whoever made it is a very generous person with an open heart. Kudos
Hello I am implementing it for a shopping cart and it works, only the detail is that it does not work with a search engine, that is, with a form that I have and send the data by post to the same index, any proposal?
Sorry. this is my code for search
if($_POST){
$busqueda = trim($_POST[‘buscar’]);
$entero = 0;
if (empty($busqueda)){
$products = $con->query(“select * from alma where CANTIDAD = ‘1’ “);
}else{
$products = $con->query(“select * from alma where (SERVICE_TAG LIKE’%$busqueda%’ OR MARCA LIKE’%$busqueda%’ OR DESCRIPCION LIKE’%$busqueda%’
OR ARTICULO LIKE’%$busqueda%’ OR BARRAS LIKE’%$busqueda%’ OR MODELO LIKE’%$busqueda%’ OR SERIE LIKE’%$busqueda%’ OR MARCA LIKE’%$busqueda%’
OR PROVEEDOR LIKE’%$busqueda%’ OR RESPONZABLE LIKE’%$busqueda%’ OR FACTURA LIKE’%$busqueda%’ OR OC LIKE’%$busqueda%’
OR STATUS LIKE’%$busqueda%’ OR DESTINO LIKE’%$busqueda%’ OR UBICACION LIKE’%$busqueda%’ OR OBSERVACIONES LIKE’%$busqueda%’) AND CANTIDAD = ‘1’ “);
unset($_POST[‘buscar’]);
}
}
Hello Your PHP Pagination demo not Working . May be issue of database connectivity .