Have you ever needed to paginate your data (blog posts, links, images or anything else) using jQuery but were a little unsure of just how to achieve the effect? In this post, I’m going to show you just how easy it can be. Let’s decide on a good example to demonstrate pagination. How about a simple commenting system?. Awesome..now lets begin.

 

First, lets create a new database table to store each comment’s ID and comment text.



The SQL

CREATE TABLE comments

(

cmt_id INT PRIMARY KEY AUTO_INCREMENT,

comment TEXT

);



Next, we use some jQuery to load up the pagination. This can switch between pages depending on what page number is being requested by your UI.

jQuery >>pagination.js

The jQuery that controls the pagination controls

$(document).ready(function()

{

//Display Loading Image
function Display_Load()

{

$("#loading").fadeIn(900,0);

$("#loading").html("<img src="loadAnm.gif" />");

}
//Hide Loading Image
function Hide_Load()

{

$("#loading").fadeOut(‘slow’);

};


//Default Starting Page Results
$("#pagination li:first")

.css({‘color’ : ‘#FF0084′}).css({‘border’ : ‘none’});

Display_Load();

$("#content").load("pagination_data.php?page=1", Hide_Load());
//Pagination Click
$("#pagination li").click(function(){

Display_Load();
//CSS Styles
$("#pagination li")

.css({‘border’ : ‘solid #dddddd 1px’})

.css({‘color’ : ‘#0063DC’});



$(this)

.css({‘color’ : ‘#FF0084′})

.css({‘border’ : ‘none’});
//Loading Data
var pageNum = this.id;

$("#content").load("pagination_data.php?page=" + pageNum,Hide_Load());

});



});



We’re also going to need to use some PHP for all our server-side comms. Lets setup a simple configuration file to allow us to connect up to the mySQL database we’re using to store our comments.

PHP >> config.php

Adjust the usual hostname, user, password and database for the data source configuration.

<?php

$mysql_hostname = "localhost";

$mysql_user = "username";

$mysql_password = "password";

$mysql_database = "database";

$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) 

or die("Opps some thing went wrong");

mysql_select_db($mysql_database, $bd) 

or die("Opps some thing went wrong");

?>



Lets calculate the number of pages needed from our comments. You can define as many items per page here as you would like.

PHP >> pagination.php

<?php

include(‘config.php’);

$per_page = 10; 



//Calculating no of pages

$sql = "select * from comments";

$result = mysql_query($sql);

$count = mysql_num_rows($result);

$pages = ceil($count/$per_page)

?>



<script type="text/javascript" src="http://ajax.googleapis.com/ajax/

libs/jquery/1.3.0/jquery.min.js"></script>

<script type="text/javascript" src="pagination.js"></script>


<div id="loading" ></div>
<div id="content" ></div>
<ul id="pagination">
<?php
//Pagination Numbers

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

{

echo ‘<li id="’.$i.‘">’.$i.‘</li>’;

}

?>
</ul>



And finally, here’s the mySQL and PHP to handle our complete set of pagination queries.

PHP >> pagination_data.php

A PHP Script that will display content from the database

<?php

include(‘config.php’);

$per_page = 9; 

if($_GET)

{

$page=$_GET['page'];

}



$start = ($page-1)*$per_page;

$sql = "select * from comments order by cmt_id limit $start,$per_page";

$result = mysql_query($sql);

?>





<table width="800px">

<?

while($row = mysql_fetch_array($result))

{

$cmt_id=$row['cmt_id'];

$comment=$row['comment'];

?>

<tr>

<td><?php echo $cmt_id; ?></td>

<td><?php echo $comment; ?></td>

</tr>

<?

}

?>

</table>



CSS Code

CSS stylesheet for the page

/*Pagination Stylesheet*/

#loading

{ 

width: 100%; 

position: absolute;

}

li

{ 

list-style: none; 

float: left; 

margin-right: 16px; 

padding:5px; 

border:solid 1px #dddddd;

color:#0063DC; 

}

li:hover

{ 

color:#FF0084; 

cursor: pointer; 

}