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

Our HTML page contains a DIV container called “results”, where we will append records returned from server. Another element contains a loading image, which we will show while the results are loading and will be replaced with a message once we reach the end of record.

1
2
3
4
<div class="wrapper">
    <ul id="results"><!-- results appear here --></ul>
    <div class="loading-info"><img src="ajax-loader.gif" /></div>
</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, 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
var track_page = 1; //track user scroll as page number, right now page number is 1
var loading  = false; //prevents multiple loads

load_contents(track_page); //initial content load

$(window).scroll(function() { //detect page scroll
    if($(window).scrollTop() + $(window).height() >= $(document).height()) { //if user scrolled to bottom of the page
        track_page++; //page number increment
        load_contents(track_page); //load content  
    }
});    
//Ajax load function
function load_contents(track_page){
    if(loading == false){
        loading = true;  //set loading flag on
        $('.loading-info').show(); //show loading animation
        $.post( 'fetch_pages.php', {'page': track_page}, function(data){
            loading = false; //set loading flag off once the content is loaded
            if(data.trim().length == 0){
                //notify user if nothing to load
                $('.loading-info').html("No more records!");
                return;
            }
            $('.loading-info').hide(); //hide loading animation once data is received
            $("#results").append(data); //append data into #results element
       
        }).fail(function(xhr, ajaxOptions, thrownError) { //any errors?
            alert(thrownError); //alert with HTTP error
        })
    }
}

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. You saved the day………..

     Reply
  2. 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();
      }
      ?>

  3. Work for 100.000++ mysql db ?

     Reply
  4. 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
  5. please explain how the same can be done.. when we fetch the data from json Array not from mysql-php..

     Reply
  6. nice tutorial guys

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

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

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

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

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

     Reply
  12. Thanks fot this example…gotta try it

     Reply
  13. 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
  14. 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
  15. 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: stephen.aryee@yahoo.com

     Reply
  16. 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 – stephen.aryee@yahoo.com

     Reply
  17. 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
  18. 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
  19. hi your code is right but is not working in mobile please solution on this code

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

     Reply
  21. 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
  22. Nice Script. Thanks for making it easier for the developers.

     Reply
  23. 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
  24. 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
  25. 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
  26. 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()){

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

  28. thanks man, its very in useful.

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

     Reply
  30. 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
  31. Can you help me . I can’t run sample with device mobile .

     Reply
  32. hey guy thank you so much its very useful

     Reply
  33. 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
  34. 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
  35. Thanks you for share it. It will be help me build a mobile site for my website http://www.socialbangla.com.

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

     Reply
  37. 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
  38. I test it’s good in PC Website but not run in Mobile site? I don’t know why?

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

     Reply
  40. 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
  41. 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
  42. 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
  43. how set charset=utf-8

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

     Reply
  45. 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
  46. 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
  47. thank you man , this is the best site ever !

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

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