Infinite scrolling with MySQL data
I used the help located in this thread: Using Infinite Scrolling with MySQL Database
And we got close to making it work normally. I have a page that is rendered in blocks using jquery masonry where the blocks are populated with data from a mysql database. When I scroll to the bottom of the page, I successfully get the load.gif image, but right after the image it says "No more messages displayed". that u should say if it's true. I'm only in 5 posts initially out of about 10-15, so the rest of the posts should load when I get to the bottom of the page, but get a message that should appear when there are actually no 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 php on the page blocks are actually included. This page initially hosts 5 items from the database. Javascript grabs the last ID sent and sends it via ajax to the blocks.php script, which then uses the last ID sent 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 calls the AJAX:
//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 tried using jquery infinite scrolling plugin but it was much more difficult to do it. I do not know what's the problem. I added warnings and did testing and the javascript script handles it completely, so it should be with blocks.php to the right?
EDIT: I made a temporary fix for this problem by changing the sql query toSELECT * FROM all_posts WHERE post_id < '".$startID."' ORDER BY post_id DESC LIMIT 15
Now the blocks are loaded via ajax, however they only load one block at a time. Ajax sends a request for every single block and they freeze one by one, is it possible for them all to disappear at once with jquery masonry?
source to share
I saw your code in another answer and I would recommend using the LIMIT function in MySql instead of offset values. Example:
SELECT * FROM all_posts ORDER BY post_id DESC LIMIT '".(((int)$page)*5)."',5
This will just take the page number in the AJAX request and get the offset automatically. This is one sequential request and works regardless of the latest results on the page. Submit something like page = 1 or page = 2 in your jQuery code. This can be done in several different ways.
First, count the number of elements built on the page and divide them by the number on the page. This will give the page number.
Second, you can use jQuery and bind the current page number to the body:
$(body).data('page', 1)
Increase it by one load per page.
This is really the best way because it uses one request for all operations and does not require a lot of information about the data already on the page.
The only thing to note is that this logic requires the first page request to be 0 and 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 lines (since 0 * 5 is 0).
Let me know any questions you may have!
source to share
Have you tried any kind of debugging?
If you are not already using I would recommend getting the firebug plugin.
Is the ajax call returning empty? If so, try repeating sql and make sure it is the correct statement and that all the variables contain the information you expect. Much might not happen given that there is a lot of communication going on between client, server and db.
In response to your comment, you add html to this piece of code:
if(html){
$(".blockContainer").append(html);
$('div#ajaxLoader').hide();
}
I want to do console.log (html) and console.log ($ (". BlockContainer").) Before the if statement.
source to share