Pagination is a crucial part of any website, especially if you have hundreds of database records that you want to group and display them as pages, and in modern days with the help of Ajax you can create pagination that doesn’t require any page reloading, users can stay in same page and navigate through vast numbers of records on fly. In this particular article we will be creating Ajax pagination using jQuery and PHP that can navigate though your database records without reloading the page.
This article has been updated, you can find other approach here.
We start off by creating a HTML file, which will make the Ajax requests to PHP file and load the response back to the page in a DIV element. We also have a loading image within the page to indicate loading processes.
1 2 | <div class="loading-div"><img src="loader.gif" ></div> <div id="results"><!-- content will be loaded here --></div> |
It’s possible to make Ajax request just using JavaScript, but since most of the websites are built with jQuery support, we will utilize the jQuery’s inbuilt load() method to make Ajax calls to the PHP file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $(document).ready(function() { $("#results" ).load( "fetch_pages.php"); //load initial records //executes code below when user click on pagination links $("#results").on( "click", ".pagination a", function (e){ e.preventDefault(); $(".loading-div").show(); //show loading element var page = $(this).attr("data-page"); //get page number from link $("#results").load("fetch_pages.php",{"page":page}, function(){ //get content from PHP page $(".loading-div").hide(); //once done, hide loading element }); }); }); |
We will load the first batch of records using load() method, and once the records are loaded, we can trigger click events in generated pagination links, which will again make the Ajax requests to the PHP file using page numbers and then the expected records will be loaded back in the results element.
Now we have jQuery code ready to make Ajax requests, we can create a new PHP file that will respond to Ajax requests made by the code above. This PHP file will work in background and have no direct contact with user.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | $db_username = 'root'; //database username $db_password = ''; //dataabse password $db_name = 'test_db'; //database name $db_host = 'localhost'; //hostname or IP $item_per_page = 5; //item to display per page $mysqli = new mysqli($db_host, $db_username, $db_password, $db_name); //Output any connection error if ($mysqli->connect_error) { die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error); } //Get page number from Ajax if(isset($_POST["page"])){ $page_number = filter_var($_POST["page"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH); //filter number if(!is_numeric($page_number)){die('Invalid page number!');} //incase of invalid page number }else{ $page_number = 1; //if there's no page number, set it to 1 } //get total number of records from database $results = $mysqli_conn->query("SELECT COUNT(*) FROM paginate"); $get_total_rows = $results->fetch_row(); //hold total records in variable //break records into pages $total_pages = ceil($get_total_rows[0]/$item_per_page); //position of records $page_position = (($page_number-1) * $item_per_page); //Limit our results within a specified range. $results = $mysqli->prepare("SELECT id, name, message FROM paginate ORDER BY id ASC LIMIT $page_position, $item_per_page"); $results->execute(); //Execute prepared Query $results->bind_result($id, $name, $message); //bind variables to prepared statement //Display records fetched from database. echo '<ul class="contents">'; while($results->fetch()){ //fetch values echo '<li>'; echo $id. '. <strong>' .$name.'</strong> — '.$message; echo '</li>'; } echo '</ul>'; echo '<div align="center">'; // To generate links, we call the pagination function here. echo paginate_function($item_per_page, $page_number, $get_total_rows[0], $total_pages); echo '</div>'; |
After connecting to MySql database, we need to know total records of the table and break them into pages. Once we know this, we can fetch and display records determined by the MySql LIMIT clause. As you can see in the end of the script above, we have called the paginate_function() PHP function, this function expects certain parameters, we need to pass those parameters in order to generate pagination link.
Below is the PHP function that generates a nice pagination links for us, we just need to call it in our PHP script to create a pagination links depending on the parameters we pass to this function.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | function paginate_function($item_per_page, $current_page, $total_records, $total_pages) { $pagination = ''; if($total_pages > 0 && $total_pages != 1 && $current_page <= $total_pages){ //verify total pages and current page number $pagination .= '<ul class="pagination">'; $right_links = $current_page + 3; $previous = $current_page - 3; //previous link $next = $current_page + 1; //next link $first_link = true; //boolean var to decide our first link if($current_page > 1){ $previous_link = ($previous==0)?1:$previous; $pagination .= '<li class="first"><a href="#" data-page="1" title="First">«</a></li>'; //first link $pagination .= '<li><a href="#" data-page="'.$previous_link.'" title="Previous"><</a></li>'; //previous link for($i = ($current_page-2); $i < $current_page; $i++){ //Create left-hand side links if($i > 0){ $pagination .= '<li><a href="#" data-page="'.$i.'" title="Page'.$i.'">'.$i.'</a></li>'; } } $first_link = false; //set first link to false } if($first_link){ //if current active page is first link $pagination .= '<li class="first active">'.$current_page.'</li>'; }elseif($current_page == $total_pages){ //if it's the last active link $pagination .= '<li class="last active">'.$current_page.'</li>'; }else{ //regular current link $pagination .= '<li class="active">'.$current_page.'</li>'; } for($i = $current_page+1; $i < $right_links ; $i++){ //create right-hand side links if($i<=$total_pages){ $pagination .= '<li><a href="#" data-page="'.$i.'" title="Page '.$i.'">'.$i.'</a></li>'; } } if($current_page < $total_pages){ $next_link = ($i > $total_pages)? $total_pages : $i; $pagination .= '<li><a href="#" data-page="'.$next_link.'" title="Next">></a></li>'; //next link $pagination .= '<li class="last"><a href="#" data-page="'.$total_pages.'" title="Last">»</a></li>'; //last link } $pagination .= '</ul>'; } return $pagination; //return pagination links } |
I have also created CSS which will make your pagination look great, you can find everything in sample file. You can download these files from links below. Just checkout the demo and test it on your website, I hope it will help you create nice pagination for your website, you can always comment below and help make this code better. If you want to load records like Facebook or Twitter style, you can checkout my other article — Loading More Results.
When I paginate to the next page my images are not loaded?!
Please help?
Reply“thanks for the tutorial the script work perfectly, but the pagination didn’t display.” – Same here
Replyvery nice tutorial.i have a small doubt to display the old posts also when click on pagination.
ReplyI found this issue when click on 2nd pagination from the active page 2 looking for solution.
Fatal error: Call to a member function execute() on boolean in /home/sanlov/sanwebe.com/assets/files/ajax_pagination.php on line 32
ReplyRelated Posts
ReplyAjax Shopping Cart with PHP and jQuery
Ajax Image upload & Resize with PHP ImageMagick & jQuery
Slide Loading Social Buttons with jQuery, CSS3 & PHP
Ajax Contact Form with an Attachment (jQuery & PHP)
Google Map v3 Editing & Saving
Woo this is great code thank you to share
Replythank you for this awesome tutorial.
Replywhen i run this code on localhost,then show fine result,but when i run this code on server,then i see black page,no data fetch from data base.
please some body help me to solve this problem
ReplyHi, how to pass paramter to fetch_pages.php like guest.php?id=2&user_id=34
Thanks,
ReplyHi guys,
The problem of the pagination. You can change this
$previous_link = ($previous==0)?1:$previous;
for this
$previous_link = ($previous<=0)?1:$previous;
ReplyThanks
How pass parameter query to fetch_pages.php ?
ReplyEx:
index.php?q=love
Not Google SEO friendly? I checked out the demo link, right-click view source, the content is not converted into html which mean google spider cant see the content in the body??
this all i see, not a list of stuff:
Reply2
<div id="results"><!-- content will be loaded here --></div></div>
Really helpful thanks a lot
ReplyHi friend, nice tutorial good sollution, but when the page is refreshed your page back to page 1. now how pagination and content always stay when the page is refreshed?
Thanks
ReplyHow transfer parameter to fetch_pages.php ?
Ex:
index.php?var=123456
Thank you so much for this solution its exactly working perfectly
ReplyThanks a lot
Hiii,
i have use your code for my project but i got
Fatal error: Call to a member function execute() on a non-object in C:\xampp\htdocs\Bhaji\fetch_pages.php on line 29
can you please help me out of this
Replythanks for the tutorial the script work perfectly, but the pagination didn’t display.
Replyhow to resolve this.
thanks.
I found only one issue in the codes. Let me explain it here:
In the pagination function, value of given value of $previous variable is
$previous = $current_page – 3;
while it should be
$previous = $current_page – 1;
that’s it. All working good except this small issue,
Thanks,
ReplyRahul
I’m getting the following error,
ReplyFatal error: Call to a member function execute() on a non-object in /home/u111693093/public_html/fetch_pages.php on line 29
Hi Sanwebe,
how I can apply this pagination which i want combine with search data ?
ReplyHi,
Replycan anybody tell me in simple words the complete procedure of pagination with next and previous button that actually works???????????
plz email me the files at [email protected]
Hi, very helpful tutorial.But it will give an error when we click on the link ‘2’.How can I resolve that?
ReplyNavigate to second page and click on the prev link.That is the real problem.
I have the page showing the correct number of results and the correct amount of pagination buttons at the bottom of the page but clicking on the pagination buttons does nothing. Any ideas what could be wrong ?
Replyhow to use double pagination . top of page and bottom of page
ReplyI got the data from database displaying on the page but I did not get the links of page 1,2,3, and so on. Is anyone know how to fix this issue?
Replyi want to setUnicode&characterEncoding=utf8;
Replybut i dont now how to set utf8 :
if you will be help me ,my metter is solve ? how about you ? help?ok
Nice Tutorial.. Thanks a ton…
ReplyNice tutorial. Thanks
ReplyHi Guys I need the pagination without page numbers only like this << >> I need the code wil any body help me
Reply