Load More Data on Infinite Scroll Using Ajax PHP MySQL

Load more data on Page Scroll is the best functionality to create pagination. It does not have multiple pages to display data. It can display whole data on a single page while you start scrolling the web page. And only limited numbers of data will be displayed on each scrolling. Even It will continue to load the data until the whole data gets empty in the database table. Therefore, you can also say it Infinite Scroll Pagination

Infinite Scroll Pagination loads whole data dynamically from the on the web page using jQuery ajax. When you take the scroll bar at the bottom of the window, Next limited data will be fetched from MySQL database and It will be appended after the existed data using jQuery. All the processes will occur without page refresh.

How to Load More Data On Page Scroll Using PHP jQuery Ajax MySQL

Read the following Algorithm to load more data on page scroll. It will help you to easily learn this script. Even it will be explained with source code & example from the next steps.

• First of all, Connect your database to PHP
• Write jquery script to perform ajax script
• write ajax script to load more data from the server script
•  fetch default limited data from database using PHP
•  Display default limited data  on the web page
• Now, Fetch more data from the database based on the last id
• At last, Display more data on the web page.

Read Also 

Dynamic Pagination in PHP with Next and Previous

How to Insert Data Using Ajax in PHP and MySQL

1.Configure Basic Requirement to Load More Data

Before going to the next step, you should configure the following basic things

Create the following folder structure

myproject/
   |__ajax-script.js
   |__database.php
   |__backend-script.php
   |__front-page.php
   |__style.css

File Name – database.php

Create codingstatus database  & connect it to PHP using the following script.

<?php 
$hostname     = "localhost"; // Enter your hostname
$username     = "root";      // Enter your table username
$password     = "";          // Enter your table password
$databasename = "codingstatus"; // Enter your Database name
// Create connection 
$conn = new mysqli($hostname, $username, $password,$databasename);
 // Check connection 
if ($conn->connect_error) { 
die("Unable to Connect database: " . $conn->connect_error);
 }
?>

Create blogs table using the following query. You can use your table.

