Infinite scroll displays a limited number of records on the page, new records automatically load when the page scroll reached the bottom of the page.
In this tutorial, I show how you can load Postgresql data on page scroll using jQuery, AJAX, and PHP.
Contents
1. Table structure
I am using posts the table in the example.
CREATE TABLE posts (
id serial PRIMARY KEY,
title varchar(100) NOT NULL,
description text NOT NULL,
link varchar(255) NOT NULL
)
2. Configuration
Create config.php file for database configuration.
Completed Code
<?php
$host = "localhost";
$user = "postgres";
$password = "root";
$dbname = "tutorial";
$con = pg_connect("host=$host dbname=$dbname user=$user password=$password");
if (!$con) {
die('Connection failed.');
}
3. HTML & PHP
I set 3 to $rowperpage(Change its value according to how many records you want to display on the page at a time.)
Fetch the total number of records from posts table and assign to $allcount variable.
Use $rowperpage in limit to fetch records. Loop on the fetched records and create layout.
Create 3 hidden fields –
- Store starting position. Set it to 0.
- Store the number of rows fetched at a time. Set it to 3.
- Store the total number of records.
Completed Code
<div class="container">
<?php
include "config.php";
// Row per page
$rowperpage = 3;
// counting total number of posts
$sql = "select count(*) as allcount from posts";
$result = pg_query($con,$sql);
$records = pg_fetch_assoc($result);
$allcount = $records['allcount'];
// select first 3 posts
$sql = "select * from posts order by id asc limit $rowperpage OFFSET 0";
$records = pg_query($con, $sql);
while ($row = pg_fetch_assoc($records)) {
$id = $row['id'];
$title = $row['title'];
$content = $row['description'];
$shortcontent = substr($content, 0, 160)."...";
$link = $row['link'];
?>
<div class="post" id="post_<?php echo $id; ?>">
<h2><?php echo $title; ?></h2>
<p>
<?php echo $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. AJAX
Create ajaxfile.php file.
Set default value of $start to 0 and $rowperpage to 3. If values are POST then assign the values to the variables.
Fetch records from posts table where use POST $rowperpage and $start in LIMIT. Loop on the fetched records and create HTML layout and assign to the $html variable.
Return $html.
Completed Code
<?php
## Database configuration
include 'config.php';
$start = 0;$rowperpage = 3;
if(isset($_POST['start'])){
$start = $_POST['start'];
}
if(isset($_POST['rowperpage'])){
$rowperpage = $_POST['rowperpage'];
}
## Fetch records
$sql = 'select * from posts order by id desc limit '.$rowperpage.' OFFSET '.$start;
$records = pg_query($con, $sql);
$html = '';
while ($row = pg_fetch_assoc($records)) {
$id = $row['id'];
$title = $row['title'];
$content = $row['description'];
$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. jQuery
Create 2 functions –
- checkWindowSize() – Using this function to check height to load new content by calling
fetchData()function. Also, calling this function on the page first time load to display new content if the page does not has enough content. - fetchData() – Using this function to fetch records.
Read values from hidden elements and assign them to the variables. Add rowperpage with start and check if it is <= allcount, if it is then send AJAX POST request to ajaxfile.php, pass start and rowperpage as data.
On successful callback add response after last class="post" element and again check window size by calling checkWindowSize().
Define 2 events –
- scroll – Detect page scroll and check if scroll reaches bottom or not. If reached then call
fetchData()to fetch new records. - touchmove – This is for mobile devices to detect scroll.
Completed Code
checkWindowSize();
// Check if the page has enough content or not. If not then fetch records
function checkWindowSize(){
if($(window).height() >= $(document).height()){
// Fetch records
fetchData();
}
}
// Fetch records
function fetchData(){
var start = Number($('#start').val());
var allcount = Number($('#totalrecords').val());
var rowperpage = Number($('#rowperpage').val());
start = start + rowperpage;
if(start <= allcount){
$('#start').val(start);
$.ajax({
url:"ajaxfile.php",
type: 'post',
data: {start:start,rowperpage: rowperpage},
success: function(response){
// Add
$(".post:last").after(response).show().fadeIn("slow");
// Check if the page has enough content or not. If not then fetch records
checkWindowSize();
}
});
}
}
$(document).on('touchmove', onScroll); // for mobile
function onScroll(){
if($(window).scrollTop() > $(document).height() - $(window).height()-100) {
fetchData();
}
}
$(window).scroll(function(){
var position = $(window).scrollTop();
var bottom = $(document).height() - $(window).height();
if( position == bottom ){
fetchData();
}
});
6. CSS
.container{
width: 55%;
margin: 0 auto;
border: 0px solid black;
padding: 10px 0px;
}
/* post */.post{
width: 97%;
min-height: 200px;
padding: 5px;
border: 1px solid gray;
margin-bottom: 15px;
}
.post h2{
letter-spacing: 1px;
font-weight: normal;
font-family: sans-serif;
}
.post p{
letter-spacing: 1px;
text-overflow: ellipsis;
line-height: 25px;
}
/* more link */.more{
color: blue;
text-decoration: none;
letter-spacing: 1px;
font-size: 16px;
}
7. Output
8. Conclusion
Adjust $rowperpage value according to your requirement. This code works even if the page has less content on the first time load.
You can view the MySQL version of this tutorial here.
If you found this tutorial helpful then don't forget to share.