SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select a row and rows around it

    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:

    Code:
    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):

    Code:
    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):

    Code:
    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:

    Code:
    2, 9, 15, 17, 26, 27, 28, 34 etc
    How would SELECT query to achieve this look like?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    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.

  3. #3
    SitePoint Addict
    Join Date
    Sep 2005
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP Shopping Cart Software Easy Ecommerce Shopping Cart Script.
    PHP Super Cart is 100% template driven.

  4. #4
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Code MySQL:
    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.

  5. #5
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's the query:

    Code:
    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:

    Code:
    #1221 - Incorrect usage of UNION and ORDER BY

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Try this:
    Code MySQL:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •