I have a MySQL database that im saving post id, post and views. I want to add a link to next and previous post based on views.
-------------------------------
| Id | Post | views |
-------------------------------
| 1 | Title 01 | 10 |
| 2 | Title 02 | 20 |
| 3 | Title 03 | 5 |
| 4 | Title 04 | 0 |
| 5 | Title 05 | 0 |
| 6 | Title 06 | 0 |
| 7 | Title 06 | 6 |
-------------------------------
So I try following queries. $post_id is the current post id.
// Previous
SELECT * FROM posts WHERE id>'$post_id' ORDER BY views ASC LIMIT 1
// Next
SELECT * FROM posts WHERE id<'$post_id' ORDER BY views DESC LIMIT 1
Above queries are returning wrong results.
// Previous
SELECT * FROM posts WHERE views>'$views' ORDER BY views ASC LIMIT 1
// Next
SELECT * FROM posts WHERE views<'$views' ORDER BY views DESC LIMIT 1
Those are returning results until the zero occurs (from example data I added above) so I change where clause to views>='$views' (previous) and views<='$views'(next) this it returns wrong results.
I know this seems like a simple question but I search everywhere couldn’t find anything normally it’s easy to get next and previous posts according to post id but views it doesn’t work at all.
Your time and answers are highly appreciated.
Edit: views column is in INT
Bit more details.
My parent page winch call views have below query
SELECT * FROM posts WHERE views DESC LIMIT 10
So this will out put post id's 2,1,7,3,4,5,6 when user click any of these posts it will go the post that display full post with next and previous navigation so i want the previous and next navigation to be the same order as parent page which is 2,1,7,3,4,5,6
To keep navigation less random, you can add sort by other field. For example, by Id. You need exact algorithm in order to get same results every time. This is just example of such algorithm:
In this case we can always select single, not random, next (previous) post.
-- Previous post
SELECT *
FROM posts
WHERE (views > '$views') OR (views = '$views' AND Id < '$post_id')
ORDER BY views ASC, Id DESC
LIMIT 1;
-- Next post
SELECT *
FROM posts
WHERE (views < '$views') OR (views = '$views' AND Id > '$post_id')
ORDER BY views DESC, Id ASC
LIMIT 1;
Also, you need to modify query on your parent page and add sort here too:
SELECT * FROM posts ORDER BY views DESC, Id ASC LIMIT 10
Note:
Site can be used with multiple users at once. And such navigation will not work in general case. For example, user1 opened first post, then go to next (second) post. At this moment user2 opened first post too and it's views count was changed. If user1 goes to previous page (first) now, link to 'next' on it may not point to second page.
To get previous value.Here find all which have views less than current and then sort in desc order and last one is then the previous one.
SELECT * FROM posts WHERE views<'$views' ORDER BY views DESC LIMIT 1
To get next value .Here find all which have views greater than current and then sort in asc order and first one is then the next one.
SELECT * FROM posts WHERE views>'$views' ORDER BY views ASC LIMIT 1
Here only edge case will be when views are equal,in that case you have to get all views which are equal and then order by id also and then if views are equal add one more condition id > current id and vice versa for previous one.