mysql select previous 5 rows, and next 5 rows.

[shortysbest]

I have an image slideshow and I want to show the next 5 images from the current displayed image, and the previous 5 images before the displayed image as well as the current image.

 

so to sort of demonstrate:

 

-IMG ID-

192

193

195 (skipped because it may have been deleted)

199

200

201 (current displayed image)

202

204

205

206

211

 

this is the code that gets the first 9 images from the database (showing just to show the variables)

another variable that would be added to make it work how I want would be the current image id, so that could just simply look like: id<$photo_id

$get_photos_query = mysql_query("SELECT * FROM photos WHERE user_id='$id' ORDER BY date DESC LIMIT 9");

 

 

[revraz]

The LIMIT would be the current row# -5.  So if you were on record 10, it would be LIMIT 5, 15

[shortysbest]

I've been trying to do it like this though, the problem is making the variables for stop and end since the id of the row isn't always (and almost never will be) in order like 1,2,3,4,5, with no breaks, as i said before they would be like 1,4,7,19,20, 21 etc.

[redixx]

I've been trying to do it like this though, the problem is making the variables for stop and end since the id of the row isn't always (and almost never will be) in order like 1,2,3,4,5, with no breaks, as i said before they would be like 1,4,7,19,20, 21 etc.

 

It doesn't matter what the ID is, the LIMIT function works the same either way.

[shortysbest]

Yes I know, however I need a starting point to add and subtract from in order to get the 5 images before and the 5 images after the current image. Kneed a way to get the true row number, not the unique Id. One way would be to put it in a while loop for every image comparing the IDs but that would be way too much on the server if I were to have say 5 million images in the database... (or eventually billions)

[xyph]

Here ya go. Keep in mind, this isn't exactly a FAST query.

 

SELECT `col1`,`id` FROM `table` WHERE `id` = 3
UNION ALL (
  SELECT `col1`,`id` FROM `table` WHERE `id` < 3 ORDER BY `id` DESC LIMIT 10
) UNION ALL (
  SELECT `col1`,`id` FROM `table` WHERE `id` > 3 LIMIT 10
) ORDER BY `id`

 

Replace 3 with your $id

[shortysbest]

That's looks great, I'll try it when I get home, thanks!