Skip to main content

Load Data On Page Scroll with jQuery, PHP & MySQL

You’re well familiar with pagination used in most of web projects to view multiple pages. Pagination provided good user interface but its require to click next or previous button to view pages. You can replace pagination with more user friendly feature to load chunks of records on each time when page scroll. With this, users do not need to click on button, records loaded when page scroll. So in this tutorial you will learn how to load records dynamically when scroll page using Ajax with PHP and MySQL.


Let’s start the Data Load on Page Scroll using jQuery with PHP and MySQL. Before you begin, take a look on files structure. We need following files for this tutorial.

  • db_connect.php
  • index.php
  • load_data.php
  • ajax.js
Create Database Table

We will create table stories to load records to display on page scroll.

CREATE TABLE IF NOT EXISTS `stories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
Create Database Connection

We will create a PHP file db_connect.php to make connection with MySQL database.

<?php
/* Database connection start */
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demos";
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
?>
Create Main File

Now we will create PHP file index.php to create HTML to display dynamically loaded record on page scroll. We will also include load_data.php to load records when page load and remaining records will be displayed when page scroll.

<div class="container">
	<h2>Example: Load Data On Page Scroll Using jQuery and PHP</h2>		
	<div id="messages">
	<?php include('load_data.php'); ?>
	</div>
	<div id="loader" style="text-align:center;"><img src="loader.gif" /></div>		
</div>	
Create JavaScript to Load Data When Page Scroll

Now we will create JavaScript file ajax.js to make Ajax request to server side load_data.php to load records from MySQL database and display. In below code, on page scroll we will get page number details and then call function loadRecords() and pass url with page details to load data.

$(document).ready(function(){ 
	$(window).scroll(function(){
		if ($(window).scrollTop() == $(document).height() - $(window).height()){
			if($(".page_number:last").val() <= $(".total_record").val()) {
				var pagenum = parseInt($(".page_number:last").val()) + 1;
				loadRecords('load_data.php?page='+pagenum);
			}
		}
	});	
});

JavaScript function loadRecords() to call on page scroll to make Ajax request to server to load.

function loadRecords(url) {
	$.ajax({
		url: url,
		type: "GET",
		data:  {total_record:$("#total_record").val()},
		beforeSend: function(){
			$('#loader').show();
		},
		complete: function(){
			$('#loader').hide();			
		},
		success: function(data){			
			$("#messages").append(data);
		},
		error: function(){} 	        
	});
}
Get Records from MySQL with HTML

Now finally in load_data.php, we will get records from MySQL database according Ajax request to load data and return data as HTML.

<?php
include_once("db_connect.php");
$perPage = 3;
$sql = "SELECT id, description FROM stories";
$page = 1;
if(!empty($_GET["page"])) {
	$page = $_GET["page"];
}
$start = ($page-1)*$perPage;
if($start < 0) $start = 0;
$query =  $sql . " limit " . $start . "," . $perPage; 
$resultset = mysqli_query($conn, $query) or die("database error:". mysqli_error($conn));
$records = mysqli_fetch_assoc($resultset);
if(empty($_GET["total_record"])) {
	$_GET["total_record"] = mysqli_num_rows($resultset); 
}
$message = '';
if(!empty($records)) {
	$message .= '<input type="hidden" class="page_number" value="' . $page . '" />';
	$message .= '<input type="hidden" class="total_record" value="' . $_GET["total_record"] . '" />';
	while( $rows = mysqli_fetch_assoc($resultset) ) {
		$message .= '<div  class="well">' .$rows["description"] . '</div>';
	}
}
echo $message;
?>

You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download