Skip to main content

Load Data On Page Scroll with jQuery, PHP & MySQL

Updated on: webdamn PHP, Tutorials

You’re well familiar with the pagination used with large data in web projects to view records in multiple pages. The Pagination provides quick solution for large data and good user interface but its needs to click next or previous button to view pages. Suppose if you want to view previous page then you need to click again to load previous page.

So if you’re looking for more user friendly solution to replace pagination, then you’re at right place. You can replace pagination with more user friendly feature to load chunks of records on each time when page scroll. With this feature, users do not need to click on next or previous button, the records are loaded when page scroll.

In this tutorial you will learn how to load more records dynamically when scroll page using Ajax with PHP and MySQL. We will cover this tutorial in easy steps with live demo.


Also, read:

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

  • db_connect.php
  • index.php
  • load_data.php
  • ajax.js

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

2. 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>

3. 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(){} 	        
	});
}

4. 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 may also like:

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

2 thoughts on “Load Data On Page Scroll with jQuery, PHP & MySQL”

Comments are closed.