I've searched everywhere else, but I could not find any reference or tutorials that shows you or explain a best way to show top comments (like the one on facebook, or youtube).
I have accomplished fetching records (20 top comments that is order by their votes). But where I always get stuck at is how am I gonna supposed to fetch the next 20 top comments
Below is what my tables looks like:
id | comments | comment_id
1 Hi nj3b21das
2 Cool jh3lkjb32
3 How are you? bn32j1343
4 What's up? 3kl213543
id | user_id | comment_id | votes
1 4326542 nj3b21das 1
2 2356453 jh3lkjb32 -1
3 8764354 bn32j1343 1
4 3213543 3kl213543 1
SELECT `comments`.comments, SUM(`votes_comments`.votes) AS total_votes
LEFT JOIN `votes_comments `
ON `votes_comments`.comment_id = `comments`.comment_id
GROUP BY comment_id
ORDER BY total_votes
There are 2 ways i can think of.
One is, you can store the current shown comment ids in an array and pass those with your ajax call and then filter in SQL statement exclude those comment ids and fetch the rest of the top comments and once you get the response, append those ids to the same array and you can continue with this.
You define a timestamp on page load that indicates the server time (Ex:
var loadTimeStamp = "<?php echo time(); ?>") and then pass this value along with limit as part of your Ajax call.
Then on the server side, you can exclude any comments that were added after this time, hence preserve the comments list. But for this to work, you need to store the time when people comment and looking at the AJAX calls facebook makes for fetching more comments, they seem to be following this type of method as i can see they pass timestamp with the call (I might be wrong, but their AJAX calls pass timestamp, so I'm assuming..)
With this method, you can go one step further and use this time along with a basic ajax long polling technique to notify the user of any new comments since the page was loaded/last loading of new comments, similar to Facebook and Twitter feeds.
Hope you got it.