Home jQuery

Simple jQuery AJAX Pagination with PHP and MySQL – Step By Step Guide!

jquery-tutorial

UPDATE: You should learn not only the AJAX pagination. Learn the PHP CRUD (create, read, update, delete, etc.) operations as well. Go to our PHP, MySQL and AJAX CRUD Tutorial – Step by Step Guide!

Hi there! Today we are going to do a very simple yet useful script for your web apps.

A jQuery AJAX pagination with the help of a PHP pagination class. This is very easy to use and integrate with your project.

This code will load the paginated data via AJAX, we’ll have some loader image to make it look good and user friendly.

Paginated data was returned by our PHP script with the help of a Modified PS_Pagination Class (Yes, I modified it myself since I want to use this Awesome pagination class with jQuery.)

Paginating Your Data with jQuery AJAX and Awesome PHP Pagination Class

We are going to have the following files for today’s code:
1. images/ajax-loader.gif – for our loader animation
2. js/jquery-1.4.js – our favorite javascript library
3. libs/ps_pagination.php – the pagination class I modified
4. styles/style.css – style for our table data and page number navigation
5. db_connect.php – for database connection
6. index.php – our main UI
7. search_results.php – returns the data that will be displayed to index.php

1.0 Prepare our database table and data.

We’re gonna use the following sample table structure and data.

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `email` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=56 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `firstname`, `lastname`, `email`, `username`, `password`, `modified`) VALUES
(28, 'John', 'Dalisay', '', 'john', 'john123', '2012-01-15 07:26:14'),
(39, 'Jem', 'Panlilio', '', 'jemboy09', 'jem123', '2012-01-15 07:26:46'),
(40, 'Darwin', 'Dalisay', '', 'dada08', 'dada123', '2012-01-15 07:25:34'),
(46, 'Jaylord', 'Bitamug', '', 'jayjay', 'jay123', '2012-01-15 07:27:04'),
(49, 'Justine', 'Bongola', '', 'jaja', 'jaja123', '2012-01-15 07:27:21'),
(50, 'Jun', 'Sabayton', '', 'jun', 'jun123', '2012-02-05 10:15:14'),
(51, 'Lourd', 'De Veyra', '', 'lourd', 'lourd123', '2012-02-05 10:15:36'),
(52, 'Asi', 'Taulava', '', 'asi', 'asi123', '2012-02-05 10:15:58'),
(53, 'James', 'Yap', '', 'james', 'jame123', '2012-02-05 10:16:17'),
(54, 'Chris', 'Tiu', '', 'chris', 'chris123', '2012-02-05 10:16:29');

2.0 Create our database connection file

Create db_connect.php file. The following code is how we connect to the database. We are using the PDO extension here.

<?php
// Database credentials
$db_host = "your_db_host";
$db_name = "your_db_name";
$db_username = "your_db_username";
$db_password = "your_db_password";

try {
	// Get database connection
	$conn = new PDO("mysql:host={$db_host};dbname={$db_name}", $db_username, $db_password);
}

// Handle connection error
catch(PDOException $exception){
	echo "Connection error: " . $exception->getMessage();
}
?>

3.0 Prepare your loader image and jQuery

Create “images” folder and put your loader GIF image inside this folder. Generate and download your AJAX loader from the ajaxload.info website.

Create “js” folder. Download jQuery and put it inside the “js” folder.

If you’re not yet familiar with jQuery, I highly recommend studying our jQuery Tutorial for Beginners – Step By Step Guide!

4.0 User interface

Our user interface will be represented by our index.php file.

<!DOCTYPE HTML>
<html>
	<head>
		<title>jQuery AJAX and PHP Pagination Demo - codeofaninja.com</title>

		<!-- Include custom CSS. -->
		<link rel="stylesheet" type="text/css" href="styles/style.css" />

	</head>
<body>

<div id='retrieved-data' style='height:15em;'>
	<!--
	This is where data will be shown.
	-->
    <img src="images/ajax-loader.gif" />
</div>

<script type = "text/javascript" src = "js/jquery-1.7.1.min.js"></script>
<script type = "text/javascript">
$(function(){
	// show the first page
	getdata(1);
});

