How can I get the nearest rows in mysql?

Question

Lets say I'm trying to build a thumbnail of some gallery (5 thumbnails showing). How can I get my mysql to display 5 thumbnails, the middle thumbnail will be the image blown up, and the two on the left of the middle will be previous images, and the two other images on the right of the middle will be 2 more images after.

EDIT

I know how to easily get 5 thumbnails with something like this

$sqlThumb = mysql_query("SELECT iID,thumbnails,userID FROM images WHERE userID = ".$_SESSION['userID']." ORDER BY iID ASC LIMIT 1,5");

But the thing is, I dont have a consistent id (meaning, there will be deleting and inserting). It is based on the images that the user has.

iID            userID 
-----          --------
1              5
2              4
3              4
4              5
5              5
6              5
10             5
11             5
12             5

So if a user clicks an image which shoes iID number 5, the thumbnail should show like this in order, 1,4,5,6,10.

However, also if a user clicks on thumbnail number 10, the thumbnail should show: 5,6,10,11,12.

have you tried my query .if want to get only from particular user just add **AND userID = ".$_SESSION['userID']."** in where clause. — Gowri, Aug 25 '11 at 04:50

score 1 · Answer 1 · answered Aug 25 '11 at 04:18

1

Try this

$offset = $current_id-3;

 (SELECT * FROM TBL WHERE id < '$current_id'    limit '$offset',2 )
 union( SELECT * FROM TBL WHERE id = '$current_id') 
 union (SELECT * FROM TBL WHERE id > '$current_id' limit 2)

answered Aug 25 '11 at 04:18

Gowri

16,587
26
100
160

by the looks of this query, you're subtracting $current_id by 3? – hellomello Aug 25 '11 at 04:53
@andewwliu: yes, off course you need to find offset to get previous two records.why do you figured any bugs in query. any none relevant results – Gowri Aug 25 '11 at 04:59
I'm trying to work. but am I supposed to do this with `mysql_query(" (SELECT * FROM TBL WHERE id < '$current_id' limit '$offset',2 ) union( SELECT * FROM TBL WHERE id = '$current_id') union (SELECT * FROM TBL WHERE id > '$current_id' limit 2)");?` – hellomello Aug 26 '11 at 06:22
@andrewliu: don't understand what you asking.but i test that query that works before posting my answer.just replace table name correct column names then add any condition in where clauss if you wish.Is that helps – Gowri Aug 26 '11 at 06:28

score 0 · Answer 2 · edited May 23 '17 at 12:05

I don't know if this works, but the idea is to have an attribute called row_number. You will get the image you want by the id and the two last and the two next by the row number.

SELECT iID,thumbnail FROM image_collection,
   (SELECT iID, thumbnail, row_number
   FROM
      (SELECT iID,thumbnail,
      @curRow := @curRow + 1 AS row_number
      FROM image
      WHERE userID = 5
      ORDER BY iID) as image_collection
   WHERE s_image.iID = 10) as image_selected
WHERE image_collection.row_number BETWEEN image_selected.row_number - 2 AND image_selected.row_number + 2

I don't tested it because I don't have MySQL running, but I see how to put the row number here: With MySQL, how can I generate a column containing the record index in a table?

Grzegorz Rożniecki · Answer 3 · 2011-08-25T05:47:41.293

0

Try this one (doesn't include any subtraction):

$id = 5;
$user_id = $_SESSION['userID'];

$sqlThumb = mysql_query("
SELECT iID FROM images WHERE userID = ".$user_id." AND iID = ".$id."
  UNION (SELECT iID FROM images WHERE userID = ".$user_id." AND iID < ".$id." ORDER BY iID DESC LIMIT 2) 
  UNION (SELECT iID FROM images WHERE userID = ".$user_id." AND iID > ".$id." ORDER BY iID ASC LIMIT 2) 
  ORDER BY iID ASC")

edited Aug 25 '11 at 05:47

answered Aug 25 '11 at 05:32

Grzegorz Rożniecki

27,415
11
90
112

:you did n't used offset in ** UNION (SELECT iID FROM images WHERE userID = ".$user_id." AND iID < ".$id." ORDER BY iID ASC LIMIT 2)** which means your query will fail to get nearest previous.for ex :1,2,3,4,5,6,7,8,9. consider 6 as current id .your query will select 1,2 .not 4,5 – Gowri Aug 25 '11 at 05:38
@gowri: Thanks for noting this, should be `ORDER BY iID *DESC*` there. I've edited answer and it works OK. – Grzegorz Rożniecki Aug 25 '11 at 05:44
still you wrong desc will change the order so the result will be 5,4,6,7,8. so you can't get correct order using this query – Gowri Aug 25 '11 at 05:56
@gowri: you aren't right, `ORDER BY iID ASC` in last line applies to results from all three subqueries, so final result will be sorted properly (4,5,6,7,8 in this case). – Grzegorz Rożniecki Aug 25 '11 at 06:01
:yes i missed that part. let see OP's response – Gowri Aug 25 '11 at 06:50

How can I get the nearest rows in mysql?

3 Answers3

Linked