Load more data using AJAX – PHP

by Posted on

Learn how you can show a “Load more” button using AJAX, PHP and MySQL. No external library has been used in this tutorial.

What is “Load more” button ?

By “Load more” we mean creating a button that allows you to show only 10 or 20 records and on clicking that button will fetch next 10 records from database without having to refresh the page. This function is heavily used in tech giant Facebook where older posts are fetched by scrolling without refreshing the page.

We will be using a sample database called classicmodels, it is attached in the source files below.

Create layout

We will be using employees table and displaying employeeNumber, firstName, lastName and email fields. A unique ID should be provided to <tbody> which will be used to append data using Ajax.

<table>
    <thead>
        <tr>
            <th>Number</th>
            <th>First name</th>
            <th>Last name</th>
            <th>Email</th>
        </tr>
    </thead>

    <tbody id="data"></tbody>
</table>

<button type="button" onclick="getData();">
    Load More
</button>

Display initial records

We are displaying 10 records in each step and we will need just 1 variable for this. start to tell the starting position of fetching data and it will be incremented in each step.

<script>
    // Starting position to get new records
    var start = 0;

    // This function will be called every time a button pressed 
    function getData() {
        // Creating a built-in AJAX object
        var ajax = new XMLHttpRequest();

        // Sending starting position
        ajax.open("GET", "Http.php?start=" + start, true);

        // Actually sending the request
        ajax.send();

        // Detecting request state change
        ajax.onreadystatechange = function () {

            // Called when the response is successfully received
            if (this.readyState == 4 && this.status == 200) {

                // For debugging purpose only
                console.log(this.responseText);
            }
        };
    }

    // Calling the function on page load
    getData();
</script>

At this point if you run the program, you will see an empty table and a button, now you need to create Http.php file to handle each request.

Handling AJAX request – PHP

Create a new file and name it Http.php, in this file we will simply get the offset (start variable) and display the records based on that value.

<?php

// Connecting with database
$connection = mysqli_connect("localhost", "root", "", "classicmodels");

// Getting offset to show next records
$start = $_GET["start"];

// Executing the query based on $start variable
$sql = "SELECT * FROM employees LIMIT $start, 10";
$result = mysqli_query($connection, $sql);

// Storing all returned records in an array
$data = array();
while ($row = mysqli_fetch_object($result))
    array_push($data, $row);

// Sending response back to AJAX
echo json_encode($data);

Now if you view your browser console, you will see some JSON string data when the page loads.

Appending records in table

Now you need to convert that JSON string into Javascript array and append in <tbody> tag. Paste the following code back in your index.php file:

ajax.onreadystatechange = function () {
    if (this.readyState == 4 && this.status == 200) {
        console.log(this.responseText);
        
        // Converting JSON string to Javasript array
        var data = JSON.parse(this.responseText);
        var html = "";

        // Appending all returned data in a variable called html
        for (var a = 0; a < data.length; a++) {
            html += "<tr>";
                html += "<td>" + data[a].employeeNumber + "</td>";
                html += "<td>" + data[a].firstName + "</td>";
                html += "<td>" + data[a].lastName + "</td>";
                html += "<td>" + data[a].email + "</td>";
            html += "</tr>";
        }

        // Appending the data below old data in <tbody> tag
        document.getElementById("data").innerHTML += html;

        // Incrementing the offset so you can get next records when that button is clicked
        start = start + 10;
    }
};

So for example, first time you will get employees from 1 to 10 and next time you hit load more button you will get employees from 11 to 20 and so on.

That’s how you can show a “Load more” button using AJAX, PHP and MySQL. You can also create a load more button in Node JS and Mongo DB. Learn from here how you can do that.

[wpdm_package id=’138′]

Published by adnanafzal565