Infinite Scroll with MySQL Data

Question

I have followed help located in this topic: Using infinite scroll w/ a MySQL Database

And have gotten close to getting this working properly. I have a page that is displayed in blocks using jquery masonry, in which the blocks are populated by data from a mysql database. When I scroll to the end of the page I successfully get the loading.gif image but immediately after the image it says "No more posts to show." which is what it should say if that were true. I am only calling in 5 posts initially out of about 10-15, so the rest of the posts should load when I reach the bottom of the page but I get the message that is supposed to come up when there really aren't any more posts.

Here is my javascript:

var loading = false;
    $(window).scroll(function(){
        if($(window).scrollTop() == $(document).height() - $(window).height()) {   
            var h = $('.blockContainer').height();
            var st = $(window).scrollTop();
            var trigger = h - 250;

              if((st >= 0.2*h) && (!loading) && (h > 500)){
                loading = true;
                $('div#ajaxLoader').html('<img src="images/loading.gif" name="HireStarts Loading" title="HireStarts Loading" />');
                $('div#ajaxLoader').show();
                $.ajax({
                    url: "blocks.php?lastid=" + $(".masonryBlock:last").attr("id"),
                    success: function(html){
                        if(html){
                            $(".blockContainer").append(html);
                            $('div#ajaxLoader').hide();
                        }else{
                            $('div#ajaxLoader').html('<center><b>No more posts to show.</b></center>');
                        }
                    }
                });
            }
        }
    });

Here is the php on the page the blocks are actually on. This page initially posts 5 items from the database. The javascript grabs the last posted id and sends that via ajax to the blocks.php script, which then uses the last posted id to grab the rest of the items from the database.

$allPosts = $link->query("/*qc=on*/SELECT * FROM all_posts ORDER BY post_id DESC LIMIT 5");
        while($allRows = mysqli_fetch_assoc($allPosts)) {
            $postID = $link->real_escape_string(intval($allRows['post_id']));
            $isBlog = $link->real_escape_string(intval($allRows['blog']));
            $isJob = $link->real_escape_string(intval($allRows['job']));
            $isVid = $link->real_escape_string(intval($allRows['video']));
            $itemID = $link->real_escape_string(intval($allRows['item_id']));

            if($isBlog === '1') {
                $query = "SELECT * FROM blogs WHERE blog_id = '".$itemID."' ORDER BY blog_id DESC";
                $result = $link->query($query);
                while($blogRow = mysqli_fetch_assoc($result)) {
                    $blogID = $link->real_escape_string($blogRow['blog_id']);
                    $blogTitle = $link->real_escape_string(html_entity_decode($blogRow['blog_title']));
                    $blogDate = $blogRow['pub_date'];
                    $blogPhoto = $link->real_escape_string($blogRow['image']);
                    $blogAuthor = $link->real_escape_string($blowRow['author']);
                    $blogContent = $link->real_escape_string($blogRow['content']);  

                    //clean up the text
                    $blogTitle = stripslashes($blogTitle);
                    $blogContent = html_entity_decode(stripslashes(truncate($blogContent, 150)));           

                    echo "<div class='masonryBlock' id='".$postID."'>";
                    echo "<a href='post.php?id=".$blogID."'>";
                    echo "<div class='imgholder'><img src='uploads/blogs/photos/".$blogPhoto."'></div>";
                    echo "<strong>".$blogTitle."</strong>";
                    echo "<p>".$blogContent."</p>";
                    echo "</a>";
                    echo "</div>";

                }
            }

Here is the php from the blocks.php script that the AJAX calls:

//if there is a query in the URL
if(isset($_GET['lastid'])) {

//get the starting ID from the URL
$startID = $link->real_escape_string(intval($_GET['lastid']));
//make the query, querying 25 fields per run
$result = $link->query("SELECT  * FROM all_posts ORDER BY post_id DESC LIMIT '".$startID."', 25");

$html = '';
//put the table rows into variables
while($allRows = mysqli_fetch_assoc($result)) {
    $postID = $link->real_escape_string(intval($allRows['post_id']));
    $isBlog = $link->real_escape_string(intval($allRows['blog']));
    $isJob = $link->real_escape_string(intval($allRows['job']));
    $isVid = $link->real_escape_string(intval($allRows['video']));
    $itemID = $link->real_escape_string(intval($allRows['item_id']));

    //if the entry is a blog
    if($isBlog === '1') {
        $query = "SELECT * FROM blogs WHERE blog_id = '".$itemID."' ORDER BY blog_id DESC";
        $result = $link->query($query);
        while($blogRow = mysqli_fetch_assoc($result)) {
            $blogID = $link->real_escape_string($blogRow['blog_id']);
            $blogTitle = $link->real_escape_string(html_entity_decode($blogRow['blog_title']));
            $blogDate = $blogRow['pub_date'];
            $blogPhoto = $link->real_escape_string($blogRow['image']);
            $blogAuthor = $link->real_escape_string($blowRow['author']);
            $blogContent = $link->real_escape_string($blogRow['content']);  

            $blogTitle = stripslashes($blogTitle);
            $blogContent = html_entity_decode(stripslashes(truncate($blogContent, 150)));

            $html .="<div class='masonryBlock' id='".$postID."'>
                    <a href='post.php?id=".$blogID."'>
                    <div class='imgholder'><img src='uploads/blogs/photos/".$blogPhoto."'></div>
                    <strong>".$blogTitle."</strong>
                    <p>".$blogContent."</p>
                    </a></div>";

        }
    }
    echo $html;
}

I have tried using the jquery infinite-scroll plugin, but it seemed much more difficult to do it that way. I don't know what the issue is here. I have added alerts and did testing and the javascript script is fully processing, so it must be with blocks.php right?

EDIT: I have made a temporary fix to this issue by changing the sql query to SELECT * FROM all_posts WHERE post_id < '".$startID."' ORDER BY post_id DESC LIMIT 15

The blocks are now loading via ajax, however they are only loading one block at a time. The ajax is sending a request for every single block and they are fading in one after another, is it possible to make them all fade in at once with jquery masonry?

Nicholas Yost Nicholas Yost · Accepted Answer · 2013-01-31T21:04:30

I seen your code in another answer, and I would recommend using the LIMIT functionality in MySql instead of offsetting the values. Example:

SELECT * FROM all_posts ORDER BY post_id DESC LIMIT '".(((int)$page)*5)."',5

This will just take a page number in the AJAX request and get the offset automatically. It's one consistent query, and works independent of the last results on the page. Send something like page=1 or page=2 in your jQuery code. This can be done a couple different ways.

First, count the number of elements constructed on the page and divide by the number on the page. This will yield a page number.

Second, you can use jQuery and bind the current page number to the body:

$(body).data('page', 1)

Increment it by one each page load.

Doing this is really the better way to go, because it uses one query for all of the operations, and doesn't require a whole lot of information about the data already on the page.

Only thing to note is that this logic requires the first page request to be 0, not 1. This is because 1*5 will evaluate to 5, skipping the first 5 rows. If its 0, it will evaluate to 0*5 and skip the first 0 rows (since 0*5 is 0).

Let me know any questions you have!

Infinite Scroll with MySQL Data

2 Answers