function getdata(pageno){
	// source of data
	var targetURL = 'search_results.php?page=' + pageno;

	// show loading animation
	$('#retrieved-data').html('<img src="images/ajax-loader.gif" />');

	// load to show new data
	$('#retrieved-data').load(targetURL).hide().fadeIn('slow');
}
</script>

</body>
</html>

5.0 Getting the page data

This will be the code inside our search_results.php file, where our modified pagination class was used.

<?php
// Connect to database.
include 'db_connect.php';

// Include our pagination class / library.
include 'libs/ps_pagination.php';

// Query all data anyway you want
$sql = "select * from users ORDER BY firstname DESC";

/*
	Now, we are going to use our pagination class.
	This is a significant part of our pagination.

	I will explain the PS_Pagination parameters:

	> $conn is a variable from our config_open_db.php
	> $sql is our sql statement above
	> 3 is the number of records retrieved per page
	> 4 is the number of page numbers rendered below
	> null - We used null because we don't have any other parameters to pass

	(i.e. param1=valu1&param2=value2)
	You can use this if you are going to use this class for search results.
	The last parameter is useful because you will have to pass the search keywords.
*/
$pager = new PS_Pagination($conn, $sql, 3, 4, null);

// Our pagination class will render new recordset.
// Search results now are limited for pagination.
$rs = $pager->paginate();

// Count how many rows of data were returned.
$num = $rs->rowCount();

if($num >= 1 ){
	// Create our table header
	echo "<table id='my-tbl'>";
	echo "<tr>";
		echo "<th>Firstname</th>";
		echo "<th>Lastname</th>";
		echo "<th>Email</th>";
	echo "</tr>";

	// Loop through the records retrieved
	while ($row = $rs->fetch(PDO::FETCH_ASSOC)){
		echo "<tr class='data-tr' align='center'>";
		echo "<td>{$row['firstname']}</td>";
		echo "<td>{$row['lastname']}</td>";
		echo "<td>{$row['email']}</td>";
		echo "</tr>";
	}

	echo "</table>";
}else{
	// If no records found
	echo "No records found!";
}

// 'page-nav' CSS class is used to control the appearance of our page number navigation
echo "<div class='page-nav'>";
	// Display our page number navigation
	echo $pager->renderFullNav();
echo "</div>";
?>

6.0 Modified PS_Pagination Class

The previous section will not work without the PS_Pagination class we modified. Create “libs” folder. Open “libs” folder. Create ps_pagination.php file. Put the following code inside it.

<?php
/**
 * PHPSense Pagination Class
 *
 * PHP tutorials and scripts
 *
 * @package		PHPSense
 * @author		Jatinder Singh Thind
 * @copyright	Copyright (c) 2006, Jatinder Singh Thind
 * @link		http://www.phpsense.com
 * @modification		Mike Dalisay
 * @link				https://www.codeofaninja.com
 */

// ------------------------------------------------------------------------

class PS_Pagination {
	var $php_self;
	var $rows_per_page = 10; //Number of records to display per page
	var $total_rows = 0; //Total number of rows returned by the query
	var $links_per_page = 5; //Number of links to display per page
	var $append = ""; //Paremeters to append to pagination links
	var $sql = "";
	var $debug = false;
	var $conn = false;
	var $page = 1;
	var $max_pages = 0;
	var $offset = 0;
	
	/**
	 * Constructor
	 *
	 * @param resource $connection Mysql connection link
	 * @param string $sql SQL query to paginate. Example : SELECT * FROM users
	 * @param integer $rows_per_page Number of records to display per page. Defaults to 10
	 * @param integer $links_per_page Number of links to display per page. Defaults to 5
	 * @param string $append Parameters to be appended to pagination links 
	 */
	
	function PS_Pagination($connection, $sql, $rows_per_page = 10, $links_per_page = 5, $append = "") {
		$this->conn = $connection;
		$this->sql = $sql;
		$this->rows_per_page = (int)$rows_per_page;
		if (intval($links_per_page ) > 0) {
			$this->links_per_page = (int)$links_per_page;
		} else {
			$this->links_per_page = 5;
		}
		$this->append = $append;
		$this->php_self = htmlspecialchars($_SERVER['PHP_SELF'] );
		if (isset($_GET['page'] )) {
			$this->page = intval($_GET['page'] );
		}
	}
	
	/**
	 * Executes the SQL query and initializes internal variables
	 *
	 * @access public
	 * @return resource
	 */
	function paginate() {
		//Check for valid mysql connection
		
		
		//Find total number of rows
		$all_rs = $this->conn->prepare( $this->sql );
		$all_rs->execute();
		
		if (! $all_rs) {
			if ($this->debug)
				echo "SQL query failed. Check your query.<br /><br />Error Returned: " . mysql_error();
			return false;
		}
		$this->total_rows = $all_rs->rowCount();
		
		//Return FALSE if no rows found
		if ($this->total_rows == 0) {
			if ($this->debug)
				echo "Query returned zero rows.";
			return FALSE;
		}
		
		//Max number of pages
		$this->max_pages = ceil($this->total_rows / $this->rows_per_page );
		if ($this->links_per_page > $this->max_pages) {
			$this->links_per_page = $this->max_pages;
		}
		
		//Check the page value just in case someone is trying to input an aribitrary value
		if ($this->page > $this->max_pages || $this->page <= 0) {
			$this->page = 1;
		}
		
		//Calculate Offset
		$this->offset = $this->rows_per_page * ($this->page - 1);
		
		//Fetch the required result set
		$query = $this->sql . " LIMIT {$this->offset}, {$this->rows_per_page}";
		$rs = $this->conn->prepare( $query );
		$rs->execute();
		
		if (! $rs) {
			if ($this->debug)
				echo "Pagination query failed. Check your query.<br /><br />Error Returned: " . mysql_error();
			return false;
		}
		return $rs;
	}
	
	/**
	 * Display the link to the first page
	 *
	 * @access public
	 * @param string $tag Text string to be displayed as the link. Defaults to 'First'
	 * @return string
	 */
	function renderFirst($tag = 'First') {
		if ($this->total_rows == 0)
			return FALSE;
		
		if ($this->page == 1) {
			return "$tag ";
		} else {
			//return '<a href="' . $this->php_self . '?page=1&' . $this->append . '">' . $tag . '</a> ';
            //default to one (1)
            return " <a href='javascript:void(0);' OnClick='getdata( 1 )' title='First Page'>$tag</a> ";
		}
	}
	
	/**
	 * Display the link to the last page
	 *
	 * @access public
	 * @param string $tag Text string to be displayed as the link. Defaults to 'Last'
	 * @return string
	 */
	function renderLast($tag = 'Last') {
		if ($this->total_rows == 0)
			return FALSE;
		
		if ($this->page == $this->max_pages) {
			return $tag;
		} else {
			//return ' <a href="' . $this->php_self . '?page=' . $this->max_pages . '&' . $this->append . '">' . $tag . '</a>';
            $pageno = $this->max_pages;
            return " <a href='javascript:void(0);' OnClick='getdata( $pageno )' title='Last Page'>$tag</a> ";
		}
	}
	
	/**
	 * Display the next link
	 *
	 * @access public
	 * @param string $tag Text string to be displayed as the link. Defaults to '>>'
	 * @return string
	 */
	function renderNext($tag = '&gt;&gt;') {
		if ($this->total_rows == 0)
			return FALSE;
		
		if ($this->page < $this->max_pages) {
			//return '<a href="' . $this->php_self . '?page=' . ($this->page + 1) . '&' . $this->append . '" title=\'next to\'>' . $tag . '</a>';
            $pageno = $this->page + 1;
            return " <a href='javascript:void(0);' OnClick='getdata( $pageno )' title='Next Page'>$tag</a> ";
		} else {
			return $tag;
		}
	}
	
