Unable to load more data when reach bottom in AJAX

Question

I am trying to load more data for SQL when reached bottom but when I try with this code, no data is loaded from SQL.

How do I add infinity scroll to this code?

I tried to debug by echo $query and I get

SELECT * FROM allpostdata WHERE sts = '1' AND mca='Vehicle' ORDER BY pdt DESC LIMIT 4 OFFSET 10

I get the right query and I also checked for console error but I get nothing, no data is loaded from SQL.

Can someone help me solve this?

 var flag = 0;
        $(window).scroll(function () {
            if ($(window).scrollTop() >= $(document).height() - $(window).height() - 300)
            { //RUN when the page is almost at the bottom
                flag += 4; //AUTO IN INCREMENT
                $(document).ready(function () {
                    filter_data();
                    function filter_data() {
                        $.post(
                                "fetch.php",
                                {
                                    action: 'fetch_data',
                                    cate: get_filter('cate'),
                                    brand: get_filter('brand'),
                                    model: get_filter('model'),
                                    sort: get_filter('sort'),
                                    date: get_filter('date'),
                                    offset: flag,
                                    limit: 4
                                }
                        )
                                .done(function (data) {
                                    $('.filter_data').html(data);
                                });
                    }
                    function get_filter(class_name) {
                        var filter = [];
                        $('.' + class_name + ':checked').each(function () {
                            filter.push($(this).val());
                        });
                        return filter;
                    }
                    $('.filter_all').click(function () {
                        filter_data();
                    });
                });
            }
        });

and here is PHP

    if (isset($_POST["action"])) {
    $query = "SELECT * FROM allpostdata WHERE sts = '1' AND mca='Vehicle'";

    if (!empty($_POST['cate'])) {
        $query .= " AND sca IN (" . str_repeat("?,", count($_POST['cate']) - 1) . "?)";
    } else {
        $_POST['cate'] = []; // in case it is not set 
    }

    if (!empty($_POST['brand'])) {
        $query .= " AND product_brand IN (" . str_repeat("?,", count($_POST['brand']) - 1) . "?)";
    } else {
        $_POST['brand'] = []; // in case it is not set 
    }

    if (!empty($_POST['model'])) {
        $query .= " AND mdl IN (" . str_repeat("?,", count($_POST['model']) - 1) . "?)";
    } else {
        $_POST['model'] = []; // in case it is not set 
    }

    if (empty($_POST['sort']) || $_POST['sort'][0] == "date") {
        $query .= " ORDER BY pdt DESC";
    } elseif ($_POST["sort"][0] == "ASC" || $_POST["sort"][0] == "DESC") {
        $query .= " ORDER BY prs " . $_POST['sort'][0];
    }

    if (!empty($_POST['offset']) && isset ($_POST['limit'])) {
        $query .= " LIMIT ".$_POST['limit']." OFFSET ".$_POST['offset']."";
    } else {
        $query .=""; // in case it is not set 
    }
echo $query;
    $stmt = $conn->prepare($query);
    $params = array_merge($_POST['cate'], $_POST['brand'], $_POST['model']);
    $stmt->execute($params);
    $result = $stmt->fetchAll();
    $total_row = $stmt->rowCount();
    $output = '';
    if ($total_row > 0) {
        foreach ($result as $row) {
            $parameter = $row['pid'];
            $hashed = md5($salt . $parameter);
            $output .= '<a href="/single_view.php?p=' . $parameter . '&s=' . $hashed . '" class="w-xl-20 w-lg-20 col-md-3 col-6 p-1 p-lg-2">
                            <div class="card border-0 small">
                                <img class="card-img-top rounded-0" src="/upload/thumb/' . $row["im1"] . '" alt="Card image cap">
                                <div class="card-body pb-0 pt-2 px-0">
                                    <h6 class="card-title text-dark text-truncate">' . ucfirst(strtolower($row['tit'])) . '</h6>
                                    <h6 class="card-subtitle mb-1 text-muted text-truncate small">' . $row['product_brand'] . '&nbsp;/&nbsp;' . $row['mdl'] . '</h6>
                                    <p class="card-text"><strong class="card-text text-dark text-truncate">&#x20B9;&nbsp;' . $row['prs'] . '</strong></p>' . timeAgo($row['pdt']) . '
                                </div>
                            </div>
                        </a>';
        }
    } else {
        $output = '<h3>No Data Found</h3>';
    }
    echo $output;
}

can some one help me how do i load data when scorlled to bottom

before adding load to scroll method