CREATE TABLE `blogs` (
  `id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `description` varchar(3000) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have inserted about 65 records in a blogs. You can insert it according to your project requirement.

File Name – ajax-script.php

Now Write the jquery ajax script of step no 2 to 3 in file ajax-script.js.

2. Load More Data on Scroll Down Using jquery

Write the following jquery script in ajax-script.js. It will call the ajax function to load content on scroll down.

$(document).ready(function(){
    $(window).on('scroll',function(){
        var lastId = $('.loader').attr('id');
        if(($(window).scrollTop() == $(document).height() - $(window).height()) && (lastId != 0)){
            load_more_data(lastId); // call ajax function
       }
});

A loader div (class="loader") will be declared at the end of each limited records per scrolling. This div will contain id of the last record. So, this id will be the last id.

You will have to load the next data based on the last id. Therefore, this id will be accessible on each scroll and pass it user-defined ajax function load_more_data(lastId) . You will learn to create  this function in the next step

3. Load More Data From the Server While Scrolling Using Ajax

Even Write declare the following user-defined ajax function in ajax-script.js. It will load data from the server while scrolling the page.

function load_more_data(lastId){
   $.ajax({
      type:'GET',
      url:'backend-script.php',
      dataType:'html',
      data:{last_id:lastId},
      beforeSend:function(){
      $('.loader').show();
    },
   success:function(data){
      setTimeout(function() {
      $('.loader').remove();
      $('#load-content').append(data);
      },1000);
      }
   });
}

The above script will load data from the backend script file backend-script.js based on the last id. When It loads successfully. It will append to the div id(id="#load-content").

File Name – backend-script.php

Now, You will learn backend script of backend-script.php file from the next steps. So, remember that you have to write the script of steps 4 to 9 in backend-script.php .

Before Writing the next script, you have to write and configure the following script.

require_once('database.php');
$db= $conn; // Enter your connection variable
$tableName='blogs'; // Enter your Table Name
$limit=10; // Enter your records limit

4. Get the Last ID  to Load More Data in PHP

Write the following script to get the last id from ajax script using $_GET['last_id] method.

if(isset($_GET['last_id']) && !empty($_GET['last_id']) ){
  $lastId= validate($_GET['last_id']);
  $fetchMoreData=fetch_more_data($lastId,$limit,$tableName);
  echo display_data($fetchMoreData);

}

When $_GET['last_id] does not set any value and does not have empty, It will call the user to define a function fetch_more_data($lastId,$limit,$tablName) to fetch more data from the database based on the last id. This function is explained in the next step 6.

fetch_more_data($lastId,$limit,$tablName) is stored in the variable $fetchMoreData. This variable is passed to another custom display_data($fetchMoreData) function to display load more data on the web page. this function will be explained in step 7

Before, passing the last id to user-define function, It is passed to the custom function validate($_GET['last_id]) as a parameter to validate it. So, you have to validate it in the next step 5.

5. Validate the Last ID in PHP

Declare the following script to validate the last id.

function validate($value) {
  $value = trim($value);
  $value = stripslashes($value);
  $value = htmlspecialchars($value);
  return $value;
}

6. Fetch More Data From the Database

Declare the following function to fetch more data from MYSQL table based on the last id.

function fetch_more_data($lastId,$limit,$tableName){
   global $db;
   $limit= trim($limit);
   $tableName=trim($tableName);
   if(empty($limit)){
     return "Limit must be required";
   }elseif (empty($tableName)) {
    return "Database must be required";
   }else{
    $query = $db->prepare("SELECT * FROM ".$tableName." WHERE id< ? ORDER BY id DESC LIMIT ?");
      if($query){
  	$query->bind_param('ii',$lastId,$limit);  
        $query->execute();
        $result=$query->get_result();
  	if($result){
  	  if($result->num_rows>0){
             $row= $result->fetch_all(MYSQLI_ASSOC);
             return $row;
          }
        }else{
          return "Error: " . $query . "<br>" . $db->error;
        }
      }else{
  return "Error: " . $query . "<br>" . $db->error;
      }
   }
}

7. Display More Data on Web Page

Declare the following function to display data on the HTML web page.

function display_data($displayData){
   if(is_array($displayData)){
     $text='';
     $text='<div class="display-content" id="load-content">';
    foreach($displayData as $data){
    
    $text.= "<div class='record'>";
    $text.= "<h1>".$data['id']." ".$data['title']."</h1>";
    $text.= "<p>".$data['description']."</p>";
    $text.= "</div>";
    }
    $text.="<div class='loader-symbol'><div class='loader' id='".$data['id']."' style='display: none;'></div></div>";
    $text.="</div>";
    return $text;
    }else{
     return $displayData;
    }    
}

8. Fetch Default Data Using PHP

Now, Declare the following function to fetch default data.

function fetch_default_data($limit,$tableName){
   global $db;
   $limit= trim($limit);
   $tableName=trim($tableName);
   if(empty($limit)){
     return "Limit must be required";
   }elseif (empty($tableName)) {
     return "Database must be required";
   }else{
     $query = $db->prepare("SELECT * FROM ".$tableName." ORDER BY id DESC LIMIT ?");
     if($query){
  	$query->bind_param('i',$limit); 
        $query->execute();
        $result=$query->get_result();
  	if($result){
  	   if($result->num_rows>0){
            $row= $result->fetch_all(MYSQLI_ASSOC);
            return $row; 
           } else{
      return "No Records Found";
           }     
      }else{
         return "Error: " . $query . "<br>" . $db->error;
      }
  }else{
  return "Error: " . $query . "<br>" . $db->error;
  }
 }
}

9. Display Default Data Using PHP

Call the fetch_default_data($limit,$tableName) and store it in variable  $fetchDefaultData. Pass this variable in display_data($fetchDefaultData) to display records on the web page. These records will be displayed before page scrolling. Even this function store in another variable $displayDefaultData.

$fetchDefaultData=fetch_default_data($limit,$tableName);
$displayDefaultData= display_data($fetchDefaultData);

File Name – front-page.php

Include database connection file and echo $displayDefaultData in front-page.php

<?php
require_once('database.php');
echo $displayDefaultData;
?>

Even include ajax  file & jQuery CDN  in front-page.php using the following script.

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.js"></script>
<script src="ajax-script.js" type="text/javascript">
</script>

10. Create a loader Using CSS

Write the following CSS code in style.css to create a loader to notify before loading more data on page scroll.

.loader {
  display:inline-block;
  border: 5px dotted lightgray;
  border-radius: 50%;
  border-top: 5px solid gray;
  border-bottom:5px solid gray;
  width: 30px;
  height: 30px;
  -webkit-animation: spin 1s linear infinite; /* Safari */
  animation: spin 1s linear infinite;
}

/* Safari */
@-webkit-keyframes spin {
  0% { -webkit-transform: rotate(0deg); }
  100% { -webkit-transform: rotate(360deg); }
}
.loader-symbol{
text-align:center}

@keyframes spin {
  0% { transform: rotate(0deg); }
  100% { transform: rotate(360deg); }
}

11. Use Complete Infinite Scroll Pagination Script to load more data

Now you can use the following complete script directly in your project.

This is a PHP script file. This file contains all the custom functions to fetch & display data.

File Name- backend-script.php

This is the ajax script file. It contains jquery & ajax script.

File Name- ajax-script.php

This is front-page Where you will display default data & load more data on page scroll.

File Name- front-page.php