Data is dynamically loaded on the page when the user reaches the bottom of the page while scrolling.
User doesn’t have to click on any action button to load new records. For doing this require sending AJAX request to fetch data on page scroll.
In this tutorial, I show how you can load content from MySQL database on page scroll using JavaScript and PHP.
Contents
1. Table structure
I am using posts table in the example. It has the following structure –
CREATE TABLE `posts` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `title` varchar(100) NOT NULL, `content` text NOT NULL, `link` varchar(255) NOT NULL );
2. Database Configuration
Create a config.php file for database configuration.
Completed Code
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = "root"; /* Password */
$dbname = "tutorial"; /* Database name */
// Create connection
$con = new mysqli($host, $user, $password, $dbname);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
3. HTML
On page first time load the initial 3 records from the posts table.
For this create $rowperpage = 3; variable. You can change its value according to your requirement.
Check the total number of records in posts table and assign it to $allcount variable.
Fetch records from posts table where use $rowperpage in LIMIT – limit 0,$rowperpage. Loop on the fetched records and create a layout to display data.
Create 3 hidden elements –
- start – To store the current row position. The default value is 0.
- rowperpage – Store the number of rows to fetch at a time.
- totalrecords – Total number of records.
Completed Code
<div class="container">
<?php
include "config.php";
// Row per page
$rowperpage = 3;
// Total number of records
$query = "SELECT count(*) as allcount FROM posts";
$result = mysqli_query($con,$query);
$row = mysqli_fetch_array($result);
$allcount = $row['allcount'];
// Select first 3 posts
$query = "select * from posts order by id asc limit 0,$rowperpage ";
$result = mysqli_query($con,$query);
while($row = mysqli_fetch_array($result)){
$id = $row['id'];
$title = $row['title'];
$content = $row['content'];
$shortcontent = substr($content, 0, 160)."...";
$link = $row['link'];
?>
<div class="post" id="post_<?= $id; ?>">
<h2><?= $title; ?></h2>
<p>
<?= $shortcontent; ?>
</p>
<a href="<?= $link ?>" target="_blank" class="more">More</a>
</div>
<?php
}
?>
<input type="hidden" id="start" value="0">
<input type="hidden" id="rowperpage" value="<?= $rowperpage ?>">
<input type="hidden" id="totalrecords" value="<?= $allcount ?>">
</div>
4. PHP
Create ajaxfile.php file to return data on page scroll.
Read POST values and assign to $postData. If start and rowperpage are POST then assign them to the variables.
Fetch records from posts table where use $start and $rowperpage in LIMIT.
Loop on the fetched records and create HTML layout.
Return the $html variable.
Completed Code
<?php
// configuration
include 'config.php';
// Read POST data
$postData = json_decode(file_get_contents("php://input"));
$start = 0;$rowperpage = 3;
if(isset($postData->start)){
$start = mysqli_real_escape_string($con,$postData->start);
}
if(isset($postData->rowperpage)){
$rowperpage = mysqli_real_escape_string($con,$postData->rowperpage);
}
// selecting posts
$query = 'SELECT * FROM posts limit '.$start.','.$rowperpage;
$result = mysqli_query($con,$query);
$html = '';
while($row = mysqli_fetch_array($result)){
$id = $row['id'];
$title = $row['title'];
$content = $row['content'];
$shortcontent = substr($content, 0, 160)."...";
$link = $row['link'];
// Creating HTML structure
$html .= '<div id="post_'.$id.'" class="post">';
$html .= '<h2>'.$title.'</h2>';
$html .= '<p>'.$shortcontent.'</p>';
$html .= "<a href='".$link."' target='_blank' class='more'>More</a>";
$html .= '</div>';
}
echo $html;
5. JavaScript
Detect page scroll using window.onscroll. Using this check if scroll position has reached the bottom or not. If it is then call fetchData() function to display new data.
Create 2 functions –
- checkWindowSize() – Using this function check if page has enough content according to screen size. If not then call
fetchData()function to load new data. Also, call this function on the page first loads. - fetchData() – Using this function fetch new data.
Read values from hidden fields and assign them to the variables. Add start with rowperpage and check if start <= allcount. If it is then update start element value and send AJAX POST request to ajaxfile.php.
Pass {start: start,rowperpage: rowperpage} as data. On successful callback add response after the last post class element.
Call checkWindowSize() function to again check if page has enough content to scroll or not.
Completed Code
checkWindowSize();
// Check if the page has enough content or not. If not then fetch records
function checkWindowSize(){
if(window.innerHeight >= document.body.clientHeight){
// Fetch records
fetchData();
}
}
// Detect scroll
window.onscroll = function (e) {
if(document.documentElement.scrollTop > document.body.clientHeight - window.innerHeight-100) {
fetchData();
}
}
// Fetch records
function fetchData(){
var start = Number(document.getElementById('start').value);
var allcount = Number(document.getElementById('totalrecords').value);
var rowperpage = Number(document.getElementById('rowperpage').value);
start = start + rowperpage;
if(start <= allcount){
document.getElementById('start').value = start;
// AJAX request
var xhttp = new XMLHttpRequest();
xhttp.open("POST", "ajaxfile.php", true);
xhttp.setRequestHeader("Content-Type", "application/json");
xhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
// Response
var response = this.responseText;
// Total number of post class
var len = document.getElementsByClassName("post").length;
// Add response HTML content after last post class
document.getElementsByClassName("post")[len-1].insertAdjacentHTML('afterend', response);
checkWindowSize();
}
};
var data = {start: start,rowperpage: rowperpage};
xhttp.send(JSON.stringify(data));
}
}
6. Demo
7. Conclusion
Adjust the number of records you want to display on the page scroll while implementing this in your project by updating the value of $rowperpage.
This code works even if the page does not have enough content on the page load.
If content not loading while the page scrolls then check the code again and debug it using the browser network tab.
You can view this tutorial if you want to know how you can implement this using jQuery and PHP.
If you found this tutorial helpful then don't forget to share.