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

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

Steps to load more data from database using PHP

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

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");
?-->




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


  <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;<p-->

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

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>

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>

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>

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

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