如何获取mysql中最近的行? [英] How can I get the nearest rows in mysql?

问题描述

假设我正在尝试构建某个画廊的缩略图(显示 5 个缩略图).如何让我的mysql显示5个缩略图，中间的缩略图是放大的图像，中间左边的两个是以前的图像，中间右边的另外两个图像是2个之后的图像.

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.

编辑

我知道如何用这样的东西轻松获得 5 个缩略图

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");

但问题是，我没有一致的 id(意思是，会有删除和插入).它基于用户拥有的图像.

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

因此，如果用户点击鞋子 iID 编号为 5 的图片，缩略图应按 1、4、5、6、10 的顺序显示.

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.

但是，如果用户点击缩略图编号 10，缩略图应该显示:5,6,10,11,12.

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

推荐答案

试试这个

$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)

这篇关于如何获取mysql中最近的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！