Select a row and rows around it

[risoknop]

I'm developing an image gallery and on a page I want to show a single image and bellow it a thumbnails of 10 other images from the same gallery. The photo I am viewing should be in the middle of these thumbnails.

For example, there are 20 images in the gallery with ids:

1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20

On the page that displays the image 11, there would be these thumbnails (11 in the middle):

7, 8, 9, 10, 11, 12, 13, 14, 15, 16

On the page that displays the image 14 there would be these thumbnails (again 14 in the middle):

10, 11, 12, 13, 14, 15, 16, 17, 18, 19

And so on. Of course, ids doesn't have to be so periodical, there might be larger setbacks like this:

2, 9, 15, 17, 26, 27, 28, 34 etc

How would SELECT query to achieve this look like?

[guido2004]

SELECT 
    ...
FROM 
  (SELECT 
       ...
   FROM images
   WHERE imageid < $imageid
   ORDER BY imageid DESC
   LIMIT 0, 4
   UNION
   SELECT
       ....
   FROM images
   WHERE imageid >= $imageid
   ORDER BY imageid ASC
   LIMIT 0, 6
  ) as A
ORDER BY imageid

This will give you max 10 images. If there are no 4 images before the current image, or 5 image after it, then the number of images returned will be less.

[jondolar]

Alternatively, you can select 10 images before and 10 images after and then use PHP to determine the actual size of the two arrays returned if you need to display a full 10 additional images.

[risoknop]

SELECT 
    ...
FROM 
  (SELECT 
       ...
   FROM images
   WHERE imageid < $imageid
   ORDER BY imageid DESC
   LIMIT 0, 4
   UNION
   SELECT
       ....
   FROM images
   WHERE imageid >= $imageid
   ORDER BY imageid ASC
   LIMIT 0, 6
  ) as A
ORDER BY imageid

This will give you max 10 images. If there are no 4 images before the current image, or 5 image after it, then the number of images returned will be less.

I get an error related to incorrect usage of UNION and ORDER BY clauses with this query.

[risoknop]

Here's the query:

SELECT
    A.*
FROM
  (SELECT
       *
   FROM media
   WHERE id < 7
   ORDER BY id DESC
   LIMIT 0, 4
   UNION
   SELECT
       *
   FROM media
   WHERE id >= 7
   ORDER BY id ASC
   LIMIT 0, 6
  ) as A
ORDER BY A.id

And here's my error:

#1221 - Incorrect usage of UNION and ORDER BY

[SpacePhoenix]

It might be the ORDER BYs in the union, one is ASC and the other is DESC, they might be conflicting try removing them but leaving in the ORDER BY that you have at the end.

[guido2004]

Try this:

SELECT
    ...
FROM
  (SELECT
       ...
   FROM images
   WHERE imageid < $imageid
   ORDER BY imageid DESC
   LIMIT 0, 4
  ) AS a
UNION
SELECT
    ...
FROM
  (SELECT
       ....
   FROM images
   WHERE imageid >= $imageid
   ORDER BY imageid ASC
   LIMIT 0, 6
  ) as B
ORDER BY imageid