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..

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.

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.

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 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.

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>';
}

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

78 Comments Add Comment