It is easy to store and retrieve data from MySQL database with few lines of PHP code. But the real headache comes when we want to display the data in an organized design. Lets take a sample scenario, you have some thousand rows of data in your database – displaying them as a HTML table will result in thousand rows of a single table, imagine how long will you have to scroll the page to reach the end of the table, even worse, how will you find a particular data within those thousand rows. A simple solution is to display them as pages, each page with limited number of rows.

Ajax pagination using PHP, MySQL and jQuery – In this tutorial I am going to write few lines of code that will fetch data from MySQL database and display them as pages, we will be using jQuery to monitor the click on page navigation and loading the table onto the browser page with Ajax.

DEMO / Ajax Pagination (311 downloads)

Download contains all the files used in creating the demo ( html, php, css, jquery and databases .sql dump )

1. First create a database – ( name the db whatever you like ).

2. then create a table and insert values with the sql statement provided below.

CREATE TABLE IF NOT EXISTS `page_data` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`animal` varchar(25) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

Now the above SQL statement creates a table called `page_data` with two columns `id` and `animal`

3. Now insert the values into the table using the SQL statement below

INSERT INTO `page_data` (`id`, `data`) VALUES
(1, 'lion'), (2, 'tiger'), (3, 'elephant'), (4, 'panther'),
(5, 'giraffe'), (6, 'deer'), (7, 'zebra'), (8, 'gazelle'), (9, 'hippo'),
(10, 'bear'), (11, 'hyena'), (12, 'cheetah'), (13, 'wolf'), (14, 'fox'), 
(15, 'reindeer'), (16, 'alligator'), (17, 'crocodile');

Ok, Ok, don’t laugh at me, I couldn’t come up with some other data, I was watching discovery channel and that had an impact on the data.
Now we have the table and data. Here is the jQuery part that monitors the click event and fetches the table accordingly.

jQuery

	$( document ).ready(function() {
//Display Loading Image - Animated Gif
function display_anim( )
{
$( '#loading' ).fadeIn( 200,0 );
$( '#loading' ).html( '<img src="images/ajax-loader.gif" />' );  
}
//Hide Loading Image
function hide_anim( )
{
$( '#loading').fadeOut( 'slow' );
};
//initialise the table at the first run
display_anim( );
$( '#content' ).load( "pagination.php?page=1", hide_anim( ) );
//Page navigation Click
$( document ).on( 'click', '#pages li',  function( ) {
display_anim( );
//get the id of clicked li
var pageNum = $(this ).attr('id');
//Loading Data
$( "#content" ).load( "pagination.php?page=" + pageNum, function( ) {
//display the table with fadeIn effect.
$( this ).hide( ).fadeIn( 500 );
hide_anim( );
});
});
});

The above jQuery has 2 functions ( display_anim() and hide_anim() ), which displays the animated gif before loading the data and hides it after loading the data.

$( document ).on( 'click', '#pages li',  function( ) {

This is the line that monitors the click event on list elements, and when click occurs on `li` we get the id of the element , which is our page number and send the page number to pagination.php that fetches the data from database and displays them as table.

Here is the PHP ( pagination.php )

<?php
include('dbconfig.php');
$per_page = 5; 
//get the total number of records ( rows ) for calulating pagination
$sql = "select * from page_data";
$rs = mysql_query( $sql );
$count = mysql_num_rows( $rs );
//calculate number of pages
$pages = ceil( $count / $per_page );
$page = isset( $_GET[ 'page' ] ) ? $_GET[ 'page' ] : 0 ;
//set the starting point 
$start = ( $page - 1 ) * $per_page;
//find the curpage - so you can set a class to highlight the navigaion
$curpag = ( $start == 0 ) ? 1 : ( $start / $per_page ) + 1 ; 
//fetch the table contents
$sql = "select * from page_data order by id limit $start, $per_page";
$rs = mysql_query( $sql );
//display the table
echo "<div id='mytable'><table>";
while( $row = mysql_fetch_array( $rs ))
{
$id = $row[ 'id' ];
$animal = $row[ 'animal' ];
echo "<tr><td>" . $id . "</td><td>" . $animal . "</td></tr>";
} 
echo "</table>";
echo '<div id="navig"><ul id="pages">';
for( $i = 1; $i <= $pages; $i++ )
{
if( $i == $curpag )
echo '<li class="curpage" id="'.$i.'">'.$i.'</li>';
else
echo '<li id="'.$i.'">'.$i.'</li>';
}
echo '</ul></div></div>'; 
?>

The PHP file and jQuery or the major part in this Ajax pagination, and they are self explanatory. I have also added current page identification so that the current page number will highlighted in the navigation. You can download the completed code that is used in the demo at the beginning of this article.

Ajax pagination using PHP, MySQL and jQuery

Leave a Reply

Your email address will not be published. Required fields are marked *