如何获得MySQL中某一行的偏移量?

I'm trying to make an image database which does not keep a consistent record of ID's. For example it might go 1,2,6,7,12, but as you can see that is only 5 rows. Inside the table I have fileid and filename. I created a PHP script to show me the image when I give the fileid. But if I give it the ID 5 which does not exist I get an error. That's fine as I want an error for that, but not for users who will browse through these images using forward and back buttons. The forward and back buttons would need to retrieve the true fileid which comes after the given ID. Hopefully that makes sense.

我正在尝试创建一个图像数据库，它没有保持ID的一致记录。例如它可能是12 6 7 12，但是你可以看到它只有5行。在表中，我有文件id和文件名。我创建了一个PHP脚本，在给出文件id时显示图像。但是如果我给它ID 5不存在，我就会得到一个错误。这很好，因为我想要一个错误，但不是用户谁将浏览这些图像使用前进和后退按钮。forward和back按钮需要检索给定ID之后的真正的fileid，希望这是有意义的。

This is how I imagine the code to look like:

这就是我想象的代码的样子:

SELECT offset( WHERE fileid=4 )

That would give me the offset of the row where fileid is equal to 4. I think this is easy enough to understand. The reasons I need this are for creating the forward and back button. So I planned to add 1 or take 1 from the offset which gives me the new ID, and the new filename. That way when users browse it will skip the dead ID values automatically, but it will give an error when giving a false ID.

这就得到了fileid等于4的行的偏移量。我认为这很容易理解。我需要它的原因是为了创建forward和back按钮。所以我打算从偏移量中添加1或者取1，这个偏移量给了我新的ID和新的文件名。这样，当用户浏览它时，它将自动跳过死ID值，但当给出一个假ID时，它将给出一个错误。

4 个解决方案

#1

Going up:

上升:

SELECT * FROM table WHERE id > 'your_current_id' ORDER BY id LIMIT 1;

Going down:

下降:

SELECT * FROM table WHERE id < 'your_current_id' ORDER BY id DESC LIMIT 1;

ps: it is better to make LIMIT 2, so that you can see that you are at the first or at the last records in the database when only one record is returned.

最好设置LIMIT 2，这样当只返回一条记录时，您就可以看到在数据库中的第一个或最后一个记录。

4 个解决方案

#1

更多相关文章

随机推荐