Load Dynamic Data on Page Scroll using jquery AJAX in PHP MYSQL

by | Sep 17, 2021 | PHP | 0 comments

In this tutorial, I am going to tell you, how to auto load more data dynamically when users scroll web pages using jQuery, Ajax, PHP, and MySQL. We are using the infinite page scroll method. This is one type of pagination. In this method is the user doesn’t need to click on the pagination button to load more dynamic data. Whenever the user visits the web page scrolls down the page automatically displays the next few records from the MySQL database server.

Remember this tutorial based on PHP, MySql, and JQuery using infinite page scroll. This Scrolling is a user-friendly way to web page load additional new data. When the user reaches the end of the web page. The following infinite load data on page scroll script

  • PHP and MySQL database are using fetch and list of all data.
  • jQuery is using page scroll Detect.
  • AJAX is using a request to load all data from the MySql server.
  • Webpage Display all dynamic content without page refresh under the previously loaded data.

Create Database and Tables

In the following example, we will start how to automatically Load Dynamic Data on Page Scroll. So first, create a MySQL database called codeat21. And after that, create the table name called posts.

Database information

  • Database Name – codeat21
  • Table Name – posts
CREATE TABLE `posts` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `description` LONGTEXT NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Configuration

First, Create dbConfig.php file to connect the MySQL database. $dbHost is a database hostname, $dbUsername is a database username, $dbPassword is a database password, $dbName is a database name.

 dbConfig.php 

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

//Create connection and select DB
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

if ($db->connect_error) {
    die("Unable to connect database: " . $db->connect_error);
} 
?> 

 index.php 

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Load Dynamic Data on Page Scroll using jquery AJAX in PHP MYSQL</title>
<style type="text/css">
@import url('https://fonts.googleapis.com/css2?family=Inter&display=swap');
body {
  margin: auto;
  padding: 0px;
  overflow-x: hidden;
  background-repeat: repeat;
  font-family: 'Inter', sans-serif;
}
.container {
    width: 100%;
    padding-right: 15px;
    padding-left: 15px;
    margin-right: auto;
    margin-left: auto;
    max-width: 1200px;
}
#postList{ 
	margin-bottom:20px;
}
h1{
	text-align: center;
    margin: 60px 0 37px 0;
    color: #07a8ff;
    font-size: 33px;
    font-weight: 600;
}
.list-item {
    margin: 0 0 37px 0;
    padding: 27px;
    font-size: 17px;
    line-height: 33px;
    color: black;
    border: 0px solid #0e97e5;
    box-shadow: inset 1px 3px 7px 3px #a1dcfe;
}
.list-item h4 {
	color: #0074a2;
	margin-left: 10px;
}
.load-more {
	margin: 15px 25px;
	cursor: pointer;
	padding: 10px 0;
	text-align: center;
	font-weight:bold;
}
</style>

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

<script type="text/javascript">
$(document).ready(function(){
	$(window).scroll(function(){
		var lastID = $('.load-more').attr('lastID');
		
		 if(($('#postList').height() <= $(window).scrollTop() + $(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>
</head>
<body>
<div class="container">
        	<h1>Load Data on Page Scroll by codeat21</h1>
            <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 7");
			
			if($query->num_rows > 0){ 
				while($row = $query->fetch_assoc()){
                    $postID = $row["id"];
            ?>
				<div class="list-item"><h2><?php echo $row['title']; ?></h2>
				<p><?php echo $row['description']; ?></h4></div>
            <?php } ?>
				<div class="load-more" lastID="<?php echo $postID; ?>" style="display: none;">
					<img src="loading.gif"/>
				</div>
            <?php } ?>
    </div>
</div>
</body>
</html>


 getData.php 

<?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"><h2><?php echo $row['title']; ?></h2>
				<p><?php echo $row['description']; ?></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
    }
}
?>

Conclusion

Finally, this is how to automatically load more data from MySQL database on page infinite page Scroll using jQuery, Ajax in PHP MYSQL tutorial is over. The tutorial is covered in simple code and easy steps with a live youtube demo.

Try youtube live demo on – infinite page Scroll dynamically using jQuery, Ajax, PHP and MySQL database from download the complete full source code from the below download Github repository link. https://github.com/CodeAT21 this is CodeAT21 Github repository.

Download source code from github   |   Live Demo – youtube