Loading More Results From Database Using jQuery Ajax

You might have seen the implementation of Ajax based data loading system at Facebook, Twitter or some other sites. Instead of showing pagination links, we can load data dynamically whenever a load button click. In this article I’ll show you how we can easily adopt this technique to make similar loading page for our web projects using jQuery. jQuery Load More Data
You may also want to checkout similar tutorial Auto Load More Data On Page Scroll. In my earlier post Simple Ajax Pagination, I have explained how to create Ajax Pagination using jQuery. Here we will be doing precisely the same thing.

Configuration

We use this configuration file to store certain variables such as MySql username and password, which is required by our program..
PHP
12345678910111213
<?php
$db_username = 'root';
$db_password = '';
$db_name = 'demo';
$db_host = 'localhost';
$item_per_page = 5;

//Try to connect to database or display error
$mysqli = new mysqli($db_host, $db_username, $db_password, $db_name);
if ($mysqli->connect_error) { //Output any connection error
    die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}
?>

Index Page

Since we will be sending data as <LI> list item, we can create a <UL> element with id “results”, here we will load our data fetched from server.
HTML
123
<div class="wrapper">
		<ul id="results"><!-- results appear here as list --></ul>
</div>

jQuery

Here’s our jQuery code, when page loads we need to load the first set of data, once it’s loaded we can add a button which can initiate the remaining data loading until all the records are loaded. You can also change the settings options such as loading image URL, button label etc.
JQUERY
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455
(function($){	
	$.fn.loaddata = function(options) {// Settings
		var settings = $.extend({ 
			loading_gif_url	: "ajax-loader.gif", //url to loading gif
			end_record_text	: 'No more records found!', //end of record text
			loadbutton_text	: 'Load More Contents!', //load button text
			data_url 		: 'fetch_pages.php', //url to PHP page
			start_page 		: 1 //initial page
		}, options);
		
		var el = this;	
		loading  = false; 
		end_record = false;		
		
		//initialize load button
		var load_more_btn = $('<button/>').text(settings.loadbutton_text).addClass('load-button').click(function(e){ 
			contents(el, this, settings); //load data on click
		});
		
		contents(el, load_more_btn, settings); //initial data load	
	}; 
	
	//Ajax load function
	function contents(el, load_btn,  settings){
		var load_img = $('<img/>').attr('src',settings.loading_gif_url).addClass('loading-image'); //loading image
		var record_end_txt = $('<div/>').text(settings.end_record_text).addClass('end-record-info'); //end record text
			
		if(loading == false && end_record == false){
			loading = true; //set loading flag on
			el.append(load_img); //append loading image
			
			//temporarily remove button on click
			if(load_btn.type === 'submit' || load_btn.type === 'click'){
				load_btn.remove(); //remove loading img
			}
			
			$.post( settings.data_url, {'page': settings.start_page}, function(data){ //jQuery Ajax post
				if(data.trim().length == 0){ //if no more records
					el.append(record_end_txt); //show end record text
					load_img.remove(); //remove loading img
					load_btn.remove(); //remove load button
					end_record = true; //set end record flag on
					return; //exit
				}
				loading = false;  //set loading flag off
				load_img.remove(); //remove loading img 
				el.append(data).append(load_btn);  //append content and button
				settings.start_page ++; //page increment
			})
		}
	}

})(jQuery);

$("#results").loaddata(); //load the results into element
As you can see we are utilizing jQuery.post() to load data from server, which is a shorthand Ajax function of $.ajax().

Fetching Pages

As I discussed earlier, we need to pass the page number to the PHP script below, it then will connect to database and fetch the records using page number and “item_per_page” offset. The PHP code uses MySqli Prepared Statements to fetch the records, which is very resilient against harmful injection practices, and the performance is also highly efficient. You can learn more about MySqli Prepared Statements here.
PHP
12345678910111213141516171819202122232425262728
<?php
include("config.inc.php"); //include config file
//sanitize post value
$page_number = filter_var($_POST["page"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH);

//throw HTTP error if page number is not valid
if(!is_numeric($page_number)){
	header('HTTP/1.1 500 Invalid page number!');
	exit();
}

//get current starting point of records
$position = (($page_number-1) * $item_per_page);

//fetch records using page position and item per page. 
$results = $mysqli->prepare("SELECT id, name, message FROM paginate ORDER BY id DESC LIMIT ?, ?");

//bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)
//for more info https://www.sanwebe.com/2013/03/basic-php-mysqli-usage
$results->bind_param("dd", $position, $item_per_page); 
$results->execute(); //Execute prepared Query
$results->bind_result($id, $name, $message); //bind variables to prepared statement

//output results from database

while($results->fetch()){ //fetch values
	echo '<li>'.$id.') <strong>'.$name.'</strong> : '.$message.'</li>';	
}

Wrap up

That’s it! we should have a neat jQuery based data loading page by now. Don’t forget to check out the demo and download the whole thing on your computer. Good luck! If you want to make Ajax Pagination with Next-Previous buttons, you can check out this updated article — Ajax Pagination with jQuery & PHP. Download Demo