Data Load While Page Scroll with jQuery PHP and MySQL

When we have more data records to display on a page, we used pagination to display just few records when page load and display next few records on every next click. You can replace your traditional pagination with more user friendly feature to load records dynamically on same page while page scroll. Users don't need to click on button to view more records; the records will be loaded on each page scroll without page load. So in this tutorial you will learn how to implement dynamic data load on page scroll using jQuery, PHP and MySQL. The tutorial is covered in very easy steps with live demo and complete script to download.

So let’s start the coding. Before begin, take a look on files structure used for this tutorial.

• Index.php


• result_data.php


• load_data.js

Step1: Create Database Table

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=1 ;


Step2: Create Database Connection
We will create db_connect.php file to make connection with MySQL database to display records.
<?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();
}
?>


Steps3: Include Bootstrap and JavaScript Files
In this tutorial we have created HTML using Bootstrap, so we include Bootstrap files and jQuery in head tag in index.php.
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script type="text/javascript" src="script/load_data.js"></script>


We will also include result_data.php in index.php to load record dynamically on page when page load.
<div class="container">
<h2>Example: Data Load While Page Scroll with jQuery PHP and MySQL</h2>
<div id="results">
<?php include('result_data.php'); ?>
</div>
<div id="loader" style="text-align:center;"><img src="loader.gif" /></div>
</div>


Steps4: Load Data on Page Scroll using jQuery Ajax
Now we will load data records dynamically on page when page scroll using jQuery Ajax. For this we have created JavaScript file load_data.js to make Ajax call on page scroll to result_data.php to get data records dynamically from MySQL database and display these.

$(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;
loadRecord('result_data.php?page='+pagenum);
}
}
});
});
function loadRecord(url) {
$.ajax({
url: url,
type: "GET",
data: {total_record:$("#total_record").val()},
beforeSend: function(){
$('#loader').show();
},
complete: function(){
$('#loader').hide();
},
success: function(data){
$("#results").append(data);
},
error: function(){}
});
}


Steps5: Get Records from MySQL Database
Finally in result_data.php, we will get records dynamically from MySQL database according to jQuery Ajax request to load data and return data as HTML.
<?php
include_once("db_connect.php");
$perPage = 3;
$sql_query = "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_query . " 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 [sociallocker]Download[/sociallocker]