根据视图获取下一个和上一个MySQL id

Question

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

Answer 1

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:

1. Sort posts by views count in descending order.
2. If two posts has equal views, than sort these posts by Id in ascending order.

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.