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

by Clue Mediator · February 24, 2021

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

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.

Preview

Output - Load More data from the database using AJAX, jQuery in PHP with MySQL - Clue Mediator — Output – Load More data from the database using AJAX, jQuery in PHP with MySQL – Clue Mediator

Steps to load more data from database using PHP

Create database table
Database connection
Create HTML page
Add CSS
Add Ajax call
Create PHP action file
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) NOT NULL 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');

# Create orders table

CREATE TABLE `posts` (

id int(11) NOT NULL 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);
?>

<?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 and PHP - Clue Mediator</title>
</head>
<body>
  <div class="container">
    <h2>Load More data from database using PHP - <a href="https://www.cluemediator.com" target="_blank" rel="noopener noreferrer">Clue Mediator</a></h2>
    <div class="postList">
      <?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)){ 
          ?>
          <div class="post"><?php echo $row['title']; ?></div>
        <?php }
      } ?>	
    </div>
    <div class="loadmore">
      <input type="button" id="loadBtn" value="Load More">
      <input type="hidden" id="row" value="0">
      <input type="hidden" id="postCount" value="<?php echo $postCount; ?>">
    </div>	
  </div>
</body>
</html>

<?php

// include database connection file

include("db_config.php");

<!DOCTYPE html>

<html>

<head>

<title>Load More data from database using AJAX, jQuery and PHP - Clue Mediator</title>

</head>

<body>

<h2>Load More data from database using PHP - <a href="https://www.cluemediator.com" target="_blank" rel="noopener noreferrer">Clue Mediator</a></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.

<style type="text/css">
  .container {
    width:650px;
  }
  .post {
    background-color: #F1F1F1;
    margin: 5px 15px 2px;
    padding: 8px;
    font-size: 14px;
    line-height: 1.5;
  }
  #loadBtn {
    background-color: #499749;
    padding: 8px 17px;
    color: #fff;
    border-radius: 5px;
    font-size: 17px;
  }
  .loadmore {
    text-align: center;
    margin-top: 10px;
  }
</style>

.container {

width:650px;

}

.post {

background-color: #F1F1F1;

margin: 5px 15px 2px;

padding: 8px;

font-size: 14px;

line-height: 1.5;

}

#loadBtn {

background-color: #499749;

padding: 8px 17px;

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.

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

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>) tag.

<script>
  $(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>

$(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

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

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

<?php }

}

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..!! 🙂

Demo & Source Code

GitHub Repository

If you found value in this article,
you can support us by buying me a coffee! ☕

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

Preview

Steps to load more data from database using PHP

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

Demo & Source Code

You may also like...

Leave a Reply Cancel reply

Search your query

JOIN OUR NEWSLETTER

Recent Posts

Join us

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

Preview

Steps to load more data from database using PHP

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

Demo & Source Code

You may also like...

How to Check If an Array Contains All Elements of Another Array in PHP

Calculate the age from date of birth in PHP

Get location from an IP address in PHP

Remove keys from an associative array in PHP

Encode and Decode JSON Web Tokens in PHP

How to remove duplicate values from an array in PHP

Leave a Reply Cancel reply

Search your query

JOIN OUR NEWSLETTER

Recent Posts

Tags

Join us