	/**
	 * Display the previous link
	 *
	 * @access public
	 * @param string $tag Text string to be displayed as the link. Defaults to '<<'
	 * @return string
	 */
	function renderPrev($tag = '&lt;&lt;') {
		if ($this->total_rows == 0)
			return FALSE;
		
		if ($this->page > 1) {
			//return ' <a href="' . $this->php_self . '?page=' . ($this->page - 1) . '&' . $this->append . '">' . $tag . '</a>';
            $pageno = $this->page - 1;
            return " <a href='javascript:void(0);' OnClick='getdata( $pageno )' title='Previous Page'>$tag</a> ";
		} else {
			return " $tag ";
		}
	}
	
	/**
	 * Display the page links
	 *
	 * @access public
	 * @return string
	 */
	function renderNav($prefix = '<span class="page_link">', $suffix = '</span>') {
		if ($this->total_rows == 0)
			return FALSE;
		
		$batch = ceil($this->page / $this->links_per_page );
		$end = $batch * $this->links_per_page;
		if ($end == $this->page) {
		//$end = $end + $this->links_per_page - 1;
		//$end = $end + ceil($this->links_per_page/2);
		}
		if ($end > $this->max_pages) {
			$end = $this->max_pages;
		}
		$start = $end - $this->links_per_page + 1;
		$links = '';
		
		for($i = $start; $i <= $end; $i ++) {
			if ($i == $this->page) {
				$links .= $prefix . " $i " . $suffix;                                       
			} else {
				//$links .= ' ' . $prefix . '<a href="' . $this->php_self . '?page=' . $i . '&' . $this->append . '">' . $i . '</a>' . $suffix . ' ';
                //$pageno = $this->page + 1;
                $links .= " <a href='javascript:void(0);' OnClick='getdata( $i )' title='Another page'>$i</a> ";
			}
		}
		
		return $links;
	}
	
	/**
	 * Display full pagination navigation
	 *
	 * @access public
	 * @return string
	 */
	function renderFullNav() {
        //echo $this->renderFirst() . " " . $this->renderPrev();
        
		return $this->renderFirst() . '&nbsp;' . $this->renderPrev() . '&nbsp;' . $this->renderNav() . '&nbsp;' . $this->renderNext() . '&nbsp;' . $this->renderLast();
	}
	
	/**
	 * Set debug mode
	 *
	 * @access public
	 * @param bool $debug Set to TRUE to enable debug messages
	 * @return void
	 */
	function setDebug($debug) {
		$this->debug = $debug;
	}
}
?>

7.0 Style page links

Style your page links, as for this tutorial, I used the following style.css:

<style type='text/css'>         
/*you can change you table style here*/  
#my-tbl {
    background-color: #FFF;
    color: black;
    padding: 5px;
    width: 700px;
    border: thin solid red;
}

#th{
    background-color: #000;
    color: #FFF;
    padding: 10px; 
    border-bottom: thin solid #000;  
}

.data-tr{
    background-color: #FFF;
    color: #000;
    border-bottom: thin solid #000;
}

.data-tr:hover {
    background-color: #FAEFCF;
    color: black;
    padding: 5px; 
    border-bottom: thin solid #000;
       
}

.data-tr td{       
    padding: 10px;            
    margin: 0px;         
}

/* you can change page navigation here*/
.page-nav{
    margin: 10px 0 0 0;
    padding: 8px;
}

.page-nav a{
    border: none;
    padding: 8px;
    text-decoration: none;
    background-color: #FFC;
    border: thin solid #FC0;
    color: #000;
}

.page-nav a:hover{
    border: thin solid #FC0;
    background-color: #FC0;
    color: #fff;
}

/*this is the style for selected page number*/
.page_link{
    padding: 8px;
}
</style>

I decided to delete the old MySQLi and PDO version pages and put it all in this one post. If you want to see the old comments, see MySQLi version old comments and PDO version old comments

Download Source Code Examples

The source code of this tutorial is part of our 30+ useful Web Programming source code package. Each item in the package has its own tutorial like the one above.

If you are just starting out to learn web programming and serious about learning more, this is the right package for you. Click green button below to see the what is included in the package and download it there.


Thanks for reading this jQuery AJAX and PHP pagination example!