Easy pagination with jQuery and Ajax

Thursday, 15 October 2009


In this tutorial I would like to go over an easy way to display your database data with jQuery using Ajax and pagination.

If you would like a brief introduction to using Ajax with jQuery you can have a look at this tutorial. jQuery makes it very easy to send Ajax requests.

You can view the demo of this tutorial here

You can download the files for this tutorial here

Pagination is the process of chunking up your data into fixed sizes so that you only view one fixed size chunk at a time and can navigate easily backwards and forwards through other chunks. You can see this process on sites like digg and ebay. Usually the navigation links show the first page, the last page, a next and previous link and a small range of page numbers. The pagination links in this tutorial will look something like this:


Pagination allows your users to digest information in small, easily digestible pieces and with a familiar interface for navigating backwards and forwards through that information.

Using Ajax we can provide a way to navigate through larger amounts of information loading one page at a time. This should reduce the load on the server and minimise page reloading times. The use of Ajax should be weighed up as it is not appropriate for all sites.

Lets start by thinking what our page will look like. We will have our information (displayed one page at a time) and another section to display which page we are currently on and navigation to other pages. I will name the two secions #content and #paginator. The main aim is to update the #content information each time someone clicks on a #paginator page link. So we start with three main functions:


function initialize() {
// Display first page

// Display initial status of page links with the first page selected

}

function displayPage(current_page) {
// Retrieve data from table equivelant to page size (for this example 10 rows) based on which page we are currently on (current_page) and return formatted rows
}

function displayLinks(current_page) {
// Create page links with first and last links based upon which page we are currently on (current_page)

// Attach event listeners to our page links
}


Now lets think about where each of these functions should reside; Since we want our program to retrieve the data on the fly and we are going to use Ajax we will have our displayPage functionality sitting in the backend (whatever your preffered language, I will be using php). We will place our initialize function in javascript so that it can load the pages and I have decided since the page links are essentially the same element and require little processing I will have javascript render the page links also. We will still need to have another function in the javascript to make the Ajax request to the php file whenever a page link is clicked. So lets have another look at our functions.


/*** Javascript - page.js ***/

function initialize() {
// Load first page into #content

// Call displayLinks(1)
}

function displayLinks(current_page) {
// Create / refresh page links with first and last links based upon which page we are currently on (current_page)

// Attach event listeners to our page links which will call updatePage function when clicked
}

function updatePage(clicked_element) {
// Get the current_page based upon the text of the selected element

// Call php displayPage(current_page) function via Ajax to get data and load this into #content section

// Update links on the page by calling displayLinks(current_page)
}


/*** php - loaddata.php***/

function displayPage(current_page) {
// Get the names from the database by calling $data = getNames(current_page)

// Format raw data by calling $formatted_data = formatData($data)

// Return $formatted_data
}

function getNames(current_page) {
// Retrieve data from table equivelant to page size (for this example 10 rows) based on which page we are currently on using SQL

// Return rows
}

function formatData(data) {
// We are simply going to attach <p> tags to the columns 'firstname lastname' for each row then return this as a single string
}


We now have most of our pseudocode written out and we should be able to start coding from here. With a little bit of css the finished product will be a simple, good looking and easy to use Ajax program that will show us pages of names, 10 at a time.

The glue that is going to hold our application together is jQuery. If you are unfamiliar with jQuery I suggest you begin by having a look at this tutorial Getting started with jQuery. jQuery will supply many of the methods we need for getting the joby done in an easy straight forward way.

There is on last function we are missing. In order to display a link to the last page we will need to retrieve the page count from the database. As this needs to be used each time displayLinks is called we will retrieve this from the database once and store it in a hidden input element on the page.

It's probably best if we start with php file so that you can know what we the data will look like when it is retrieved by our Ajax calls.


/*** php - loaddata.php***/

<?php
if (isset($_REQUEST['action'])) {
$action = $_REQUEST['action'];

switch ($action) { // We will determine which function to call based on an action paramater passed in through the url
case 'get_rows':
displayPage();
break;
case 'row_count':
getRowCount(); // Return the number of rows in the database
break;
default;
break;
}

exit;
} else {
return false;
}

// Returns the number of rows from the table we are querying
function getRowCount() {
$db = array(
// Database connection details
);

$link = mysql_connect($db['host'], $db['login'], $db['password']);

mysql_select_db($db['database']);

$strSQL = "SELECT COUNT(*) FROM names";
$result = mysql_query($strSQL);
$count = mysql_fetch_row($result);

echo $count[0];
}


// Retrieves raw data from the database then formats it and then returns the formatted rows as a string
function displayPage() {
$start_row = isset($_REQUEST['start'])?$_REQUEST['start']:0;
$start_row = 10 * (int)$start_row;

$employees = getNames($start_row);

$formatted_names = "<div id='formatted_eployees'>" . formatData($employees) . "</div>";

echo $formatted_names;
}

// Retrieves rows from the names table
function getNames($start_row = 0) {
$db = array(
// Database connection details
);

$link = mysql_connect($db['host'], $db['login'], $db['password']);

mysql_select_db($db['database']);

$strSQL = "SELECT * FROM names ORDER BY id ASC LIMIT {$start_row}, 10"; // Returns 10 rows based on the current row

$result = mysql_query($strSQL);

$employees = array();

while ($row = mysql_fetch_assoc($result)) {
$employees[] = $row;
}

return $employees;
}

// Returns the rows as a formatted string
function formatData($data) {
$formatted = '';
foreach ($data as $dat) {
$formatted .= '<p>' . $dat['firstname'] . ' ' . $dat['lastname'] . '</p>';
}
return $formatted;
}

?>


