Infinite scroll pagination using php jquery and ajax

In this tutorial, we are going to learn how to create infinite scroll pagination using PHP jquery and ajax. we have seen on Facebook, Twitter, or other websites on that automatically loaded content when scrolling down to the web page.

So you can follow the below steps and see our example for mouse scroll pagination in Php.

Overview

Create a Database and Table

Connect to Database

Get the limit data

Get the infinite data

Create a Database and Table

First of all for we will create a database and table. after then we will add dummy data in the table. here we take the products table and also added products related data into the product table. so you can see the below code.

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `price` varchar(255) NOT NULL,
  `status` int(1) NOT NULL,
  `created_at` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `products` ADD PRIMARY KEY (`id`);
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;
COMMIT;

CREATE TABLE `products` (

`id` int(11) NOT NULL,

`title` varchar(255) NOT NULL,

`description` text NOT NULL,

`price` varchar(255) NOT NULL,

`status` int(1) NOT NULL,

`created_at` date NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `products` ADD PRIMARY KEY (`id`);

ALTER TABLE `products`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;

COMMIT;

Connect to Database

Here in this step, we will create a config.php file and connect the MySQL with PHP. so you can see the below code for configuration.

config.php

<?php
$hostname="localhost";
$username="root";
$password="";
$database="mouse_scroll_pagination_php";
$conn = mysqli_connect($hostname,$username,$password,$database); 
?>

<?php

$hostname="localhost";

$username="root";

$password="";

$database="mouse_scroll_pagination_php";

$conn = mysqli_connect($hostname,$username,$password,$database);

Get the limit data

Here in this step, we have to need limited data for display data. so we are displaying 10 data per rows. for that, we will take a constant variable so we can display 10 data per rows. now here we will take two hidden inputs filed for the current row position and the total number of rows.

So you can see below code for the first time get rows records.

<?php
$query = "select * from products order by id desc limit $row,$row_per_page ";
$result = mysqli_query($conn,$query);
while($row = mysqli_fetch_array($result)){
?>
<tr class="product" id="product_<?php echo $row['id']; ?>">
<td><?php echo $row['title']; ?></td>
<td><?php echo $row['description']; ?></td>
<td><?php echo $row['price']; ?></td>
<td><?php echo $row['created_at']; ?></td>
</tr>
<?php } ?>

<?php

$query = "select * from products order by id desc limit $row,$row_per_page ";

$result = mysqli_query($conn,$query);

while($row = mysqli_fetch_array($result)){

</tr>

<?php } ?>

Get the infinite data

Here in this step, we have used some functions. you can see it.

$(window).scrollTop() function when scroll then it will return current vertical position.

$(document).height() function return height of the document.

$(window).height() function return pixel value of the height of the (browser) window.

When we scroll down if the current position and bottom position are the same then it will be passed next current row data using ajax and call into pagination.php file. after it will be helpful for getting limit data into the query.

<!DOCTYPE html>
<html lang="en">
<head>
	<title>Infinite scroll pagination using php jquery and ajax - Webprogrammingtutorials.com</title>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
	<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
	<script type="text/javascript">
	$(document).ready(function(){
		$(window).scroll(function(){
			var position = $(window).scrollTop();
			var bottom = $(document).height() - $(window).height();
			if( position == bottom ){
				var row = Number($('#row').val());
				var allcount = Number($('#all_product_count').val());
				var rowperpage = 10;
				row = row + rowperpage;
				if(row <= allcount){
					$('.ajax-load').show();
					var url = "pagination.php";
					$('#row').val(row);
					$.ajax({
						url: url,
						type: 'post',
						data: {row:row},
						success: function(response){
							$('.ajax-load').hide();
							$(".product:last").after(response).show().fadeIn("slow");
						}
					});
				}
				else{
					$('#remove').remove();
					$(".product:last").after('<tr id="remove"><td colspan="4" style="text-align: center;"><b>No Data Available</b></td></tr>');
				}
			}
		});
	});
	</script>
	<style>
	table {
	border-collapse: collapse;
	border-spacing: 0;
	width: 100%;
	border: 1px solid #ddd;
	}
	th,td {
	text-align: left;
	padding: 8px;
	}
	tr:nth-child(even){background-color: #f2f2f2}
	</style>
</head>
<body>
	<?php
	include('config.php'); 
	$row= 0;
	$row_per_page = 10;
	$count_product_query = "SELECT count(*) as allcount FROM products";
	$count_product_result = mysqli_query($conn,$count_product_query);
	$count_product_fetch = mysqli_fetch_array($count_product_result);
	$all_product_count = $count_product_fetch['allcount'];
	?>
	<div class="container">
		<h2>Product Table</h2>
		<div style="overflow-x:auto;">
			<table class="table table-bordered">
				<thead>
					<tr>
						<th>Product Name</th>
						<th>Description</th>
						<th>Price</th>
						<th>Create</th>
					</tr>
				</thead>
				<tbody>
					<?php
					$query = "select * from products order by id desc limit $row,$row_per_page ";
					$result = mysqli_query($conn,$query);
					while($row = mysqli_fetch_array($result)){
					?>
					<tr class="product" id="product_<?php echo $row['id']; ?>">
						<td><?php echo $row['title']; ?></td>
						<td><?php echo $row['description']; ?></td>
						<td><?php echo $row['price']; ?></td>
						<td><?php echo $row['created_at']; ?></td>
					</tr>
					<?php } ?>
				</tbody>
			</table>
			<input type="hidden" id="row" value="0">
			<input type="hidden" id="all_product_count" value="<?php echo $all_product_count; ?>">
		</div>
		<div class="row ajax-load" style="display:none;">
			<div class="col-lg-12" style="text-align: center;"><img src="loader.gif" width"100px" height="100px"/></div>
		</div>
	</div>
</body>
</html>

100

101

<!DOCTYPE html>

<head>

<title>Infinite scroll pagination using php jquery and ajax - Webprogrammingtutorials.com</title>

$(document).ready(function(){

$(window).scroll(function(){

var position = $(window).scrollTop();

var bottom = $(document).height() - $(window).height();

if( position == bottom ){

var row = Number($('#row').val());

var allcount = Number($('#all_product_count').val());

var rowperpage = 10;

row = row + rowperpage;

if(row <= allcount){

$('.ajax-load').show();

var url = "pagination.php";

$('#row').val(row);

$.ajax({

url: url,

type: 'post',

data: {row:row},

success: function(response){

$('.ajax-load').hide();

$(".product:last").after(response).show().fadeIn("slow");

}

});

}

else{

$('#remove').remove();

$(".product:last").after('<tr id="remove"><td colspan="4" style="text-align: center;"><b>No Data Available</b></td></tr>');

}

});

</script>

<style>

table {

border-collapse: collapse;

border-spacing: 0;

width: 100%;

border: 1px solid #ddd;

}

th,td {

text-align: left;

padding: 8px;

}

tr:nth-child(even){background-color: #f2f2f2}

</style>

</head>

<body>

<?php

include('config.php');

$row= 0;

$row_per_page = 10;

$count_product_query = "SELECT count(*) as allcount FROM products";

$count_product_result = mysqli_query($conn,$count_product_query);

$count_product_fetch = mysqli_fetch_array($count_product_result);

$all_product_count = $count_product_fetch['allcount'];

<h2>Product Table</h2>

<thead>

<tr>

<th>Product Name</th>

<th>Description</th>

<th>Price</th>

<th>Create</th>

</tr>

</thead>

<tbody>

<?php

$query = "select * from products order by id desc limit $row,$row_per_page ";

$result = mysqli_query($conn,$query);

while($row = mysqli_fetch_array($result)){

</tr>

<?php } ?>

</tbody>

</table>

</div>

</div>

</body>

</html>