Ajax pagination using PHP, MySQL and jQuery

Written by pelister. Posted in Jquery, PHP

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.

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.

DEMO / DOWNLOAD

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.

1 Star2 Stars3 Stars4 Stars5 Stars (22 votes, average: 4.59 out of 5)
Loading...
pelister
3D modeler and Web designer. Founder of Techlister. Love to build sites using WordPress and Joomla. Interested in Astronomy, project member of SETI (Search for Extra Terrestrial Intelligence).

Tags: , , , , , , ,

Comments (9)

  • Bijay Luitel

    |

    Its is very simple but very useful script.

    Reply

  • agustri

    |

    nice article. and thank for share.

    Reply

  • Elias

    |

    Thank you for this script ! is nice and clean 🙂

    Reply

  • Masum

    |

    Thanks for this script.

    Reply

  • aime

    |

    hello i’ve tried the demo it’s just wonderful only when i lauch it locally it still takes me to the website demo..what do i need to do to make it run on localhost ?

    Reply

  • aime

    |

    i forgot to mention that i downloaded all the files and created table page_data and inserted two more rows only i can’t see them since the demo doesn’t take account of my local config and database thank u in advance

    Reply

  • aime

    |

    my problem is solved i just had to adjust the href attribute for js and css and also replace index animal by data 😉

    Reply

    • pelister

      |

      Glad. everything fine for you.

      Reply

  • David

    |

    The best AJAX upload script Ever!

    Reply

Leave a comment

Techlister© - 2012