Category

UI/UX & Front-end

Category

Hello folks,
You might have seen the feature of Load More. If you want to develop such a feature as “Load more data using jQuery” then you are in right place. You check this demo also before starting to code.

It can be for anything like load more products if you are in e-commerce website, It can be load more post you are visiting any blog website or in general, it can be load more records.
It’s a kind of pagination, yes but it will have a Load More button instead pagination links or page numbers. We will talk about other pagination as well in the upcoming article. For now, we will develop this as a Load more option. However, if you want to check we already have a similar article.

Check Also: How to integrate data table plugin in HTML and CSS

DataTable is a plug-in for the jQuery JavaScript library. It is a highly flexible tool, built upon the foundations of progressive enhancement, that adds all of these advanced features to any HTML table

In this post, we are going to implement a kind of pagination called Load More. We will load Quotes. 
By default, we will load 2 quotes in this example but you can define the number in the constant file or customize this solution as per your requirement.

Now when you click on the Load more button it will load another 2 quotes and append them to the current list. This process will go so on and so far until there are no remaining quotes. The load more button will be hidden if there are no more quotes remaining to load. So let’s start to develop load more data using jQuery, and AJAX.

Step 1: Database Setup

Since we are fetching all quotes/records from the database first of all we need to set up the database. Create a database, and create a table “bird_quotes” because we take this table name in code. In the next section, you will get the SQL query for the same.

Step 2: Code & Files setup

Now we need to create these files as shown in the below screenshot, I will explain the purpose of these files. Now let’s create code for each file and understand the purpose also.

Step 3: Lets code

constant.php

The purpose of creating this is to store database credentials & pagination settings. In the third part of this article, we considered loading 2 records each time. So this number can be changed from the constant.php file

<?php
/**
 * Database configuration
 */
define('DB_HOST', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'codingbirds');

/**
 * Define no. of records to be load each time
 */
define('PER_PAGE', 2);

Database.php

This file is a PHP class of database connection. As you know we require a database connection with PHP, So here is the code for this file.

<?php
include_once "constants.php";
class Database
{
    /**
     * @var mysqli
     */
    public $connection;

    public function __construct()
    {
        $this->connection = new mysqli(DB_HOST,DB_USERNAME,DB_PASSWORD,DB_NAME);
        if (!$this->connection) {
            die("Error in database connection". $this->connection->connect_error);
        }
    }
}

index.php

This index.php is the main file that gets loaded and shows the 2 quotes by default and shows “ajax load more” button UI. Below is the code for this file.

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <link rel="icon" href="https://codingbirdsonline.com/wp-content/uploads/2019/12/cropped-coding-birds-favicon-2-1-192x192.png" type="image/x-icon">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.8.2/css/all.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
    <script type="text/javascript" src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.bundle.min.js"></script>
    <title>How to load more records using php, mysql & AJAX - Coding Birds Online</title>
    <style> .blockquote  { font-size: 1.1rem !important; }</style>
</head>
<body>
<div class="container">
   <div class="mt-3 mb-3">
       <center>
           <img src="https://codingbirdsonline.com/wp-content/uploads/2020/01/cropped-coding-birds-online-favicon-180x180.png" width="50"/>
       </center>
   </div>
    <div class="row">
        <div class="col-sm-9 col-md-7 col-lg-5 mx-auto">
            <div id="quotes-container"></div>
            <input type="hidden" id="page" value="0"/>
            <button
                type="button"
                id="load-more-btn"
                onclick="loadMoreQuotes();"
                class="btn btn-sm btn-success btn-block"
                >Load more...
                <div id="loading" class="spinner-border spinner-border-sm d-none" role="status">
                    <span class="sr-only">Loading...</span>
                </div>
            </button>
        </div>
    </div>
    <div class="row">
        <script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
        <!-- middle-ads -->
        <ins class="adsbygoogle"
             style="display:block"
             data-ad-client="ca-pub-5191137430270102"
             data-ad-slot="7861133754"
             data-ad-format="auto"
             data-full-width-responsive="true"></ins>
        <script>
            (adsbygoogle = window.adsbygoogle || []).push({});
        </script>
    </div>
</div>
<script src="load-more-quotes.js"></script>
</body>
</html>

load-more-quotes.js

This file contains JavaScript & jQuery logic to load more data. loadMoreQuotes( ) function is called the first time automatically when the page load, later it is called with the click of a button.

