Sunday, November 5, 2017

Dynamically load data on div scroll using php, mysql, jquery and ajax

In this tutorial, we will show you how can you dynamically load data on div tag using PHP, MySQL, JQuery and Ajax. We have used similar paging technique like Facebook to load data from database. Whenever user scroll down and reach to bottom of the page, then it will fetch the new data from database without page reload.

Lets see the below complete example, where we Load Data on Page Scroll using JQuery Ajax PHP from MySQL Database.




First Create "Post" Database Table
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{
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;
$items_per_group = 5 ; 
//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 * $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', $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;' ><h1><a href='".$data['POSTLINK'].
"' style='text-decoration:none;'>".$data['POSTTITLE']."<a/> </h1></span>
<span>".$data['POSTDETAILS']."</span>
</div>
</div>" ;
}
return $result ;
}
}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
}
public function getTotalNumberOfRecordsCount(){
$items_per_group = 5 ;
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']/$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 on scroll down, 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 consist of Jquery, AJAX, PHP and HTML code, which helps to fetch records form MySQL database when user scroll down to bottom of the page.

  1. JQuery script helps to detect scroll down to load.
  2. AJAX helps to fetch records without page refresh.


<?php
include("DAO.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;
        -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;
      }
      .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();
        }
                );
        // Scroll to load data
        $(window).scroll('scroll', function() {
          if($(window).scrollTop() >= $('#display_posts').offset().top + $('#display_posts').outerHeight() - window.innerHeight) {
            loadData();
          }
        }
                        );
        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/Dynamically%20load%20data%20on%20div%20scroll%20using%20php-%20mysql-%20jquery%20and%20ajax

No comments:

Post a Comment