AllsWeb Blog
No Result
View All Result
  • Home
  • Main Home
  • PHP and MySQL
  • JavaScript
    • jQuery & AJAX
  • WordPress
  • SEO
  • Web Hosting
  • Comparison
Support
Knowledgebase
  • Home
  • Main Home
  • PHP and MySQL
  • JavaScript
    • jQuery & AJAX
  • WordPress
  • SEO
  • Web Hosting
  • Comparison
No Result
View All Result
AllsWeb White Logo
No Result
View All Result
Home JavaScript jQuery & AJAX

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

Create Database Table

Lakshika Mathur by Lakshika Mathur
December 18, 2019
Reading Time: 4 mins read
0
Load Data on Page Scroll from MySQL Database using jQuery Ajax PHP

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. 

RELATED POSTS

What is Application Programming Interface (APIs)?

Like Dislike Rating System with jQuery, Ajax, and PHP

Star Rating System with jQuery, Ajax, PHP, and MySQL

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

Tags: MySQL DatabasePage Scroll
ShareTweetSendShareSharePinScan
Lakshika Mathur

Lakshika Mathur

Related Posts

What is Application Programming Interface (APIs), Types, and Importance.
PHP and MySQL

What is Application Programming Interface (APIs)?

January 29, 2022
61
Like Dislike Rating System with jQuery, Ajax, and PHP
jQuery & AJAX

Like Dislike Rating System with jQuery, Ajax, and PHP

January 6, 2020
739
Star Rating System with jQuery, Ajax, PHP, and MySQL
jQuery & AJAX

Star Rating System with jQuery, Ajax, PHP, and MySQL

January 6, 2020
162
How to Force Download File in PHP
PHP and MySQL

How to Force Download File in PHP

January 2, 2020
82
Form Validation using jQuery Validation Plugin
jQuery & AJAX

Form Validation using jQuery Validation Plugin

January 2, 2020
34
How to Connect to the Remote MySQL Database using PHP
PHP and MySQL

How to Connect to the Remote MySQL Database using PHP

January 1, 2020
28
Next Post
Telephone

International Telephone Input with Country Flags and Dial Codes using jQuery

Credit Card Validation using jQuery

Credit Card Validation using jQuery

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • Comparison (3)
  • HTML & CSS (9)
  • Interesting Facts (1)
  • JavaScript (27)
    • jQuery & AJAX (18)
  • PHP and MySQL (48)
  • Security (10)
  • SEO (2)
  • Trademark (2)
  • Tutorials (5)
  • Uncategorized (1)
  • Web Hosting (19)
    • VPS Server (5)
  • WordPress (8)

Recent Posts

  • Is the Trademark valuable to your Brand or domain?
  • Ideas For Ten Fantastic Online Business From Home
  • Some best free WordPress Themes for Affiliate Marketing Websites
  • Home
  • Posts
  • Privacy Policy
  • Terms and Conditions

Built and Maintained With ♥ by AllsWeb Team

No Result
View All Result
  • Home
  • Main Home
  • PHP and MySQL
  • JavaScript
    • jQuery & AJAX
  • WordPress
  • SEO
  • Web Hosting
  • Comparison

Built and Maintained With ♥ by AllsWeb Team

Go to mobile version