08 Dec, 2023

Load More data from the database using AJAX, jQuery in PHP with MySQL

By Sinhcoms LLP25 Sep, 202326 mins read 302 views

Load More data from the database using AJAX, jQuery in PHP with MySQL, Load more data on button click using jQuery, AJAX, and PHP

We’ll explain to you how to load more data from the database using AJAX, jQuery in PHP with MySQL.

Files on Github

It is nothing but one type of pagination. If you have seen some social media or e-commerce sites that use the Load More data functionality to view dynamic data. This functionality loads the data from the database without page refresh using AJAX. In this article, we will show you how to show more data on button click using AJAX , jQuery and PHP .

Here, we will implement the load more data feature instead of displaying the pagination link.

output-load-more-data-from-the-database-using-ajax-jquery-in-php-with-mysql-clue-mediator

Table of contents [Show]

File Structure
1. Create database table
2. Database connection
3. Create HTML page
4. Add CSS
5. Add Ajax call
6. Create PHP action file
7. Output

File Structure

load-more-data-php
- db_config.php
- index.php
- loadmore-data.php

1. Create database table

First of all, we will create a table named posts in the demo database. Run the following SQL script to create a table with dummy records in the database.

# Create orders table

CREATE TABLE`posts`(

id int(11) NOTNULL PRIMARY KEY AUTO_INCREMENT,

title varchar(255) NOT NULL,

status varchar(255) NOT NULL,

created_date date NOT NULL

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# Inserting data in the posts table

INSERT INTO`posts`(`id`,`title`,`status`,`created_date`)VALUES

(1,'Replace image src in HTML using PHP','publish','2020-01-31'),

(2,'Get current page URL in PHP','publish','2020-02-19'),

(3,'Get keys from an associative array in PHP','draft','2020-02-20'),

(4,'Upload image using PHP','publish','2020-03-14'),

(5,'Delete all files from a folder in PHP','draft','2020-04-02'),

(6,'Check username availability using PHP and jQuery','publish','2020-05-08'),

(7,'Get location from an IP address in PHP','publish','2020-07-22'),

(8,'Create a REST API in PHP with MySQL','publish','2020-09-23'),

(9,'Find URLs in a string and make clickable links in PHP','publish','2020-12-29');

2. Database connection

We have to create a db_config.php file and add the following code to connect the database.

db_config.php

<?php

$dbhost="localhost";

$dbuser="root";

$dbpass="";

$db="demo";

$con=mysqli_connect($dbhost,$dbuser,$dbpass,$db) or die($con);

3. Create HTML page

Here, we will include a database connection file named db_config.php and show limited data (3 posts) with a Load More button. Here we will store the total post count and start value (offset) in the hidden text. So we can manage and fetch the next records (3 posts) from the database.

index.php

<?php

// include database connection file

include("db_config.php");

<!DOCTYPE html>

<html>

<head>

<title>Load More data from database using AJAX,jQuery andPHP</title>

</head>

<body>

<h2>Load More data from database using PHP</h2>

<?php

$count_query="SELECT count(*) as allcount FROM posts";

$count_result=mysqli_query($con,$count_query);

$count_fetch=mysqli_fetch_array($count_result);

$postCount=$count_fetch['allcount'];

$limit=3;

$query="SELECT * FROM posts ORDER BY id desc LIMIT 0,".$limit;

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

if($result->num_rows> 0){

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

<?php

}

}?>

</div>

</div>

</body>

</html>

4. Add CSS

Now, we will add the following CSS in the index.php file before the closing head (</head>) tag for the basic UI style.

.container {

width:650px;

}

.post {

background-color:#F1F1F1;

margin:5px15px2px;

padding:8px;

font-size:14px;

line-height:1.5;

}

#loadBtn {

background-color:#499749;

padding:8px17px;

color:#fff;

border-radius:5px;

font-size:17px;

}

.loadmore {

text-align:center;

margin-top:10px;

}

</style>

5. Add Ajax call

In this step, we need to include the following jQuery library in the head section to load more data from the database without page refresh.

1	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

Now, we will need to add a jQuery script to handle the load more functionality. When the Load More button is clicked, the AJAX request is sent to the loadmore-data.php file.

Set the row value to hidden text to get the next record.
Append the response to the div with .postList class.
The rowCount greater than post count then hide the Load More button.

Add the following script in the index.php file before the closing body (</body>) ta

$(document).ready(function(){

$(document).on('click','#loadBtn',function(){

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

var count=Number($('#postCount').val());

var limit=3;

row=row+limit;

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

$("#loadBtn").val('Loading...');

$.ajax({

type:'POST',

url:'loadmore-data.php',

data:'row='+row,

success:function(data){

var rowCount=row+limit;

$('.postList').append(data);

if(rowCount>=count){

$('#loadBtn').css("display","none");

}else{

$("#loadBtn").val('Load More');

}

});

</script>

6. Create PHP action file

Let’s create a php file named loadmore-data.php. The Ajax request is sent to this PHP file and then retrieves the next 3 post data from the database based on the $_POST[‘row’] value and returns the HTML layout.

loadmore-data.php

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15	<?php include('db_config.php'); if(isset($_POST['row'])){ $start=$_POST['row']; $limit=3; $query="SELECT * FROM posts ORDER BY id desc LIMIT ".$start.",".$limit; $result=mysqli_query($con,$query); if($result->num_rows> 0){ while($row=mysqli_fetch_assoc($result)){ ?> <div class="post"><?php echo $row['title'];?></div> <?php} } } ?>
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

7. Output

Run the project and check the output in the browser.

I hope you find this article is helpful.
Thank you for reading. Happy Coding..!! ?