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

In 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
9
10
11
12
13
14
<?php
$db_username = 'xxxxx';
$db_password = '';
$db_name = 'xxxxxx';
$db_host = 'localhost';
$item_per_page = 5;

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

?>

Index Page

Create a UL element with ID “results”, this is where we are going to display the results fetched from server. You can wrap it with a DIV element, it will give you more control when you style them with CSS:

1
2
3
<div class="wrapper">
        <ul id="results"><!-- results appear here as list --></ul>
</div>

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 remaining data when user scrolls to bottom of the page.

Just make sure, the height of initially loaded content is greater than the height of page itself for scroll to work. Which means if there’s no vertical scrollbar in browser, you might want to increase $item_per_page number in PHP config script.

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
(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!', //no more records to load
            data_url        : 'fetch_pages.php', //url to PHP page
            start_page      : 1 //initial page
        }, options);
       
        var el = this; 
        loading  = false;
        end_record = false;
        contents(el, settings); //initial data load
       
        $(window).scroll(function() { //detact scroll
            if($(window).scrollTop() + $(window).height() >= $(document).height()){ //scrolled to bottom of the page
                contents(el, settings); //load content chunk
            }
        });    
    };
    //Ajax load function
    function contents(el, settings){
        var load_img = $('<img/>').attr('src',settings.loading_gif_url).addClass('loading-image'); //create load 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
            $.post( settings.data_url, {'page': settings.start_page}, function(data){ //jQuery Ajax post
                if(data.trim().length == 0){ //no more records
                    el.append(record_end_txt); //show end record text
                    load_img.remove(); //remove loading img
                    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 content
                settings.start_page ++; //page increment
            })
        }
    }

})(jQuery);

$("#results").loaddata(); //load the results into element

Fetching Database Records

The page number passed to PHP script using jQuery $.post() method in script above, is used to determine the position of the records in database query as you can see in PHP script below. I am using MySqli Prepared Statements to fetch the records, which is very secure and faster way. If you are confused or curious about MySqli, you can see PHP MySqli Basic usage.

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

Download Demo

  1. Iam getting this error:
    Warning: mysqli_stmt::bind_param():Number of variables doesn’t match number of parameters in prepared statement

     Reply
  2. Please can you give a tutorial on comment on a post as well as post likes.
    Will be very glad. Great post

     Reply
  3. Iam getting this error:
    Warning: mysqli_stmt::bind_param():Number of variables doesn’t match number of parameters in prepared statement in my root directory\fetch_pages.php on line 20

    Please can you help me fix it.

     Reply
  4. Iam getting this error:
    Warning: mysql_stmt::bind_param():Number of variables doesn’t match number of parameters in prepared statement in my root directory\fetch_pages.php on line 20

     Reply
  5. This is a bug, when scroll at bottom untikl no data and then refresh page, and then scroll to the bottom again until no data, and see the numbers is skipped alot

     Reply
  6. When u refresh at the bottom of the page, some rows are missing…

     Reply
  7. what if i wanna create overflow x:scroll and make it the trigger for autoload?

     Reply
  8. what should i do if i wanna change from scroll overflow y to scroll overflow x ??
    iam trying
    if($(window).scrollBottom() + $(window).width() >= $(document).width())

     Reply
  9. Very Nice tutorial. i want to use it for http://howcanisolve.com . is it work with all browsers?

     Reply
  10. Thanks for sharing! :)

    From
    Uganda, East Africa

     Reply
  11. Great tutorial

     Reply
  12. thanks for the post,,,,great tutorial

     Reply
  13. Nice and easy! Thanks!

     Reply
  14. Found script jumping records on fast scrolling
    So modified this

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    $(window).scroll(function() { //detect page scroll
         if($(window).scrollTop() + $(window).height() &gt;= $(document).height()) { //if user scrolled to bottom of the page
              function checkVariable() {
                   if(loading == false){
                       track_page++; //page number increment
                       load_contents(track_page); //load content   
               }
              }
              $('.loading-info').show(); //show loading animation
              setTimeout(checkVariable,1000);  
         }
    });
     Reply
  15. suggestion for fade effect on display

    $(data).hide().appendTo(“#results”).fadeIn(1000);

     Reply
  16. That nice, do you have “Auto Load More Data” plugin for wordpress?
    If not, I need to convert your code to wordpress plugin.
    Anyway thanks,

     Reply
  17. This artical was more helpful to me. Thank you!

     Reply
  18. hi, I hope you make the same tutorial for CodeIgniter. thanks

     Reply
  19. its not working for me
    hope u can check and tell me the error
    the page is not loading by itself whn i try to inspect (ctrl+shift+I) it loads

     Reply
  20. Thanks a lot for all tutorials. are awesome. I learn a lot.

     Reply
  21. hi friend i i don’t know how to correct the error of ajax utf8 decode because my all my data are with arabic language and i see result like this ??????????? if someone have solution please tell me and thank you

     Reply
    • Try using mysqli_set_charset($conn, “utf8”); before your mysqli_query.

      $conn is the mysqli_connect().

  22. Nice post save my time to get this point.

     Reply
  23. Does anyone know how to do the same using a database in postgresql instead of MySQL?
    Any website or Manual?
    I tried to use this one and change it but it is not working.

    config.php
    connect_error) {
    die(‘Error : (‘. $db->connect_errno .’) ‘. $db->connect_error);
    }
    ?>

    Not really sure on how to change the autoload_process.php and index.php

    I just changed the $mysqli for $db.

    THANKS

     Reply
  24. works well on opera n firefox but does not work on chrome

     Reply
  25. Hi I have a problem, im using bootstrap and using pills/tabs, now when I go to other tab, the window height function seems not working. can you suggest any thing to do?

     Reply
  26. Please, where should i save the PHP file?

     Reply
  27. Hi,
    Thanks for sharing code, it very helpful,. i have one problem, this load more i need center page. when i am going footer section it will works,.

     Reply
  28. Good, Could you please let me know if I want to load pages on scroll let say 3 pages on scroll at a time and then there is button to click to load more and then on click 1 page can be loaded until there is some content on the page?? Thanks

    Regards
    Sateesh
    http://www.savedimes.com

     Reply
  29. Great tutorial. Made my day. J

    It would be good to have a ‘show more’ button after scrolls down. But, it doesn’t load more records if, on the first screen, if there’re not enough records (example, items_per_group= 2) to enable first scroll. This behavior can be tacked.

    Great post. Thx

     Reply
  30. this file won’t download
    please fix it or email it to please
    Thank you
    Sam

     Reply
  31. Nice tutorial by the way. save my day

     Reply
  32. will this work on MVC framworks like CodeIgniter?

     Reply
  33. It can be better if You can give alto scroll for 3 scroll downs after tat a show more button to load further data

     Reply
Message Type : Question Comment ?
  • Question : Can include code, jsFiddle, codePen etc using Markdown Syntax.
  • Comment : Short comments and questions.