使用MySQL数据进行无限滚动 [英] Infinite Scroll with MySQL Data
问题描述
我已按照本主题中的帮助：使用带有MySQL数据库的无限滚动
并且已经接近让这个工作正常。我有一个使用jquery masonry以块显示的页面，其中的块由来自mysql数据库的数据填充。当我滚动到页面的末尾时，我成功地获得了loading.gif图像，但在图像之后立即显示没有更多帖子显示。如果这是真的，它应该说什么。我最初只在10-15个帖子中调用了5个帖子，所以当我到达页面底部时，其余的帖子应该加载但是当我真的没有更多的时候我会收到应该出现的消息这些是我的javascript：
var loading = false ;
$（窗口）.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）&&&&&"""""""&b; b $ b $（' div＃ajaxLoader'）。html（'< img src =images / loading.gifname =HireStarts Loadingtitle =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（） ;
}其他{
$（'div＃ajaxLoader'）。html（'< center>< b>不再显示帖子。< / b>< / center>'）;
}
}
}）;
}
}
}）;
这是块实际上的页面上的php。此页面最初发布数据库中的5个项目。 javascript抓取最后发布的id，并通过ajax将其发送到blocks.php脚本，然后使用最后发布的id从数据库中获取其余项目。
$ 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']）;
//清理文本
$ 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>;
}
}
这是块中的php AJAX调用的.php脚本：
//如果URL中有查询
if（isset（ $ _GET ['lastid']））{
//从URL获取起始ID
$ startID = $ link-> real_escape_string（intval（$ _ GET ['lastid'] ））;
//进行查询，每次运行查询25个字段
$ result = $ link-> query（SELECT * FROM all_posts ORDER BY post_id DESC LIMIT'。$ startID。'，25 ）;
$ html ='';
//将表行放入变量
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（$ 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;
}
我尝试过使用jquery无限滚动插件，但似乎更多难以这样做。我不知道这里的问题是什么。我添加了警报并进行了测试，javascript脚本已完全处理，所以必须使用blocks.php吗？
编辑：我通过将sql查询更改为 SELECT * FROM all_posts WHERE post_id<已对此问题进行临时修复'。$ startID。'ORDER BY post_id DESC LIMIT 15
这些块现在通过ajax加载，但它们只加载一个一次阻止。 ajax正在发送每个块的请求，并且它们一个接一个地消失，是否可以使用jquery砌体使它们全部淡入？
我在另一个答案中看到了你的代码，我建议在MySql中使用LIMIT功能而不是偏移值。示例：
SELECT * FROM all_posts ORDER BY post_id DESC LIMIT'。（（（int）$ page）* 5）。 '，5
这将只取AJAX请求中的页码并自动获取偏移量。这是一个一致的查询，独立于页面上的最后结果。在jQuery代码中发送类似page = 1或page = 2的内容。这可以通过几种不同的方式完成。
首先，计算页面上构造的元素数量并除以页面上的数字。这将产生一个页码。
其次，您可以使用jQuery并将当前页码绑定到正文：
$（body）.data（'page'，1）
每页加载一次增加它。
这样做真的是更好的方法，因为它对所有操作使用一个查询，并且没有不需要关于页面上已有数据的大量信息。
唯一需要注意的是这个逻辑要求第一页请求为0，而不是1这是因为1 * 5将评估为5，跳过前5行。如果为0，它将评估为0 * 5并跳过前0行（因为0 * 5为0）。
如有任何问题，请告诉我们！
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?
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!
这篇关于使用MySQL数据进行无限滚动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
