Infinite page scroll is a type of pagination where the user does not need to click on a link to load dynamic data. The actual data from the server is automatically loaded while scrolling down the page. Therefore, the Infinite scrolling is a user-friendly way to load the extra content on a web page. So, It is the best replacement of pagination links for autoloading dynamic content from the server.
Load data on a scroll Use Ajax to load external content from the database using jQuery. Therefore, when the user goes at the end of the web page, retrieve the data from the MySQL database, and load the new content into the web page, scrolling down the page. In this tutorial, we will explain to you how to load data on page scroll using jQuery, Ajax, PHP, and MySQL.
The following functionality will be implementing load data on the page scroll script.
- Fetch and list data from the database using PHP and MySQL.
- Detect page scroll using jQuery.
- Make AJAX request to load data from the server.
- Display dynamic content under pre-loaded data..
Create Database Table
There is a need to create a table in the database to store the post information. The following SQL creates a posts table with some primary fields in the MySQL.
CREATE TABLE `posts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1=Active, 0=Inactive',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Database Configuration (dbConfig.php)
To use the dbConfig.php file, you have to connect and select the MySQL. Therefore, specify the database hostname ($dbHost), password ($dbPassword), username ($dbUsername), and name ($dbName) as per your MySQL credentials.
<?php
//DB details
$dbHost = 'localhost';
$dbUsername = 'root';
$dbPassword = 'root';
$dbName = 'allsweb';
//Create connection and select DB
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
if ($db->connect_error) {
die("Unable to connect database: " . $db->connect_error);
}
?>
Data List (index.php)
Firstly, a limited number of posts data will be retrieved from the MySQL database and listed on the web page. After then, fetch the additional data from the database during page scrolling.
jQuery & Ajax Code:
You have to use the jQUERY to load data on page scroll without a page refresh, include the jQuery library first.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
Use the jQuery scroll() method to detect the page scroll, and initiate Ajax request when the user is scrolling down to the bottom of the page. On Ajax request, Send the last displayed post ID (lastID) to the getData.php file. Once the Ajax success method returns the posts data, the content HTML will append to the posts list.
<script type="text/javascript">
$(document).ready(function(){
$(window).scroll(function(){
var lastID = $('.load-more').attr('lastID');
if(($(window).scrollTop() == $(document).height() - $(window).height()) && (lastID != 0)){
$.ajax({
type:'POST',
url:'getData.php',
data:'id='+lastID,
beforeSend:function(){
$('.load-more').show();
},
success:function(html){
$('.load-more').remove();
$('#postList').append(html);
}
});
}
});
});
</script>
HTML & PHP Code:
Include the database configuration file (dbConfig.php) to connect and select the database. Now, get some limited number of records from the post table and list them on the web page.
<div id="postList">
<?php
// Include the database configuration file
require 'dbConfig.php';
// Get records from the database
$query = $db->query("SELECT * FROM posts ORDER BY id DESC LIMIT 5");
if($query->num_rows > 0){
while($row = $query->fetch_assoc()){
$postID = $row["id"];
?>
<div class="list-item"><h4><?php echo $row['title']; ?></h4></div>
<?php } ?>
<div class="load-more" lastID="<?php echo $postID; ?>" style="display: none;">
<img src="loading.gif"/>
</div>
<?php } ?>
</div>Load Data from Database (getData.php)
The Ajax request calls the getData.php file on page scroll. The new posts data is fetched from the MySQL database based on the last post ID and generates the posts list HTML. The content HTMLwas returned to the success method of Ajax request.
<?php
if(!empty($_POST["id"])){
//Include DB configuration file
require 'dbConfig.php';
//Get last ID
$lastID = $_POST['id'];
//Limit on data display
$showLimit = 2;
//Get all rows except already displayed
$queryAll = $db->query("SELECT COUNT(*) as num_rows FROM posts WHERE id < ".$lastID." ORDER BY id DESC");
$rowAll = $queryAll->fetch_assoc();
$allNumRows = $rowAll['num_rows'];
//Get rows by limit except already displayed
$query = $db->query("SELECT * FROM posts WHERE id < ".$lastID." ORDER BY id DESC LIMIT ".$showLimit);
if($query->num_rows > 0){
while($row = $query->fetch_assoc()){
$postID = $row["id"]; ?>
<div class="list-item"><h4><?php echo $row['title']; ?></h4></div>
<?php } ?>
<?php if($allNumRows > $showLimit){ ?>
<div class="load-more" lastID="<?php echo $postID; ?>" style="display: none;">
<img src="loading.gif"/>
</div>
<?php }else{ ?>
<div class="load-more" lastID="0">
That's All!
</div>
<?php }
}else{ ?>
<div class="load-more" lastID="0">
That's All!
</div>
<?php
}
}
?>
CSS Code
There is a need to use the following CSS code to specify the style of the posts list.
#postList{
margin-bottom:20px;
}
.list-item {
background-color: #F1F1F1;
margin: 5px 15px 2px;
padding: 2px;
font-size: 14px;
line-height: 1.5;
height: 120px;
}
.list-item h4 {
color: #0074a2;
margin-left: 10px;
}
.load-more {
margin: 15px 25px;
cursor: pointer;
padding: 10px 0;
text-align: center;
font-weight:bold;
}Also, read our previous blog- Ajax Pagination with Search and Filter in PHP
