Loading More Results From Database Using jQuery Ajax
Written by Saran on March 6, 2013, Updated June 5, 2017
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
<?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
- 1
- 2
- 3
<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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
(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 elementFetching 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
<?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>';
}
