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.
MYSQL
  • 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.
PHP
  • 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:
HTML
  • 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.
JQUERY
  • 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.
PHP
  • 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