jQuery: AJAX pagination with PHP

  • jQuery
  • Gabriele Romanato

AJAX pagination is made up of two components: a server-side script that processes the AJAX requests on each link and the client-side code that actually send the requests. In this tutorial we're going to use PHP for the server-side part and jQuery for making AJAX requests. The choice of PHP and jQuery is irrelevant for our purposes: the important thing here is to understand how pagination works.

How pagination works

In SQL ( we're going to use the MySQL dialect here ) a LIMIT clause can specify two things:

  1. If used with a single parameter, it specifies the total number of results to fetch.
  2. If used with two parameters ( comma-separated ), the first parameter indicates the start offset and the second parameter the total number of results to fetch.

Consider the following MySQL queries:


SELECT * FROM posts ORDER BY id DESC LIMIT 3;
SELECT * FROM posts ORDER BY id DESC LIMIT 3,3;

The first query selects the first three posts from the database, while the second one moves the start offset ( which starts at 0 by default ) by 3 positions and specifies that the total number of posts must be 3.

So the offset sequence for the two queries will be as follows:

  1. 0, 1, 2
  2. 3, 4, 5

Pagination uses the second type of query. The start offset changes dynamically and is calculated with the following formula:

offset = ( pageNumber * resultsPerPage ) - resultsPerPage

So if we are on page 2, the start offset in our case will be 3 ( ( 2 * 3 ) - 3 ). If we are on page 3, the start offset will be 6 ( ( 3 * 3 ) - 3 ). And so on.

Using PHP for processing AJAX requests

PHP processes all the GET requests sent by jQuery by performing the following steps:

  1. Validates the page parameter by making sure that is a valid integer.
  2. Calculates the start offset.
  3. Runs the MySQL query.
  4. Returns and outputs the results as an HTML string.

The first step is data validation:


$start = 0;
$end = 3; // Results per page
$value = 0; // Offset to be calculated


if( isset( $_GET[ 's'] ) ) {

	$taintedStart = $_GET[ 's' ];
	
	// We take 2 just for the sake of the example

	if( strlen( $taintedStart ) <= 2 ) {
	
	    // Raw conversion

		$s = intval( $taintedStart );
	

        // Is it a valid integer?
        
		if( filter_var( $s, FILTER_VALIDATE_INT ) ) {

			if( $s > $start ) { // Greater than 0?
				$start = $s;
			}

		

		}


	}

}

Then we calculate the start offset:


$value = ( $start * $end ) - $end;

Finally, we run the query and output the resulting HTML string:


$db = new DB( 'host', 'username', 'password', 'database' );
$posts = $db->getResults( "SELECT * FROM posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY id DESC LIMIT $value,$end" );


$html = '';

foreach( $posts as $post => $content ) {
	$postContent = $content['post_excerpt'];
	$postTitle = $content['post_title'];
	$date = strtotime( $content['post_date'] );
	$postDate = strftime( '%d-%m-%Y', $date );

	$html .= sprintf( '<h3>%s</h3><small>%s</small><p>%s</p>', $postTitle, $postDate, $postContent );
}

echo $html;

Using jQuery to make AJAX requests

Compared to the PHP code, the jQuery code required for our task is really simple:


(function( $ ) {
	$(function() {
		$( "a", "#pagination" ).on( "click", function( e ) {
			e.preventDefault();
			var $a = $( this );

			$a.addClass( "current" ).
			siblings().
			removeClass( "current" );

			var page = $a.data( "page" );
			$.get( "ajax.php", { s: page }, function( html ) {
				$( "#content" ).html( html );

			});
		});

	});

})( jQuery );

We simply run a GET request by passing the page number stored on each link as a data attribute as the sole parameter.

Demo

The following video shows our AJAX pagination in action.

Code

You can find the code used in this tutorial on GitHub.

Back to top