Load more data using jQuery Ajax and PHP from MySQL database

In this tutorial, we are going to show you how to Load More Results From MySQL Database Using jQuery Ajax in PHP. We have used similar paging technique like Facebook and twitter to load data from database. Whenever user click on load more button, then it will fetch the new data from database without page reload.

Lets see the below complete example, Here we Load More Data from MySQL database, when user click on load more button.

Create Load More Results From Database System Using jQuery, Ajax, PHP and MySQL

First Create "Post" Database Table
Create below table in mysql database and add entries to it.

CREATE TABLE `post` (
  `POSTID` int(3) NOT NULL,
  `POSTTITLE` varchar(100) NOT NULL,
  `POSTDETAILS` varchar(10000) NOT NULL,
  `POSTLINK` varchar(100) NOT NULL
)

config.php
Consists of database configuration details to establish database connection.

<?php
/* DATABASE CONFIGURATION */
define('DB_SERVER', 'localhost');
define('DB_DATABASE', 'skptricksdemo');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');

?>

DAO.php
This DAO class helps to establish the database connection and populate/fetch the record from MySQL database on AJAX call.

<?php
include("config.php");
class DAO{
 
 private $items_per_group = 2 ; 
 
 public function dbConnect(){
  
  $dbhost = DB_SERVER; // set the hostname
  $dbname = DB_DATABASE ; // set the database name
  $dbuser = DB_USERNAME ; // set the mysql username
  $dbpass = DB_PASSWORD;  // set the mysql password

  try {
   $dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
   $dbConnection->exec("set names utf8");
   $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   return $dbConnection;

  }
  catch (PDOException $e) {
   echo 'Connection failed: ' . $e->getMessage();
  }
 
  
 }
 