$(document).ready(function () {
            filter_data();
            function filter_data() {
                $.post(
                        "fetch.php",
                        {
                            action: 'fetch_data',
                            cate: get_filter('cate'),
                            brand: get_filter('brand'),
                            model: get_filter('model'),
                            sort: get_filter('sort'),
                            date: get_filter('date')
                        }
                )
                        .done(function (data) {
                            $('.filter_data').html(data);
                        });
            }
            function get_filter(class_name) {
                var filter = [];
                $('.' + class_name + ':checked').each(function () {
                    filter.push($(this).val());
                });
                return filter;
            }
            $('.filter_all').click(function () {
                filter_data();
            });
        });

All i want is when it is scroll to bottom of page it should load next set from DB.

Take a look at this Question: https://stackoverflow.com/questions/3898130/check-if-a-user-has-scrolled-to-the-bottom — Richard, Dec 31 '19 at 12:32
@Richard but when i reach bottom my `offset` value gets changed. — sanoj lawrence, Dec 31 '19 at 12:36
How many rows are there in `allpostdata`? That first query would return no rows if there are fewer than 10 rows in the table. And if `flag += 4;` how could offset be 10? — DinoCoderSaurus, Dec 31 '19 at 13:57
@DinoCoderSaurus i have 17 rows (now testing will add in future). `flag += 4;` so what is the solution — sanoj lawrence, Dec 31 '19 at 16:01
No sir. Have you figured out why offset is 10 when it should be a multiple of 4? I misspoke, it's not simply "how many rows in the table", but rather how many rows in the table meet the WHERE criteria? Does that query give you the expected result when run in sqlite3 or your favorite admin tool? — DinoCoderSaurus, Jan 02 '20 at 18:16

sergej · Accepted Answer · 2020-01-06T02:51:57.520

I think you almost did it right.

to get infinite scroll working you need to add the following lines at the bottom of your first script:

var $window = $( window );
var $document = $( document );

$window.scroll(function () {

    // In some cases `$document.height()` is equal to `$window.height()`, 
    // so you can use the upper condition
    //if ( ( window.innerHeight + window.scrollY ) >= document.body.offsetHeight - 300 ) {
    if ( $window.scrollTop() >= $document.height() - $window.height() - 300 ) {

        console.log('infinite scroll');
        flag += 4; // AUTO IN INCREMENT
        filter_data();
    }
});

Here the fiddle:

Condition 1: https://jsfiddle.net/ra6Lmy54/3/
Condition 2: https://jsfiddle.net/8d7qtmy1/

To prevent multiple requests and stop infinite scroll if all data has been loaded, you can add these two semaphores: fetching and done

// init at the top of your script
var fetching = false;
var done = false;

// add these two lines at the beginning of filter_data
function filter_data() {
    // prevent concurrent requests
    if(fetching === true) { return; }
    fetching = true;

// ...
// condition in the scroll handler should look like this
if ( 
    $window.scrollTop() >= $document.height() - $window.height() - 300 &&
    fetching === false && done === false 
) {

// ...
// ajax success and error handler
.done( function (data) {

    console.log('data received');

    // append new data
    if(data !== '<h3>No Data Found</h3>') {
         //$('.filter_data').html(data); // override
         $('.filter_data').append(data); // append              
    }
    // we reached the end, no more data
    else {

        if(flag === 0) $('.filter_data').append(data); // display initially "no data found"
        done = true;
    }

    flag += 4; // move here. increment only once on success
    fetching = false; // allow further requests again
})
.fail( function( error ) {

    console.log( 'An error occurred while fetching', error )
    // TODO: some error handling
});

Here the full fiddle: https://jsfiddle.net/fus6hyar/

Possible reasons why it does not work:

Sorry for the dumb question but I don't know your html markup. Is you scroll height > window height?
- here is the same code but with a content height < window height. You can see that the scroll event is not fired in this case (because there is nothing to scroll).
- https://jsfiddle.net/623wmbut/4/
If your scroll handler is working and infinite scroll will be triggered. You should check if your request is fine. Add an error handler to the post request, check if something is wrong with your request and add some logs. One of the logs must appear in the console or you have something like a server timeout error or the request is not send correctly. However, if you get a response from the server (or not), you will see if your server data is correct or not and if you need to debug your php/sql code or check your javascript further.

.done( function (data) {

    console.log('data received', data);
    // ...
})
.fail( function( error ) {

    console.log( 'An error occurred while fetching', error )
    // some error handling ...
});

If data has been received but still won't be rendered, check if the selector is still correct/available:

.done( function (data) {

    console.log('data received', data);
    console.log($('.filter_data')); // can this element be found?
    // ...
})

If all these things does not work, you can try to add more logs at the beginning of you js script like console.log($(window)) to check if you initialised JQuery correctly. Finally you can add your html markup to your question, maybe there is an error.

Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/205305/discussion-between-sergej-and-sanoj-lawrence). — sergej, Jan 03 '20 at 16:23

Unable to load more data when reach bottom in AJAX

1 Answers1