max max - 5 months ago 20
PHP Question

Getting next and previous MySQL id according to views

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 quarries. $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 query giving me 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


Above giving me results until the zero come (from example data I added above) so I change where clause to
views>='$views'
(previous) and
views<='$views'
(next) this is giving me 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

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.