Load More Data on Infinite Scroll Using Ajax & PHP

by

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, the Next limited data will be fetched from the MySQL database and It will be appended after the existed data using jQuery. All the processes will occur without page refresh.

load more data on page scroll using ajax php

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.

Read Also 

How to Insert Data Using Ajax in PHP and MySQL

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
   |__index-page.php
   |__style.css

1. Connect PHP to MySQL Database

Create codingstatus database  & connect it to PHP.

File Name – database.php

<?php

 
$hostname     = "localhost"; // Enter your hostname
$username     = "root";      // Enter your table username
$password     = "";          // Enter your table password
$databasename = "codingstatus.com"; // 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);
 }

?>

2. Create a Table in MySQL Database

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

Table Name – blogs

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.

3. Write Ajax Script to Load More Data on Scroll Down

To load more data on scroll down using Ajax, you will have to configure the following steps –

  • Create a scroll event with the window object
  • Get an id of a div using the class name loader and assign it to a variable lastId. This div will contain id the last record. So, this id will be the last id.
  • 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
  • Declare a custom function  load_more_data(lastId)  to load data from the server while scrolling the page.
  • Assign file backend-script.js to load data from the backend script based on the last id. When It loads successfully. It will append to the div id(id="#load-content").

File Name– ajax-script.js

$(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);

        }
    });

    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);
                    
                }
            });
    }
});

4. Write PHP Script to Load more Data on Ajax Request

Configure the following steps to load more data using PHP Script on ajax request –

Database Connection –

  • Include database connection file database.php
  • Assign connection variable $conn to a new variable $db
  • Assign table name blogs to a variable $tableName
  • Declare the number of records 10 to load on the scroll. You can change it according to your project requirement.

Custom Functions –

  • Declare a custom function validate() to validate the last id
  • Declare fetch_more_data() with parameters $lastId, $limit & $tableName to fetch records from MySQL table
  • Also, Declare display_data() with parameter $displayData to display records on the page.
  • Declare fetch_default_data() with parameters $tableName & $limit to display default 10 records on the web page.

Get the Last Id –

  • If $_GET['last_id'] is not empty then write the following steps –
  • Pass $_GET['last_id'] to a custom, function validate() to and assign it to a variable $lastId
  • Assign a custom function fetch_more_data($lastId,$limit,$tableName) and assign it to a variable $fetchMoreData.
  • Print another custom function display_data($fetchMoreData) .

File Name– backend-script.php

<?php

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

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

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

}

// validate last id
function validate($value) {
  $value = trim($value);
  $value = stripslashes($value);
  $value = htmlspecialchars($value);
  return $value;
}
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;
    }
  }
}

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;
 }
}
// fetching padination 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;
 }}}
?>

5. 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.

File Name – style.css

.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); }
}

6. Create an HTML Page to Display Records

To Display records on the HTML page, you will have to do the following steps –

  • Include an external CSS file style.css
  • Also, include jQuery CDN & external jquery ajax ajax-script.js
  • Include more file backend-script.php
  • Print a variable $displayDefaultData to display default 10 records

File Name- index.php

<!DOCTYPE html>
<html>
<head>
   <title>Load more data on Infinite Scroll Using PHP Ajax</title>
   <link rel="stylesheet" href="style.css">
</head>
<body>
    
<?php

    require_once('backend-script.php');
    echo $displayDefaultData;
?>


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

</body>
</html>