假设我正在尝试构建一些画廊的缩略图(显示5个缩略图)。我怎样才能让我的mysql显示5个缩略图，中间的缩略图将是放大的图像，中间左边的两个将是以前的图像，中间右边的另外两个图像将是之后的2个图像。
编辑我知道如何用这样的东西轻松获得5个缩略图
$sqlThumb = mysql_query("SELECT iID,thumbnails,userID FROM images WHERE userID = ".$_SESSION['userID']." ORDER BY iID ASC LIMIT 1,5");
但问题是，我没有一个一致的id(意思是，会有删除和插入)。它基于用户拥有的图像。
iID userID
----- --------
1 5
2 4
3 4
4 5
5 5
6 5
10 5
11 5
12 5
如果用户点击了id为5的图片，缩略图应该按照顺序显示，1,4,5,6,10
但是，如果用户点击缩略图10，缩略图应该显示: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)
我不知道这是否有效，但是这个想法是有一个名为row_number的属性。您将获得您想要的图像的id和最后两个和下两个的行号。
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
我没有测试它，因为我没有MySQL运行，但我看到如何把行号在这里:使用MySQL，我如何生成一个包含表中记录索引的列?
试试这个(不包含任何减法):
$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")