Auto Load Dynamic Data with jquery smooth scroll

 

When you have millon of data in your database how will you display that data to your website visitor? Either you can display the data using pagination or auto load the data through jquery scrolling. In this article I will show you how to load dynamic data from your database while you scroll down your mouse or press bottom arrow key on your keyboard. Now a days many ecommerce websites are using jquery scrolling method to load their data.

Database
Below are the table structure of user table.

CREATE TABLE IF NOT EXISTS user(
   uid int(11) NOT NULL AUTO_INCREMENT,  
   user_name varchar(100) NOT NULL,
   email varchar(50) NOT NULL,
   contact_number varchar(15) NOT NULL,
   user_bio text NOT NULL,
   PRIMARY KEY (uid)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

DB Configuration
Below are the database configuration files.Create a file name as config.php and paste below code in it.

<?php
define("SHOW_RESULT_PER_PAGE",50); //How many records you want to show per scrolling
define("DB_HOST_NAME","your host name");//Replace with your host name
define("DB_USER_NAME","your db username");//Replace with your database username
define("DB_PASSWORD","your db password");//Replace with your database password
define("DB_NAME","your database name");//Replace with your database name
$connection=mysqli_connect(DB_HOST_NAME,DB_USER_NAME,DB_PASSWORD,DB_NAME);

// Check whether connection is successfully established or not
if (mysqli_connect_errno()){
  	die("Unable to connect to Database: " . mysqli_connect_error());
}
?>

Jquery Scrolling Code
Create a javascript file and name as scroll.js and paste below code.

var current_pos = 0; //This variable will store How many times record loaded
var busy  = false; //This global variable will prevents from multipal ajax Load
function AutoLoadDataWithScroll(total_records,url,show_result_span_id){
	//Initially call this function to load records
	LoadData(total_records,url,show_result_span_id);
	
	/*This will load the page while you scroll your page*/
	$(window).scroll(function() { 
		if($(window).scrollTop() + $(window).height() == $(document).height()) //if user has reached bottom of the page
		{
			LoadData(total_records,url,show_result_span_id);			
		}
	});
}

/*Loading Data*/
function LoadData(total_records,url,show_result_span_id){
	/*If current_pos is less than or equal to total_page and busy variable is false then execute below code*/
	if(current_pos <= total_records && busy==false)
	{
		busy = true; //Again prevent multiple ajax loading
		$('#loading').html('Please wait...'); //show loading text
		
		$.ajax({method: "POST",
			    url: url,
			    data: { start_from:current_pos}
			  })
		.done(function( response ) {
				$("#"+show_result_span_id).append(response);
				
				//hide loading image
				$('#loading').html(''); //hide loading text once data is received
				
				current_pos++; //Increase to next position
			    busy = false; 
			})
		.fail(function(xhr,status,err) { //If there is any error in ajax then this will execute and display error message
				alert(err);
				$('#loading').html(''); //hide loading image
				loading = false;
		});
	}	
}

In the above jquery file there are two functions AutoLoadDataWithScroll() and LoadData(). Both function will accept 3 parameters i.e total_records,url and show_result_span_id. total_records should be total pages(total record in your database/how many record show per page). url should be the PHP file which will retrieve data from database and finally show_result_span_id is the unique id which will display the result. LoadData() function simply send HTTP request to web server and retrieve data from the server then display the data to the user.

Lets describe little bit about AutoLoadDataWithScroll() function. The window scroll in jquery i.e $(window).scroll applies to window object when user scroll down to a different location. scrollTop() which returns the current vertical position of the scroll bar. scrollTop() returns 0 if scroll bar is at the top or if the element is not scrollable. $(window).height() which returns the height of the window whereas $(document).height() returns the height of the document.

function.php
In this file there is one function which checks whether there are records in user table or not.

<?php
define("SHOW_RESULT_PER_PAGE",50); //How many records you want to show per scrolling
/*This function will calculate how many records are there in your table*/
function TotalRecord(){
	global $connection;
	$sql="SELECT uid FROM user";
	$result=mysqli_query($connection,$sql);
	return mysqli_num_rows($result);
}
?>

style.css
Add some css in the listing.

body{margin: 0px;padding:0px;}
.main{width:500px;margin:0 auto;}

.data-listing{
	border:1px solid #999;
}
.data-listing th{
font-size: 18px;
padding:5px;
}
.data-listing td{padding:5px;}
.data-listing td.no_record{
	color: #333;
    font-weight: bold;
    text-align: center;
}

index.php

<?php
require("config.php");
require("function.php");
$TotalRecords=TotalRecord();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Auto Scroll</title>
<link rel="stylesheet" href="style.css">
<script type="text/javascript" src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
<script type="text/javascript" src="scroll.js"></script>
</head>
<body>
<div class="main">
  <table class="data-listing" border="0" cellpadding="0" cellspacing="0">
    <?php
if($TotalRecords>0){
	$TotalPages=ceil($TotalRecords/SHOW_RESULT_PER_PAGE);?>
    <thead>
      <tr>
        <th width="25%">User Name</th>
        <th width="25%">Email Address</th>
        <th width="25%">Contact Number</th>
        <th width="25%">User Biography</th>
      </tr>
    </thead>
    <tbody id="load_result">
    </tbody>
    <script type="text/javascript">
		AutoLoadDataWithScroll('<?php echo $TotalPages;?>','get_result.php','load_result');
	</script>
    <?php
}
?>
  </table>
</div>
</body>
</html>

get_result.php
This will retrieve the data from database.

<?php
require("config.php");
if(isset($_POST["start_from"])){
	$start=(int)$_POST["start_from"];
	$limit=SHOW_RESULT_PER_PAGE;
	//get current starting position of records
	$start_from=($start * $limit);
	$sql="SELECT * FROM user LIMIT $start_from,$limit";
	$result=mysqli_query($connection,$sql);
	$Arr=array();
	if(mysqli_num_rows($result)>0){		
		echo '<tr>
			  <td>'.stripslashes($row['user_name']).'</td>
			  <td>'.stripslashes($row['email']).'</td>
			  <td>'.stripslashes($row['contact_number']).'</td>
			  <td>'.stripslashes($row['user_bio']).'</td>
			  </tr>';
	}else{
		echo '<tr><td colspan="4" class="no_record">No more records.</td></tr>';
	}	
}
?>

Leave a comment

SUBSCRIBE TO NEWSLETTER

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Categories