Simple Ajax Pagination with jQuery & PHP

Pagination can get complicated depending on the size of records in the database, especially when you want to group number of links, display next/previous links etc. In this article we’ll only focus on creating simple ajax based pagination using jQuery load() method. I am pretty sure its the most simplest example you can find on the net.

Configuration

Let’s start with configuration file to store our MySql database username and password, and also to connect to MySQL using PHP mysqli_connect. If you notice we are using PHP mysqli_connect() here, not regular mysql_connect(), i = stands for improved extension, developed to take advantage of new features found in newer MySQL systems. PHP encourages everyone to use mysqli_* instead of regular mysql_* extensions, which will be totally removed in the future PHP versions.

Anyways! mysqli_connect() isn’t that different, here’s our configuration file :

1
2
3
4
5
6
7
8
9

<?php
$db_username = 'root';
$db_password = '';
$db_name = 'demo';
$db_host = 'localhost';
$item_per_page = 5;

$connecDB = mysqli_connect($db_host, $db_username, $db_password,$db_name)or die('could not connect to database');
?>

Front Page

Generating Pagination Links

Index page is where you usually want to display the pagination links. Before we can generate links, first we need to get number of pages (total record / item per page = number of pages), and then Using PHP FOR loop, we can generate basic pagination links as shown in example below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

<?php
include("config.inc.php");

$results = mysqli_query($connecDB,"SELECT COUNT(*) FROM paginate");
$get_total_rows = mysqli_fetch_array($results); //total records

//break total records into pages
$pages = ceil($get_total_rows[0]/$item_per_page);

//create pagination
$pagination = '';
if($pages > 1)
{
$pagination .= '<ul class="paginate">';
for($i = 1; $i<$pages; $i++)
{
$pagination .= '<li><a href="#" class="paginate_click" id="'.$i.'-page">'.$i.'</a></li>';
}
$pagination .= '</ul>';
}

?>

You see, it’s very simple! If we weren’t using jQuery, we would be creating actual hyperlinks to pages, the whole next/previous thing, but thanks to jQuery, things are simple. Since we now have the pagination links ready, we can easily display it anywhere we want like this : echo $pagination;.

Using jQuery load() method

jQuery load() is an excellent AJAX method to load data from server. We’ll utilize this powerful yet simple method to perform the pagination task without refreshing the page, we’ll also be adding some basic effects like loading animation, changing active link background etc.

When user clicks on pagination link, we will get its value using jQuery .text(), the value is our page number, which needs to be sent to fetch_pages.php.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

$(document).ready(function() {
$("#results").load("fetch_pages.php", {'page':0}, function() {$("#1-page").addClass('active');}); //initial page number to load

$(".paginate_click").click(function (e) {

$("#results").prepend('<div class="loading-indication"><img src="ajax-loader.gif" /> Loading...</div>');

var clicked_id = $(this).attr("id").split("-"); //ID of clicked element, split() to get page number.
var page_num = parseInt(clicked_id[0]); //clicked_id[0] holds the page number we need

$('.paginate_click').removeClass('active'); //remove any active class

//post page number and load returned data into result element
//notice (page_num-1), subtract 1 to get actual starting point
$("#results").load("fetch_pages.php", {'page': (page_num-1)}, function(){

});

$(this).addClass('active'); //add active class to currently clicked element

return false; //prevent going to herf link
});
});

Displaying Results and Pagination Links

We have PHP & jQuery codes ready to generate pagination links and fetch records from the database, all we need to do is place below codes within the body of the page.

<?php echo $pagination; ?> displays the pagination links and <div id="results"></div> to dump the Ajax results.

1 2	<?php echo $pagination; ?> <div id="results"></div>

Fetching Pages

When the page number is passed to “fetch_pages.php” using jQuery .load() method, we need to get starting point of record, and number of records to display per page. The MySQL LIMIT clause can be used to limit the results we want from the database, we’ll just pass these two values as arguments, and have our records fetched, that’s it!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

<?php
include("config.inc.php"); //include config file

//sanitize post value
$page_number = filter_var($_POST["page"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH);

//validate page number is really numaric
if(!is_numeric($page_number)){die('Invalid page number!');}

//get current starting point of records
$position = ($page_number * $item_per_page);

//Limit our results within a specified range.
$results = mysqli_query($connecDB,"SELECT id,name,message FROM paginate ORDER BY id DESC LIMIT $position, $item_per_page");

//output results from database
echo '<ul class="page_result">';
while($row = mysqli_fetch_array($results))
{
echo '<li id="item_'.$row["id"].'">'.$row["id"].' <span class="page_name">'.$row["name"].'</span><span class="page_message">'.$row["message"].'</span></li>';
}
echo '</ul>';
?>

Conclusion

You can use above examples to create your own sophisticated ajax driven pagination, below you’ll find downloadable files and link to demo page. And also you might find my other similar post helpful — loading more results dynamically. Good Luck!

Download Demo

9 Thoughts

sangeetha April 10, 2013 at 11:28 am

i need previos last for jquery database pagination

Reply ↓
John April 14, 2013 at 7:50 am

Hi, Your tutorials r very nice. but in this tutorial, its not displaying correct values in the 1st page. it showing from 5th id. Please check and update. Both your demo, downloaded files, and my working example r working same from 5th id on 1st page. I hope you got my point.

Reply ↓
- Saran April 15, 2013 at 6:32 am
  
  Thanks, it is fixed now.
Stefan Cosma April 21, 2013 at 4:17 pm

Hey Saran,

Great tutorial for anyone who wants to learn how to paginate. But there is one little issue. You should change the line:

1
for($i = 1; $i<$pages; $i++)

to this

1
for($i = 1; $i<=$pages; $i++)

I found that if there is an odd number of entries, the last page is not displayed. For example if you have 50 entries and the pagination is set to 6 items per page, the 9th page is not displayed, thus the correction! That way the last page is displayed.

Have a great day!

Reply ↓
- Saran April 21, 2013 at 4:34 pm
  
  Thanks, appreciated!
- DjLexsas May 16, 2013 at 6:47 am
  
  Thats right i was looking for mistake also and now i found it what was wrong there. And thanks a lot for great tutorial
Dan May 1, 2013 at 8:45 am

How to add “preview” and “next”?

Reply ↓
- DjLexsas May 17, 2013 at 7:22 am
  
  You just need to change pagination PHP code into your owns
DjLexsas May 17, 2013 at 7:30 am

Saran maybe can you make tutorial like how to make simple AJAX chat with pagination. I tried to make by my own but it was terrible. Because when I want to setInterval() so when I change Page so after 10 seconds for example from page 10 it moves back to 1 page. Maybe can you help with this?

Sorry for my bad English.

Best Regards,
Modestas

Reply ↓