Next/Previous rows

[The Little Guy]

How can I get the next four and previous four rows?

 

Here is what I have and it isn't working at at all.

$q1 = Mysql::query("SELECT id, file, dir FROM photos WHERE id > $id AND album = '$al' ORDER BY id ASC LIMIT 4");
$q2 = Mysql::query("SELECT id, file, dir FROM photos WHERE id < $id AND album = '$al' ORDER BY id DESC LIMIT 4");

[.josh]

LIMIT has an optional offset you can specify.  Keep track of the current offset with a variable, changing the offset as you move from next to previous in your code (however you do that, clicking on a link or with a loop or whatever).  Offset should be (n-1)*numrows  subtracting 1 from n because as with everything else rows start with 0, and then multiplying by number of rows you are selecting (in this case 4).  I suggest you read a pagination tutorial as part of pagination is doing something like this.

[The Little Guy]

Here is what I have for a query. I have 2 problems.

 

(SELECT id, file, dir, 'prev' as direction FROM photos WHERE id < 19 AND album = '2' ORDER BY id ASC LIMIT 4)
union
(SELECT id, file, dir, 'curr' as direction FROM photos WHERE id = 19)
union
(SELECT id, file, dir, 'next' as direction FROM photos WHERE id > 19 AND album = '2' ORDER BY id DESC LIMIT 19, 4)

 

Problems:

 

1. there should ALWAYS return 4 values with my limit 4. next has no more rows, go to the beginning. If prev has no more rows, get the last

 

2. the thrid query in my union, is in the wrong direction. if I have 19 as the id, I get "23, 22, 21, 20" But if I change DESC to ASC, I get "39, 40, 41, 42". How can I reverse those?

[.josh]

soo...don't you think you should have mentioned all that in the beginning?  I mean how are people supposed to help you when you post a different query than what you are using and a bunch of extra "rules" to follow?

[The Little Guy]

Well... I would have mentioned all that at the beginning, if I had those problems then. I would have also posted that query if I had it then too.