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′]
