php - How to avoid getting duplicate posts from database in an "infinite scrolling" system? - Stack Overflow

admin管理员组

文章数量:1025457

I have an AJAX system that fetch posts from database and then create DIVs in my layout to put them. When te user reachs the end of the page then many other posts will be loaded by the API. The problem is, how can I get the following 10 posts that are just the previously posts of the last that is already loaded? The same thing I want to happen when I load the new posts at the top, how to show every post in the right order without missing any post or duplicating any post?

Sorry for the bad explanation and the bad english.

I have an AJAX system that fetch posts from database and then create DIVs in my layout to put them. When te user reachs the end of the page then many other posts will be loaded by the API. The problem is, how can I get the following 10 posts that are just the previously posts of the last that is already loaded? The same thing I want to happen when I load the new posts at the top, how to show every post in the right order without missing any post or duplicating any post?

Sorry for the bad explanation and the bad english.

• php
• javascript
• html
• mysql

Share Improve this question Follow asked Nov 20, 2012 at 17:39 CainãCainã 95533 gold badges1313 silver badges2020 bronze badges 0

Add a ment  | 

4 Answers 4

Sorted by: Reset to default 5

You need to store the id of the last retrieved message. When you submit the new request, send the last received message id and have php/mysql return the results from there.

---

If your query looks something like this:

SELECT * FROM posts WHERE username='redcurley' LIMIT 10

Then you'd need to modify to be something more like:

SELECT * FROM posts WHERE username='redcurley' AND id > :id LIMIT 10

You could alternatively use the offset method that Scott mentions.

---

Edit: I assumed you were using auto-increment ids because it's so mon. Since you aren't, you need some way to keep track of the last message returned. You can do this through sorting the records in a way that they'll e out in the same order every time and use offsets.

You could probably use an OFFSET in your query, which would work even if you wanted to get results that may have out-of-order IDs. So if you had -

SELECT * FROM posts WHERE username='foo' LIMIT 10

The next query would be

SELECT * FROM posts WHERE username='foo' LIMIT 10 OFFSET 10

and so on like that as you fetch more and more records. Your Javascript would have to keep track of the offset and send it to the server with the AJAX request.

To allow near infinate results you can't use an AUTO_INCREMENT as there is eventually a limit.

What you can do though is when posted add a timestamp to the row and then in first load there is no timestamp given to the AJAX handle code so it gets them all and sends back the timestamp the query was run at then when it recives it it can then use a different query that has WHERE timestamp > $sentTimestamp

E.G

<?php
// AJAX handler code
$sqlQuery = "SELECT * FROM posts"

if(!empty($_REQUEST['lastPull'])){
    $sqlQuery .= " WHERE `timestamp` > ".mysql_real_escape_string($_REQUEST['lastPull']);
}
// do your query and formatting for AJAX response here
?>

When user load page, You can fetch all last 10 post with page load,

for example -

id POST 1 Post1 2 Post2 3 Post3 . . . 10 Post10

So when user scroll page bottom, You will call your ajax code and pass you last id as input and make paggination using LIMIT in your SQL query.

For example - My last post id is 50 and user scroll down page, So query will fire, Select * from tableName where id < 50 LIMIT 10

This is for old post, and mean while if any new post then you can check new post using new query with last id at the time of user refresh page. Select * from tableName where id > 50 LIMIT 10

This is for new post, return both collection in single request to page and by evaluating both you can inserts new div tag to show this.

You can use ajax and JSON bination, Click Here if you want more deail about JSON and AJAX.

I have an AJAX system that fetch posts from database and then create DIVs in my layout to put them. When te user reachs the end of the page then many other posts will be loaded by the API. The problem is, how can I get the following 10 posts that are just the previously posts of the last that is already loaded? The same thing I want to happen when I load the new posts at the top, how to show every post in the right order without missing any post or duplicating any post?

Sorry for the bad explanation and the bad english.

I have an AJAX system that fetch posts from database and then create DIVs in my layout to put them. When te user reachs the end of the page then many other posts will be loaded by the API. The problem is, how can I get the following 10 posts that are just the previously posts of the last that is already loaded? The same thing I want to happen when I load the new posts at the top, how to show every post in the right order without missing any post or duplicating any post?

Sorry for the bad explanation and the bad english.

• php
• javascript
• html
• mysql

Share Improve this question Follow asked Nov 20, 2012 at 17:39 CainãCainã 95533 gold badges1313 silver badges2020 bronze badges 0

Add a ment  | 

4 Answers 4

Sorted by: Reset to default 5

You need to store the id of the last retrieved message. When you submit the new request, send the last received message id and have php/mysql return the results from there.

---

If your query looks something like this:

SELECT * FROM posts WHERE username='redcurley' LIMIT 10

Then you'd need to modify to be something more like:

SELECT * FROM posts WHERE username='redcurley' AND id > :id LIMIT 10

You could alternatively use the offset method that Scott mentions.

---

Edit: I assumed you were using auto-increment ids because it's so mon. Since you aren't, you need some way to keep track of the last message returned. You can do this through sorting the records in a way that they'll e out in the same order every time and use offsets.

You could probably use an OFFSET in your query, which would work even if you wanted to get results that may have out-of-order IDs. So if you had -

SELECT * FROM posts WHERE username='foo' LIMIT 10

The next query would be

SELECT * FROM posts WHERE username='foo' LIMIT 10 OFFSET 10

and so on like that as you fetch more and more records. Your Javascript would have to keep track of the offset and send it to the server with the AJAX request.

To allow near infinate results you can't use an AUTO_INCREMENT as there is eventually a limit.

What you can do though is when posted add a timestamp to the row and then in first load there is no timestamp given to the AJAX handle code so it gets them all and sends back the timestamp the query was run at then when it recives it it can then use a different query that has WHERE timestamp > $sentTimestamp

E.G

<?php
// AJAX handler code
$sqlQuery = "SELECT * FROM posts"

if(!empty($_REQUEST['lastPull'])){
    $sqlQuery .= " WHERE `timestamp` > ".mysql_real_escape_string($_REQUEST['lastPull']);
}
// do your query and formatting for AJAX response here
?>

When user load page, You can fetch all last 10 post with page load,

for example -

id POST 1 Post1 2 Post2 3 Post3 . . . 10 Post10

So when user scroll page bottom, You will call your ajax code and pass you last id as input and make paggination using LIMIT in your SQL query.

For example - My last post id is 50 and user scroll down page, So query will fire, Select * from tableName where id < 50 LIMIT 10

This is for old post, and mean while if any new post then you can check new post using new query with last id at the time of user refresh page. Select * from tableName where id > 50 LIMIT 10

This is for new post, return both collection in single request to page and by evaluating both you can inserts new div tag to show this.

You can use ajax and JSON bination, Click Here if you want more deail about JSON and AJAX.

本文标签： phpHow to avoid getting duplicate posts from database in an quotinfinite scrollingquot systemStack Overflow