$(document).ready(function(){
    /**
     * Call load quotes as soon as page ready
     */
    loadMoreQuotes();
});
function loadMoreQuotes(){
    let page = parseInt($("#page").val());
    page = page + 1;
    let quotesHtml = ``;
    $.ajax({
        url: "load-more-script.php",
        method: "POST",
        data: {
            request_name: "load-more-quotes",
            page: page,
        },
        beforeSend: function(){
            /**
             * Show the loading icon in load more button
             */
            $("#loading").removeClass('d-none');
        },
        success: function (response){
            /**
             * Prepare the html of quotes
             * @type {any}
             */
            response = JSON.parse(response);
            let quotes = response.data;
            if(quotes.length > 0) {
                $.each(quotes, function (key, value) {
                    quotesHtml += `<div class="card card-signin my-3">
                                    <div class="card-body">
                                        <blockquote class="blockquote mb-0">
                                            <p>${value.quote}</p>
                                            <footer class="blockquote-footer">Someone famous in <cite title="Source Title">${value.author}</cite></footer>
                                        </blockquote>
                                    </div>
                                </div>`;
                });
                /**
                 * Update the page page number to track the no. of calls
                 * Append quotes html prepared to div
                 * Conditionally show/hide the load more button
                 */
                $("#page").val(page);
                $("#quotes-container").append(quotesHtml);
                if (response.remaining_quotes == 0) {
                    $("#load-more-btn").hide();
                }
            }
        },
        error: function(errorResponse){
            console.error(errorResponse)
        },
        complete: function(){
            /**
             * Hide loading icon each time when api call completes
             */
            $("#loading").addClass('d-none');
        }
    })
}

load-more-script.php

This file is a PHP script file to perform our load more pagination from the backend. It returns the response in the form of JSON.

<?php
require_once "constants.php";
require_once "Database.php";
require_once "Quote.php";
/**
 * Prepares the Database, Quote class objects
 */
$db = new Database();
$quote = new Quote();
if (isset($_POST['request_name'])){
    if($_POST['request_name'] == 'load-more-quotes'){
        /**
         * Make the offset, limit
         */
        $offset = ($_POST['page'] - 1) * PER_PAGE;
        $limit = PER_PAGE;
        $quotesData = array();
        $pagination = array(
            'offset' => $offset,
            'limit' => $limit,
        );
        $quotes = $quote->loadQuotes($db->connection, $pagination);
        $totalQuotes = $quote->totalQuotes($db->connection);
        if($quotes){
            while ($quote = $quotes->fetch_object()){
                $quotesData[] = $quote;
            }
        }
        echo json_encode(array(
            'data' => $quotesData,
            'remaining_quotes' => $totalQuotes - PER_PAGE * $_POST['page'],
        ));
    }
}

Quote.php

This is a PHP class file which two functions to load the quotes and count total quotes respectively.

<?php
class Quote
{
    /**
     * Load the quotes based on limit. offset
     * @param $connection
     * @param $pagination
     * @return object
     */
    public function loadQuotes($connection, $pagination)
    {
        $query = "SELECT * FROM bird_quotes LIMIT ". $pagination['offset']. ",". $pagination['limit'];
        $result = $connection->query($query) or die("Error in query".$connection->error);
        return $result;
    }

    /**
     * Calculates the total quotes in database
     * @param $connection
     * @return int
     */
    public function totalQuotes($connection)
    {
        $query = "SELECT COUNT(*) FROM bird_quotes as quotes_count";
        $result = $connection->query($query) or die("Error in query".$connection->error);
        return $result->fetch_row()[0];
    }
}

SQL code to create table

-- phpMyAdmin SQL Dump
-- version 5.0.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Aug 10, 2022 at 08:44 AM
-- Server version: 10.4.14-MariaDB
-- PHP Version: 7.4.15

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `codingbirds`
--

-- --------------------------------------------------------

--
-- Table structure for table `bird_quotes`
--

CREATE TABLE `bird_quotes` (
  `id` int(11) NOT NULL,
  `quote` text NOT NULL,
  `author` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `bird_quotes`
--

INSERT INTO `bird_quotes` (`id`, `quote`, `author`) VALUES
(1, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 'Source Title'),
(2, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 'Source Title'),
(3, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 'Source Title'),
(4, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 'Source Title'),
(5, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 'Source Title'),
(6, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 'Source Title'),
(7, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 'Source Title'),
(8, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 'Source Title'),
(9, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 'Source Title'),
(10, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 'Source Title');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `bird_quotes`
--
ALTER TABLE `bird_quotes`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `bird_quotes`
--
ALTER TABLE `bird_quotes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Step 4: Run the code

Congrats! now you have reached the final step to run the code & get your desired output. As you navigate this localhost URL to your browser you will get something like as below.

Now if you want to load more quotes, just click on the button you will get 2 more quotes without page refresh since we are using AJAX. Why 2 quotes because we defined this value in the constants.php file, you can change it according to your needs.

Source Code & Demo

You can download the full 100% working source code from here. You check this demo also before starting to code.

Conclusion

I hope you learned what I explained in the above steps, If you have any suggestions are appreciated. And if you have any errors comment here, I will help. You can download the full 100% working source code from here.

Ok, Thanks for reading this article, see you in the next post. If this code works for you, please leave a comment so that others can find it useful which gives me the motivation to create such content.