 public function getData($groupNo){
  
 //sanitize post value
 $group_number = $groupNo;
 
 
 //throw HTTP error if group number is not valid
 if(!is_numeric($group_number)){
  header('HTTP/1.1 500 Invalid number!');
  exit();
 }
 
 //get current starting point of records
 $position = ($group_number * $this->items_per_group);
  
  try {
   $dbConnection = $this->dbConnect();
   $stmt = $dbConnection->prepare("SELECT * FROM `post` order by 
   `POSTID` LIMIT :position , :items_per_group");
   $stmt->bindParam(':position', $position , PDO::PARAM_INT);
   $stmt->bindParam(':items_per_group', $this->items_per_group, PDO::PARAM_INT);
   $stmt->execute();

   $Count = $stmt->rowCount(); 
   //echo " Total Records Count : $Count .<br>" ;
             
   $result ="" ;
   if ($Count  > 0){
    while($data=$stmt->fetch(PDO::FETCH_ASSOC)) {
          
       $result = $result ."<div class='user-post-right-display-main-container-upto-last'  >
     <div  style='margin-left:10px;' >
     <span style='margin-left:12px;' ><h3><a href='".$data['POSTLINK'].
     "' style='text-decoration:none;'>".$data['POSTTITLE']."<a/> </h3></span>
     <span>".$data['POSTDETAILS']."</span>
     </div>
     </div>" ;
     }
    return $result ;
   }

  }
  catch (PDOException $e) {
   echo 'Connection failed: ' . $e->getMessage();
  }
 }
 
 public function getTotalNumberOfRecordsCount(){
  
  
  try {
   $dbConnection = $this->dbConnect();
   $stmt = $dbConnection->prepare('SELECT count(*) as `total_count` FROM `post`');
   $stmt->execute();
   
   $Count = $stmt->rowCount(); 
   //echo " Total Records Count : $Count .<br>" ;

   if ($Count  > 0){
    $data=$stmt->fetch(PDO::FETCH_ASSOC) ;
    $total_groups = ceil($data['total_count']/$this->items_per_group);
    return $total_groups;

    
   }

  }
  catch (PDOException $e) {
   echo 'Connection failed: ' . $e->getMessage();
  }
 }
 
 
}


?>

controller.php

Controller work is to control the flow of execution. Here When use click on "load more" button, jQuery script send request to controller.php page and it will process the request and return response to be displayed.

<?php
include("DAO.php");

if(isset($_POST["group_no"])){
 
 $group_number = filter_var($_POST["group_no"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH);
 $dao = new DAO();
 echo $dao->getData($group_number);
}

?>

index.php
This page consists of Jquery, AJAX, PHP and HTML code, which helps to fetch records form MySQL database when user click on "Load More" button.

• JQuery script helps to detect on click event to load data.
• AJAX helps to fetch records without page refresh.

<?php
include("controller.php");
$dao = new DAO();

?>

<html>
<head>
<script src="jquery-3.2.1.min.js"></script>
<style>
.user-post-right-display-main-container-upto-last
{
float:left;
line-height:20px;
min-height:50px;
word-wrap:break-word;
overflow:hidden;
display:block;
font-size:15px;
width:600px;
margin-top:15px;
background-color:white;
border:1px solid #d6d7da;
border-radius:4px;


}
.user-post-display-body-main-container:after {
 content: ".";
 display: block;
 height: 0;
 clear: both;
 visibility: hidden;
}
.animation-load{
float:left;
min-height:30px;
text-align: center;
width:600px;
font-size:18px;
margin-bottom:15px;
margin-top:15px;
background-color:white;
border:1px solid #d6d7da;
border-radius:4px;
padding-top:5px;
-moz-border-radius:4px;
-webkit-border-radius:4px;
  box-shadow: 0 2px 10px #cbcbcb;
 -moz-box-shadow: 0 2px 10px #cbcbcb;
 -webkit-box-shadow: 0 2px 10px #cbcbcb;
 display:none;
 font-weight: bold;
cursor: pointer; 
}


</style>


<script type="text/javascript">



$(document).ready(function() {
 var track_load = 0; //total loaded record group(s)
 var loading  = false; //to prevents multipal ajax loads
 var total_groups = <?php echo $dao->getTotalNumberOfRecordsCount(); ?> //total record group(s)
 
  
    // Load data on first load...
 $.post('controller.php',{'group_no': track_load}, function(data){
  track_load++;
  $('.user-post-display-body-main-container').append(data);
  $('.animation-load').show();

 });
 
 
 
function loadData()
{


 if(track_load <= total_groups && loading==false) //there's more data to load
   {
    loading = true; //prevent further ajax loading
   
    $('.animation-load').html('<img src="loading-animation.gif" width="70px;" height="50px">');
    //load data from the server using a HTTP POST request
    $.post('controller.php',{'group_no': track_load}, function(data){
         
     $(".user-post-display-body-main-container").append(data); //append received data into the element

     //hide loading image
     $('.animation-load').html('Load More...'); //hide loading image once data is received
     
     track_load++; //loaded group increment
     loading = false; 
    
    }).fail(function(xhr, ajaxOptions, thrownError) { //any errors?
     
     alert(thrownError); //alert with HTTP error
     $('.animation-load').hide(); //hide loading image
     loading = false;
    
    });
    
    
   }
   
   if(track_load >= total_groups-1)
      {
    //reached end of the page yet? disable load button
    
    $('.animation-load').html('No more post');
      }
   
   
} 
 
 $(".animation-load").click(function (e) { //user clicks on button
  loadData();
  
});



});
</script>
</head>
<body style="margin:0px auto;width:700px;">

<div id="display_posts" class="user-post-display-body-main-container" style="margin:0px auto;width:700px;">

<?php  // echo $dao->getData(0); ?>
</div>
<div class="animation-load" > Load More... </div>
</body>
</html>

Download Link :
https://github.com/skptricks/php-Tutorials/tree/master/Load%20more%20data%20using%20jQuery%20Ajax%20and%20PHP%20from%20MySQL%20database

Also you can check out below example : 
jquery scroll pagination ajax php