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

111 Comments Add Comment

  • You saved the day………..

     Reply
  • Hey , i just trying to load my own data from sql , i changed
    //Limit our results within a specified range.
    $results = $mysqli->query(“SELECT id,title,introtext,fulltext FROM r04g8_content”);
    in autoload_process.php
    and
    $results = $mysqli->query(“SELECT COUNT(*) as t_records FROM r04g8_content”);
    in index.php

    but its not laoding nothing plus no error just blank page, whats the problem ?

     Reply
    • Your index.php code seems to be fine.

      But, you have not used any limit clause in ‘autoload_process.php”. Omitting limit clause doesn’t make sense because we don’t want to load all the records on the first page load (eager load).

      Try below code in “autoload_process.php”.

      $results = $mysqli->prepare(“SELECT id,title,introtext,fulltext FROM r04g8_content LIMIT $position, $items_per_group”);
      $results->execute(); //Execute prepared Query
      $results->bind_result($id, $title, $introtext, $fulltext); //bind variables to prepared statement

      echo ”;
      while($results->fetch()){ //fetch values
      echo ”.$id.’) ‘.$title.”.$introtext.”.$fulltext.”;
      }
      echo ”;
      $mysqli->close();
      }
      ?>

  • Work for 100.000++ mysql db ?

     Reply
  • hey what if we wanna do it the Facebook Messeges way ?
    ie, when we scroll up more messages are loaded. How they catch our scroll ?

     Reply
  • please explain how the same can be done.. when we fetch the data from json Array not from mysql-php..

     Reply
  • nice tutorial guys

     Reply
  • It’s very nice content given by you. I would like to suggest to other developer also.
    Thank you.

     Reply
  • Hi, this is for mysqli, you can post for mysql to? thanks

     Reply
    • hello
      if this can help you, i have test this on MySQL and it’s working, change just the configuration with another one

  • Can anyone please tell me how to unable it for mobile? This code is not working for mobile

     Reply
  • I can not download, can someone pass me another place to do. Already tried everything on this site.

     Reply
  • Thank you so much ….Its very helpful for my project

     Reply
  • Thanks fot this example…gotta try it

     Reply
  • I’m having a major problem with this… The code works perfectly if my autoload.php is inside of my htdocs on XAMPP… But when I put it in a folder, it doesn’t work at all. I’ve checked that I’ve got the urls correct and file permissions are set to everyone read and write and still, XAMPP returns a 500 Internal Server Error in “Inspect Element” on Firefox… What can I do now? I have created like 10 or so of these types of php files and I don’t want to clutter up the htdocs…

     Reply
  • Hello
    thanks for this tutorial
    I have a problem maybe lot of people the same, if you show the 5 results on a page with no scroll it’s impossible to show something more, we have to reduce the window to appear the scrollbar et after this work,
    Someone can tell me how to scroll directly ?
    thanks

     Reply
  • how can make the “Auto Load More Data On Page Scroll” load current (NOW) data that was posted into the database without refreshing the page. i need HELP: [email protected]

     Reply
  • How can the “AUTO LOAD” load my current post immediately I make a Post or comments without refreshing Page.
    Please someone help me out: this is my email – [email protected]

     Reply
  • thanks for the great tutotial, i just wanna know if it is possible to send multiple data to the autoload php script on the first load.. i need to send a username to get specific data back.. THanks in Advance

     Reply
  • great work…
    I need more help,
    i want to add 1 more feature in this… i m displaying the database records in descending order,,, if new record is added to database the list should get updated using ajax without reloading the page,, n new record should get appended at top.

    Can anyone help me out or any reference please.
    Thanks.

     Reply
  • hi your code is right but is not working in mobile please solution on this code

     Reply
  • Great Script.
    Working , My doubt is can we fetch results in array instead of object.

     Reply
  • Just a small correction on jQuery Line 6
    $(‘#results’).load(“autoload_process.php”, {‘page’:track_load}, function() {track_load++;}); //load

    the parameter name should be ‘group_no’ not ‘page’

    great tutorial thanks a lot!

     Reply
  • Nice Script. Thanks for making it easier for the developers.

     Reply
  • Please help me in for what will be the code in case of a mssql database in back end and classic ASP page in front end.

     Reply
  • Little bit make me confuse during experiment. But finally I found the problem. It because your script at demo above is differ from the downloaded script. Specially for
    $(‘#results’).load(“autoload_process.php”, {‘page’:track_load}, at line 6.
    it should be the same as line 19.
    $.post(‘autoload_process.php’,{‘group_no’: track_load}, –>” page above vs group_no”

    Thanks for the nice tutor. I love the way you teaching wold….

     Reply
  • Thank you for beset tutorial and demo,

    When i use your code , when i load page and scroll up, donw,up, donw,up, donw …. speed

    I have duplicate data , How can i do that ?

     Reply
  • Mobile autoload default web browse solved problem:

    1
    2
    3
    $(window).scroll(function() {
            if($(window).scrollTop() + 20 &gt;= $(document).height() - $(window).height()){
                if(track_load &lt; total_groups &amp;&amp; loading==false){
     Reply
    • $(window).scroll(function() {
      if($(window).scrollTop() + 20 >= $(document).height() – $(window).height()){
      if(track_load < total_groups && loading==false){

    • I change only this line and all working true.
      if($(window).scrollTop() + $(window).height() + 20 >= $(document).height()){

  • Hi, first off thanks for providing such a cool tutorial – its great.
    I’ve got this up and working on my site – it returns 80 x 80 px images in a nice grid – 20 at a time.
    The whole thing is working fine except for one thing. I can’t seem to turn off the numbering for each listed result? is this something that needs to be done in the jquery?

    Sorry if I’m being a doof and I’ve missed something spectacularly simple.

    My results look like this:
    echo ‘default_icon.'” width=”80″ height=”80″ />’

    Yet I’m still picking up the numbering for some reason?

    Any help would be much appreciated – cheers guys

     Reply
    • Hi Ryan, hopefully you have solved this problem by now, but just incase you haven’t the results are in an ordered list. If you change the to that should solve your problem!

  • thanks man, its very in useful.

     Reply
  • It’s very nice content given by you. I would like to suggest to other developer also.
    Thank you.

     Reply
  • I have to spend my weekend on trying to load data when the user scroll down a page using javascript. I was able to achieve the this results when the user scroll down. But the page continue to load even when the user scroll up the page which i don’t want. I just found this article and am going to try it out. Thanks Bro

     Reply
  • Can you help me . I can’t run sample with device mobile .

     Reply
  • hey guy thank you so much its very useful

     Reply
  • Hey Saran Chamling !!
    this is a very helpful article.
    I ‘ve followed the above mentioned code in my project and i got the necessary results.

    Thanks a lot for sharing a useful code !!

     Reply
  • Hey Saran Chamling !! this is a very helpful article. I ‘ve followed the above mentioned code in my project and i got the necessary results.

    Thanks a lot for sharing a useful code !!

     Reply
  • Thanks you for share it. It will be help me build a mobile site for my website http://www.socialbangla.com.

     Reply
  • thanks for the post, it has helped me so much ! thank a lot.

     Reply
  • I assume the current content body height should be longer than the screen height at the very beginning when the page is launched. Coz if there is no scroll bar then the page will not scroll. :)

     Reply
  • I test it’s good in PC Website but not run in Mobile site? I don’t know why?

     Reply
  • I can not load javascript in getcontent.php …
    how to get javascript from getcontent.php? ..

     Reply
  • First Thanks a lot man, Secondly I’m new to javascript and I’d like to know how can I get the script to load the data before hitting the very bottom of the page, like when the user scrolls around 200px from the bottom of the screen.

     Reply
  • jQuery line 6 and line 19 are different. It should be both ‘page’ or ‘group_no’.
    Also i think line 13 in jQuery

    1
    if(track_load <= total_groups && loading==false)

    should be

    1
    if(track_load < total_groups && loading==false)

    .
    Otherwise if track_load is 1 and you only have one total_group the script tries to load data.

     Reply
  • Thanks a lot. This post is very helpful for me.

    But I found a syntax error on this page.

    In the jquery source part, there is a different json attribute name between line 6 and 19.

    please check it.

     Reply
  • how set charset=utf-8

     Reply
  • not work for IE10 can you help me? please.

     Reply
  • Hey man!

    Thank you so much for your tutorial….its very comprehensive and greatly helpful.

    Quick question, if I instead wanted to detect and analyze the scrolling for a div rather than the whole document, how would the jQuery have to be changed to accomplish this? I have a type of height detection algorithm for the if statement, but how would I change the existing code from “window” to the div for instance.

     Reply
  • I have been trying this one out using plain javascript and Ajax, it’s been such a pain, thanks man… it was so helpful

     Reply
  • thank you man , this is the best site ever !

     Reply
  • Great article, helped me out a lot!
    I’ve just got one problem that I’m hoping you might be able to help me with.

    I’m using your script inconjuction with jQuery Mobile, and when the new posts are ajax’d in, they’re un-styled. I know this is because the jQuery mobile library has already loaded on the main Index page, but is there a way to get it to ‘reload’ when the new ajax content is being brought in?

    Just something I’m having a bit of trouble with at the moment.

    Thanks, Ross

     Reply
    • $(‘.loadedData’).trigger(‘pagecreate’);
      where loadedData is for example element which contains loaded data. Hope this helps to everybody.