It should be clear now what our backend script is doing. It has two main functions which will be called depending on the action variable passed in through the URL. Either returning the number of rows in the table or returning our 10 rows of formatted data as a string based upon which is our current row. If you would like to be able to call the pagintation SQL string using MSSQL here is the code:


$strSQL = "
SELECT TOP 10 * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber FROM names
) AS results
WHERE RowNumber > {$start_row}";


Now lets have a look at the javascript file:


/*** Javascript - page.js ***/

$("document").ready(function() {
// Load first page into #content
$("#content").load("loaddata.php?action=get_rows");

// Call displayLinks(1)
$.get("loaddata.php?action=row_count", function(data) {
$("#page_count").val(Math.ceil(data / 10)); // Store the number of pages in the hidden input #page_count
displayLinks(1);
});
}

// Create / refresh page links with first and last links based upon which page we are currently on (current_page)
function displayLinks(current_page) {
var pages = $("#page_count").val();

if (pages <= 5) {
$("#content").after("<div id='paginator'><a href='#' class='pagor selected'>1</a><a href='#' class='pagor'>2</a><a href='#' class='pagor'>3</a><a href='#' class='pagor'>4</a><a href='#' class='pagor'>5</a><div style='clear:both;'></div></div>");
$(".pagor").click(function() {
var index = $(".pagor").index(this);
$("#content").load("loaddata.php?action=get_rows&start=" + index);
$(".pagor").removeClass("selected");
$(this).addClass("selected");
});
} else {
if (current_page < 5) {
// Draw the first 5 then have ... link to last
var pagers = "<div id='paginator'>";
for (i = 1; i <= 5; i++) {
if (i == current_page) {
pagers += "<a href='#' class='pagor selected'>" + i + "</a>";
} else {
pagers += "<a href='#' class='pagor'>" + i + "</a>";
}
}
pagers += "<div style='float:left;padding-left:6px;padding-right:6px;'>...</div><a href='#' class='pagor'>" + Number(pages) + "</a><div style='clear:both;'></div></div>";

$("#paginator").remove();
$("#content").after(pagers);

// Attach event listeners to our page links which will call updatePage function when clicked
$(".pagor").click(function() {
updatePage(this);
});
} else if (current_page > (Number(pages) - 4)) {
// Draw ... link to first then have the last 5
var pagers = "<div id='paginator'><a href='#' class='pagor'>1</a><div style='float:left;padding-left:6px;padding-right:6px;'>...</div>";
for (i = (Number(pages) - 4); i <= Number(pages); i++) {
if (i == current_page) {
pagers += "<a href='#' class='pagor selected'>" + i + "</a>";
} else {
pagers += "<a href='#' class='pagor'>" + i + "</a>";
}
}
pagers += "<div style='clear:both;'></div></div>";

$("#paginator").remove();
$("#content").after(pagers);

// Attach event listeners to our page links which will call updatePage function when clicked
$(".pagor").click(function() {
updatePage(this);
});
} else {
// Draw the number 1 element, then draw ... 2 before and two after and ... link to last
var pagers = "<div id='paginator'><a href='#' class='pagor'>1</a><div style='float:left;padding-left:6px;padding-right:6px;'>...</div>";
for (i = (Number(current_page) - 2); i <= (Number(current_page) + 2); i++) {
if (i == current_page) {
pagers += "<a href='#' class='pagor selected'>" + i + "</a>";
} else {
pagers += "<a href='#' class='pagor'>" + i + "</a>";
}
}
pagers += "<div style='float:left;padding-left:6px;padding-right:6px;'>...</div><a href='#' class='pagor'>" + pages + "</a><div style='clear:both;'></div></div>";

$("#paginator").remove();
$("#content").after(pagers);

// Attach event listeners to our page links which will call updatePage function when clicked
$(".pagor").click(function() {
updatePage(this);
});
}
}
}

function updatePage(clicked_element) {
// Get the current_page based upon the text of the selected element
var selected = $(clicked_element).text();

// First update content
$("#content").load("loaddata.php?action=get_rows&start=" + (selected - 1));

// Then update links
displayLinks(selected);
}



Ok, that looks like a lot to look at, but I'll break it down into smaller chunks.

The intialize function is now $("document").ready according to jQuery. This function is almost identical to our pseudocode so I don't think I need to go over it in detail. It retrieves and then stores the number of pages. It then loads the first pages content and lastly it loads in the page links section.

Our big one is the display links function and I think I should write this out into pseudocode so that we can understand it a bit better:


function displayLinks(current_page) {
// Retrieve the maximum page from the hidden element and store it in var pages
var pages = $("#page_count").val();

// If the number of pages is less than five:
// Always display links 1 2 3 4 5
// When someone clicks on 1 2 3 4 5 {
// Get the index of the clicked on element
// Switch the selected class to the clicked on element
// Load page data according to the index of the clicked on element
}

// If the number of pages is more than five:
// We are going to have three different cases:
// 1. If we are on the first five links display like this 1 2 3 4 5 ... (last_element)
// 2. If we are on the last five links display like this 1 ... (last - 4) (last - 3) (last - 2) (last - 1) (last_element)
// 3. Otherwise display like this 1 ... 17 18 [19] 20 21 ... (last_element)


// Each of our three steps above does the following:

// Store our links in the var pagers

// Remove paginator from the screen
$("#paginator").remove();

// Attach pagers after the #content section
$("#content").after(pagers);

// Attach event listeners to our links
$(".pagor").click(function() {
updatePage(this);
});
}


Finally the updatePage function does three things. Gets the index of the clicked on link. Then updates the content and links.

You can now have a look at how it all comes together here.
blog comments powered by Disqus