Home jQuery

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

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

You can download all the code used in this tutorial for only $9.99 $5.55!

THANK YOU!

has been added to your cart!

Powered by Easy Digital Downloads

Thank you!

have been added to your cart!

Powered by Easy Digital Downloads

Thank you for learning from our post about: jQuery AJAX and PHP pagination example!

RELATED TUTORIAL: PHP, MySQL and AJAX CRUD Tutorial – Step by Step Guide!

Home jQuery

Check and Validate Username in jQuery

Hi there developer! Today we are going to do a code snippet that:

  1. Checks if a username is available or not (in the database).
  2. If the username is available, the program will output “[your_username] is available!”
  3. Then if it is not available, “Username already taken” will be printed.
  4. This code also has a simple validation that states whether the inputted username is too short or is empty.

All those tasks will be performed via AJAX, so it is without page refresh. You can expand its validation though.

In this code, we need a sample data from the database table and four (4) files which includes: config_open_db.php, index.php, check.php and the jQuery library file.

database – you could have this table structure and data:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

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

INSERT INTO `users` (`id`, `firstname`, `lastname`, `username`, `password`) VALUES
(1, 'John', 'Doe', 'johnny', 'john'),
(2, 'Albert', 'Minston', 'albert', 'albert');

config_open_db.php – this file is for database connection so that we’ll be able to load usernames from the database. You should have something like this: (Please supply the variables with YOUR settings.)

<?php
$host = "localhost";
$db_name = "your_db_name";
$username = "your_db_username";
$password = "your_db_password";

//connect to mysql server
$mysqli = new mysqli($host, $username, $password, $db_name);

//check if any connection error was encountered
if(mysqli_connect_errno()) {
    echo "Error: Could not connect to database.";
    exit;
}
?>

index.php – this file makes our user interface for this tutorial. jQuery library is of course included in this file.

<html>
	<head>
		<title>Username checker and validator</title>
	</head>
<body>

<p>
	<div id = "feedback"></div>
	<form name = 'form'>
		<input type = 'text' id = 'username_input' name = 'username' >
	</form>(try 'johnny', 'albert', 'chris' or 'james')
</p>

<div class='back-link'>
	<a href='https://www.codeofaninja.com/2011/06/check-and-validate-username-without.html'>Back to tutorial page</a>
</div>

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

	// when the document is ready, run the jquery script
	$(document).ready(function(){
		$('#feedback').load('check.php').show();

		/* We use keyup so that everytime the user type in the keyboard, it'll check the database and get results however,
		  you can change this to a button click which is I think, more advisable.
          Sometimes, your server response is slow but just for this demo, we'll use 'keyup' */
		$('#username_input').keyup(function(){

			$.post('check.php', { username: form.username.value },
				function(result){
					$('#feedback').html(result).show();
				});

		});
	});
</script>

</body>
</html>

check.php – this file makes the request to the database, to check the inputted value.

<?php
include_once("config_open_db.php");

isset( $_POST['username'] ) ? $username = $username = $mysqli->real_escape_string( $_POST['username'] ) : $username = "";
if($username == null){
	echo "Please enter a username.";
}

elseif(strlen($username) < 5){
	echo "Username is too short.";
}

else{
	$sql = "SELECT *
			FROM users
			WHERE username = \"{$username}\"";

	$result = $mysqli->query($sql);
	$num = mysqli_num_rows($result);
	if($num == 1 ){
		while($row = mysqli_fetch_array($result)){
			$fn = $row['firstname'];
			$ln = $row['lastname'];
			echo "<div style='color: red; font-weight: bold;'>Username already taken.</div>";
		}
	}else{
		echo "<span style='font-weight: bold;'>$username</span> is available!";
	}
}
?>

Download jQuery Code Examples

You can download all the code used in this tutorial for only $9.99 $5.55!

THANK YOU!

has been added to your cart!

Powered by Easy Digital Downloads

Thank you!

have been added to your cart!

Powered by Easy Digital Downloads

Related Tutorials

Thank you for learning from our post about Check and Validate Username in jQuery.

display-facebook-photos-on-website-1

How To Display Facebook Page Photo Albums on Website Using PHP?

display-facebook-photos-on-website-1
Before we start coding, let me ask you this: Do you really want to code this feature on your website? Or would you prefer to use a website plugin that does the trick instantly?

There is an easy way to customize & embed Facebook photo albums on your website. No coding required. How? Use the SociableKIT! Learn how to use it from this Step by step tutorial.

If you need to display single Photo Album from a Facebook page, SociableKIT can do it as well! How? Learn from this step by step tutorial.

Going back to coding...

Do you have a photo manager on your website? Do you upload photos on your Facebook page as well?

If your answer is both YES, you're wasting a lot of time managing your brand's photos online.

What if there's a way to upload your photo albums ONCE, and then let it appear both on your Website and Facebook page?

Will you be happy saving your precious time?

Read more

Home Android

A Happy Developer, Android + CakePHP + jQuery

Hi there! Well, I don't have much posts few weeks ago since I'm kinda busy working on a project. I got good news and not-so-bad news, haha! This past few weeks, I've been working on an Android application which makes me so happy (though at first, kinda stressed, since I'm a beginner on this). Yes, it has been my dream to be a Java programmer since college. I can't believe that now, one of my dreams is coming true (Since Android is Java based).
A Happy Developer, Android + CakePHP + jQuery
There's more :)

I can say that now, I'm comfortable with developing an Android application. It is total object oriented, powerful, has lots of possibilities, useful, extensible, and almost anything you can think of. It can be integrated with other technologies like Web App Development (which is also what I'm working on now). Most of all, it is continuously developed by increasing hundreds of thousands of other developers around the world (I'm happy and grateful to be one of them). Another great thing is that, there are over 400,000 Android devices were activated everyday.
I'm gonna tell you something about my current project. The Android application is run in a tablet device (I have Samsung Galaxy Tab, Android Froyo for now. But I'm delighted by it.). The Android App I'm doing is some sort of a content management system that syncs data from an encrypted XML stream. Those data include images, slideshows, flash games, videos, audios, websites, survey forms, sales data, etc. The XML stream is of course generated dynamically by a Web based content management system which is developed with some web development technologies like HTML5CakePHP and jQuery
Sounds simple right? But like any other software project, it will be from simple to complex. We will discover more about the client's business logic. The user of this application is in the retail industry. The Android App will be installed in tablet devices in different parts of the country (where they have market places).
Here's the not-so-bad news: The web based CMS is developed by other programmer. And so now, it is assigned to be developed by me. So I got a lot of work to do. I thought I will concentrate on Android development, but it seemed like web app development always haunt me haha! Anyway the good news there is that, I'll have a lot of knowledge to share with you guys and of course the open-source community.
As of now, I'm the only programmer of this project. Good thing is I'm with a Systems Analyst who helps a lot. But the team will expand sooner or later. :)
Just a piece of advice, to those who want to learn language frameworks such as CakePHP (for PHP) and jQuery (for JavaScript), you have to learn first its basic or native language: PHP and JavaScript. This will give you great development advantage. :)
And oh, I got a late special announcement (or at-least for me), as you've noticed, you accessed this blog in a top level domain: https://www.codeofaninja.com which is before http://www.codeofaninja.blogspot.com. It's my dream to own a ".com" website before, and now, it just came true last June 2, 2011. I registered https://www.codeofaninja.com at Go Daddy.
I'm also so happy to see that there are increasing number of traffic on this site (based on this blog's google analytics account). It makes my goal to help and share knowledge to other people in different parts of the world.