» » Auto Load More Data On Page Scroll (jQuery/PHP)

Auto Load More Data On Page Scroll (jQuery/PHP)


previous article – Loading More Results (jQuery/PHP), the records are loaded when the users clicked on “Load more Data” button, which is great but how about loading database records automatically when user scrolls down to the bottom of the page? The technique can be seen in Twitter, Facebook and several other websites. Let’s use examples from previous article, with some modification we can create Ajax based auto loading script, which loads records when user scrolls to bottom of the page.

auto-load-records-on-scroll

MySQL Table

Run MySql query below to create a table in MySql for this demo. You can also find a sql file in download, which you can import in PhpMyAdmin.
 
1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `paginate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `message` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=51 ;

Configuration

Let’s start with a configuration file to store our MySql database username and password.
 
1
2
3
4
5
6
7
8
<?php
$db_username = 'xxxxx';
$db_password = '';
$db_name = 'xxxxxx';
$db_host = 'localhost';
$items_per_group = 5;
$mysqli = new mysqli($db_host, $db_username, $db_password,$db_name);
?>

Index Page

Before we write our jQuery code, we need to know the total records in the table, and break them into page numbers.
 
1
2
3
4
5
6
7
<?php
include("config.php");
$results = $mysqli->query("SELECT COUNT(*) as t_records FROM paginate");
$total_records = $results->fetch_object();
$total_groups = ceil($total_records->t_records/$items_per_group);
$results->close();
?>

jQuery

I have made few changes in jQuery code below. The .click() method is replaced with .scroll(), our code can now detect the page scroll, and auto loads additional data when user scrolls to bottom of the page.
 
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
$(document).ready(function() {
    var track_load = 0; //total loaded record group(s)
    var loading  = false; //to prevents multipal ajax loads
    var total_groups = <?php echo $total_groups; ?>; //total record group(s)
   
    $('#results').load("autoload_process.php", {'page':track_load}, function() {track_load++;}); //load first group
   
    $(window).scroll(function() { //detect page scroll
       
        if($(window).scrollTop() + $(window).height() == $(document).height())  //user scrolled to bottom of the page?
        {
           
            if(track_load <= total_groups && loading==false) //there's more data to load
            {
                loading = true; //prevent further ajax loading
                $


class="br0">('.animation_image').show(); //show loading image
               
                //load data from the server using a HTTP POST request
                $.post('autoload_process.php',{'group_no': track_load}, function(data){
                                   
                    $("#results").append(data); //append received data into the element

                    //hide loading image
                    $('.animation_image').hide(); //hide loading image once data is received
                   
                    track_load++; //loaded group increment
                    loading = false;
               
                }).fail(function(xhr, ajaxOptions, thrownError) { //any errors?
                   
                    alert(thrownError); //alert with HTTP error
                    $('.animation_image').hide(); //hide loading image
                    loading = false;
               
                });
               
            }
        }
    });
});

HTML

HTML code below should be placed within the body tag of the page, the results will appear inside the results element, and loading animation should appear only when user scrolls down to the bottom.
 
1
2
3
4
5
<ol id="results">
</ol>
<div class="animation_image" style="display:none" align="center">
<img src="ajax-loader.gif">
</div>

loading Process

Create a PHP file with code below, when the group number is passed to “autoload_process.php” using jQuery .post() method, we need starting point of record, and number of records to display per group. The MySQL LIMIT clause can be used to limit the results, we’ll just pass these two values as arguments, and have our records fetched.
 
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
<?php
include("config.php"); //include config file

//sanitize post value
$group_number = filter_var($_POST["group_no"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH);

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

//get current starting point of records
$position = ($group_number * $items_per_group);

//Limit our results within a specified range.
$results = $mysqli->query("SELECT id,name,message FROM paginate ORDER BY id ASC LIMIT $position, $items_per_group");

if ($results) {
    //output results from database
   
    while($obj = $results->fetch_object())
    {
        echo '<li id="item_'.$obj->id.'">'.$obj->id.' - <strong>'.$obj->name.'</strong></span> &mdash; <span class="page_message">'.$obj->message.'</span></li>';
    }

}
unset($obj);
$mysqli->close();
?>

Related Posts :

0 Response to "Auto Load More Data On Page Scroll (jQuery/PHP) "

Post a Comment

Subscribe to: Post Comments (Atom)