MySQL: How do I select the next or previous row from database?

Question

I've got a table called "posts", and within that table a column with "post_id" (A long with other columns ofc).

How to I select the next (or previous) row from the table, by "post_id"?

Let's say my current "post_id" is 15. Now I need to select the next row, where certain conditions are met, so I need to put in some where clauses.

I'm bad at explaining this I feel. I'm looking for something like this:

SELECT (next row) FROM posts WHERE post_draft=0 && post_approved=1

Can anybody help me with this?

Make new variable $previous = $post_id-1; And then make query to select where id=$previous. Also, check out this http://stackoverflow.com/questions/1446821/how-to-get-next-previous-record-in-mysql — , Jan 24 '17 at 10:04
Also, make sure you clean up your query before using it. PDO the shit out of it so you always have a solid base protection. — Merv, Jan 24 '17 at 10:13
@NikolayGanovski - `$post_id-1` won't work when there are gaps. — Rick James, Apr 21 '17 at 05:23

arkascha · Accepted Answer · 2017-01-24T10:14:53.887

1

Relational database system have no concept of a "next row" or "previous row". That is because there is no natural order of entries in such a database. So there terms "next" and "previous" are not really defined.

You can however implement a query that delivers what you are looking for:

SELECT * 
  FROM posts 
  WHERE post_id>15 
    AND post_draft=0 
    AND post_approved=1
  ORDER BY post_id ASC
  LIMIT 1;

So if you have the current post_id it is easy to pick the "next" entry according to a given order of a result set. The same is obviously possible for the "previous" entry.

edited Jan 24 '17 at 10:14

answered Jan 24 '17 at 10:06

arkascha

41,620
7
58
90

Awesome, this works like a charm. To select the previous row I just changed post_id greater than, to lesser than, and ORDER BY to DESC, if anyone else stumbles upon this post one day :) Thanks – user2004478 Jan 24 '17 at 11:45

score 0 · Answer 2 · answered Jan 24 '17 at 10:06

I would use something like this.

$next_post_query = "SELECT * FROM `posts` WHERE `post_draft`=0 AND `post_approved`=1 AND `post_id` > {$current_post_id} order by `post_id` asc limit 1";
$prev_post_query = "SELECT * FROM `posts` WHERE `post_draft`=0 AND `post_approved`=1 AND `post_id` < {$current_post_id} order by `post_id` desc limit 1";

score -1 · Answer 3 · answered Jan 24 '17 at 10:57

-1

Assuming the post_id column is auto-increment, you can try

select * from posts where post_id in (select post_id+1 from posts where post_draft=0 and post_approved=1)

answered Jan 24 '17 at 10:57

Krishnakumar

725
1
6
11

`$post_id+1` won't work when there are gaps. Nor will that subquery work if the next id is a draft or not approved. – Rick James Apr 21 '17 at 05:25
@RickJames Hello!!! I assumed post-id is auto-increment so there's unlikely to be any gaps... – Krishnakumar Apr 21 '17 at 07:02
@RickJames : Secondly my assumption of the question: he wants to select the 'next' post-id after the 'post-id' that satisfies the where condition... – Krishnakumar Apr 21 '17 at 07:03
@RickJames So... if post_id=1 where draft=0 and post_approved=1... then we want to display the row for post id=2.. similarly if post_id=5 where draft=0 and post_approved=1... then we want to display the row for post id=6 – Krishnakumar Apr 21 '17 at 07:04
@RickJames I think my answer satisfies the requirement... but maybe I understood the question wrong... – Krishnakumar Apr 21 '17 at 07:05
Wow. Upon second look... That's a correlated IN-SELECT! That is one of the more contorted constructs I have seen in a long time -- It checks each post_id for the next one having the correct filtering. So, perhaps my criticism of gaps is not right. Still, there is no mention of `15` or `limit 1`. Once they are added, I don't think the query can survive in its current format. – Rick James Apr 21 '17 at 16:12
(We both knew a Logan?) – Rick James Apr 21 '17 at 16:14
@RickJames :: I don't see why do we need a 15 here... 15's just a sample given by the questioner. He needed a generic result. – Krishnakumar Apr 24 '17 at 08:38
I'm not the only Rick James; you are not the only Krishna Kumar. It's a big world. We both worked for a Logan. – Rick James Apr 24 '17 at 14:20
The Question asks how to find the "next" one, and uses "15" as a sample. I would expect a usable answer to continue with the "15". Otherwise, how dos your suggested code say "next after ___"? – Rick James Apr 24 '17 at 14:25
The 15 is just an example. You can see clearly what he wants with the sample 'query' he has given. He wants to select 'next' row of whatever 'post_IDs' his 'where' condition results in. – Krishnakumar Apr 24 '17 at 14:27
@RickJames Lol.. you know what, I didn't read the question these many times when I answered it... But after your comments I keep reading it again and again... :) – Krishnakumar Apr 24 '17 at 14:29

MySQL: How do I select the next or previous row from database?

3 Answers3