Facebook Style Auto Scroll Pagination using jQuery and PHP

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

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





DAO.php

<?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

?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

JQuery script helps to detect scroll down to load. 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>

In this tutorial, we will show you. 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 weConsists of database configuration details to establish database connection.This DAO class helps to establish the database connection and populate/fetch the record from MySQL database on AJAX call.Controller work is to control the flow of execution. Here on scroll down, JQuery script send request topage and it will process the request and return response to be displayed.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.------------------------------Check out our blog archive on the topic if you’re looking to learn about :1.2. PHP Limit Data Selections From MySQL Database