Infinite Scroll Pagination using PHP and jQuery returns only few posts and halts with a loader gif

I m trying to replicate code found online for Infinite scroll using PHP and JQuery.

But unfortunately, there is a flaw in the code which makes it only to return 7 posts only and stops fetching other posts upon scrolling down leaving a loader gif at the bottom.

Generally, I wouldn’t have asked this question, but the code seems pretty nice (in laymans terms) which I presume will be very helpful for rookies like me in the community.

Meanwhile will search other resources and try to answer it by myself.

My code Goes as :

Index.php

<div class="post-wall">
        <div id="post-list">
            <?php
            require_once ('db.php');
            $sqlQuery = "SELECT * FROM tbl_posts";
            $result = mysqli_query($conn, $sqlQuery);
            $total_count = mysqli_num_rows($result);
            
            $sqlQuery = "SELECT * FROM tbl_posts ORDER BY id DESC LIMIT 7";
            $result = mysqli_query($conn, $sqlQuery);
            ?>
            <input type="hidden" name="total_count" id="total_count"
            value="<?php echo $total_count; ?>" />

            <?php
            while ($row = mysqli_fetch_assoc($result)) {
                $content = substr($row['content'], 0, 100);
                ?>
                <div class="post-item" id="<?php echo $row['id']; ?>">
                    <p class="post-title"><?php echo $row['title']; ?></p>
                    <p><?php echo $content; ?></p>
                </div>
                <?php
                }
                ?>
            </div>
            <div class="ajax-loader text-center">
                <img src="LoaderIcon.gif"> Loading more posts...
            </div>
    </div>

<script type="text/javascript">
$(document).ready(function(){
        windowOnScroll();
});
function windowOnScroll() {
       $(window).on("scroll", function(e){
        if ($(window).scrollTop() == $(document).height() - $(window).height()){
            if($(".post-item").length < $("#total_count").val()) {
                var lastId = $(".post-item:last").attr("id");
                getMoreData(lastId);
            }
        }
    });
}

function getMoreData(lastId) {
       $(window).off("scroll");
    $.ajax({
        url: 'getMoreData.php?lastId=' + lastId,
        type: "get",
        beforeSend: function ()
        {
            $('.ajax-loader').show();
        },
        success: function (data) {
               setTimeout(function() {
                $('.ajax-loader').hide();
            $("#post-list").append(data);
            windowOnScroll();
               }, 1000);
        }
   });
}
</script>

getMoreData.php

<?php
require_once('db.php');

$lastId = $_GET['lastId'];
$sqlQuery = "SELECT * FROM tbl_posts WHERE id < '" .$lastId . "' ORDER BY id DESC LIMIT 7";

$result = mysqli_query($conn, $sqlQuery);


while ($row = mysqli_fetch_assoc($result))
 {
    $content = substr($row['content'],0,100);
    ?>
    <div class="post-item" id="<?php echo $row['id']; ?>">
        <p class="post-title">  <?php echo $row['title']; ?></p>
        <p><?php echo $content; ?></p>
    </div>
    <?php
}
?>

Any help is greatly appreciated.

Answer

I would set this up differently using classes and controllers etc, but as simple scripts, I might set it up something like:

Create a file called getData.php with this content:

<?php

require_once('db.php');

if (! function_exists('getData')) {
    /**
     * @param int $offset
     * @param int $limit
     * @return array|null
     */
    function getData($offset, $limit, $conn) {
         $offset = (int)$offset;
         $limit  = (int)$limit;
         $sqlQuery = "SELECT * FROM tbl_posts ORDER BY id DESC LIMIT $limit OFFSET $offset";
         $result = mysqli_query($conn, $sqlQuery);
         $rows = [];
         while ($row = mysqli_fetch_assoc($result)) {
             $rows[]= $row;
         }
         return $rows;
     }
}

Create another file called index.php with this content:

<?php
require_once ('getData.php');

$offset = (int)($_GET['offset'] ?? 0);
$dataOnly = (int)($_GET['dataOnly'] ?? 0);
$limit = 7;
$rows = getData($offset, $limit, $conn);
$offset+= $limit;
$data = [
    'rows' => $rows,
    'offset' => $offset,
];


$data = json_encode($data);

// if this is an ajax call, stop here and just spit out our json
if ($dataOnly) {
    echo $data;
    exit;
}
// otherwise, render the page
$sqlQuery = "SELECT * FROM tbl_posts";
$result = mysqli_query($conn, $sqlQuery);
$total_count = mysqli_num_rows($result);
?>

<!DOCTYPE html>
<html>
<head>
    <script type="text/javascript" src="jquery-3.2.1.min.js"></script>
    <style type="text/css">
        body {
            font-family: Arial;
            background: #e9ebee;
            font-size: 0.9em;
        }

        .post-wall {
            background: #FFF;
            border: #e0dfdf 1px solid;
            padding: 20px;
            border-radius: 5px;
            margin: 0 auto;
            width: 500px;
        }

        .post-item {
            padding: 10px;
            border: #f3f3f3 1px solid;
            border-radius: 5px;
            margin-bottom: 30px;
        }

        .post-title {
            color: #4faae6;
        }

        .ajax-loader {
            display: block;
            text-align: center;
        }
        .ajax-loader img {
            width: 50px;
            vertical-align: middle;
        }
    </style>
</head>
<body>
<div class="post-wall">
    <div id="post-list">
        <input type="hidden" name="total_count" id="total_count" value="<?= $total_count ?>" />
        <input type="hidden" name="offset" id="offset" value="<?= $offset ?>" />
    </div>
    <div class="ajax-loader text-center">
        <img src="LoaderIcon.gif"> Loading more posts...
    </div>
</div>

<script type="text/javascript">
    $(document).ready(function(){
// load the initial rows on page load
        let initialData = <?= $data ?? '' ?>;
        if (initialData) {
            if (initialData.rows) {
                addrows(initialData.rows);
                $('.ajax-loader').hide();
            }
        }
        windowOnScroll();

    });
    function windowOnScroll() {
        $(window).on("scroll", function(e){
            if ($(window).scrollTop() == $(document).height() - $(window).height()){
                console.log('test');
                if($(".post-item").length < $("#total_count").val()) {
                    let offset = $('#offset').val();
                    getMoreData(offset)
                }
            }
        });
    }

    function getMoreData(offset) {
        $('.ajax-loader').show();
        $(window).off("scroll");
        let pageUrl = window.location.href.split('?')[0];
        $.ajax({
            url: pageUrl + '?dataOnly=1&offset=' + offset,
            type: "get",
            success: function (response) {
                response = JSON.parse(response);
                if (response.rows) {
                    addrows(response.rows);
                    if (response.offset) {
                        $('#offset').val(response.offset);
                    }
                    $('.ajax-loader').hide();
                }
                windowOnScroll();
            }
        });
    }

    function addrows(rows) {
        let postList = $("#post-list");
        $.each(rows, function (i, row) {
            let rowHtml = '<div class="post-item" id="'+row.id+'"><p class="post-title">'+row.title+'</p><p>'+row.content+'</p></div>';
            postList.append(rowHtml);
        });
    }
</script>
</body>
</html>

Now, I cant test this locally so there might be an error or two in there, but that should give you the general idea.

One thing im not 100% sure of is that if ($(window).scrollTop() == $(document).height() - $(window).height()){ condition.

XSS warning

You dont show how these “posts” get added to the database, presumably they come from user submissions on some other form. If that is the case, make sure that you understand what XSS vulnerabilities are and how to prevent them