Load Data on Page Scroll from MySQL Database using jQuery Ajax PHP

The infinite page scroll is one type of pagination where the user doesn’t need to click on the link to load dynamic data. The dynamic data is loaded from the server automatically while scrolling page down. Infinite Scrolling is a user-friendly way to load additional content on the web page. This effect is the best replacement of pagination links for auto load dynamic content from server.

Load Data on Scroll use Ajax to load external content from database using jQuery. When the user reaches the end of the web page, the data is retrieved from the MySQL database and new content is loaded in the web page while scrolling the page down. In this tutorial, we will show you how to load data on page scroll using jQuery, Ajax, PHP, and MySQL.

The following functionality will be implemented in load data on page scroll script.

• Fetch and list data from database using PHP and MySQL.
• Detect page scroll using jQuery.
• Make AJAX request to load data from the server.
• Display dynamic content under the previously loaded data.

Load More Data from Database using jQuery Ajax PHP MySQL

Create Database Table

To store the post information a table needs to be created in the database. The following SQL creates an posts table with some basic fields in the MySQL database.

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)

The dbConfig.php file is used to connect and select the MySQL database. Specify the database hostname ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL credentials.

<?php
//DB details
$dbHost     = 'localhost';
$dbUsername = 'root';
$dbPassword = 'root';
$dbName     = 'codexworld';

//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)

Initially, a limited number of posts data will be retrieved from the MySQL database and listed in the web page. Additional data will be fetched from the database while page scrolling.

jQuery & Ajax Code:
The jQuery is used to load data on page scroll without page refresh, include the jQuery library first.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

The jQuery scroll() method will be used to detect the page scroll and Ajax request will be initiated when user scrolling down to bottom of the page. On Ajax request, the last displayed post ID (lastID) will be sent to the getData.php file. Once Ajax success method returns the posts data, the content HTML will append to 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, fetch some limited number of records from the posts 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 getData.php file is called by the Ajax request on page scroll. The additional posts data is fetched from the MySQL database based on the last post ID and generate the posts list HTML. The content HTML is 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

The following CSS code is used 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;
}