获取mysql查询中的下一行

Question

Let's say I have a query: SELECT * FROM table In this scenario I could get the id and and -1 or +1 the id to get the next or previous row.

But if I have SELECT * FROM table WHERE name LIKE ..... I would get several rows and they wouldn't be order by id, how would i be able to get the next or previous row?

My objective is creating a galleri that has a next and previous button with a link to the next or previous image.

This galleri can be ordered and sorted in many different ways.

Is it possible to detect which row is the next or previous in this scenario?

Answer 1

You must add the column called "GalleryID" or something like this and get the GalleryID from searched query than select all photos by GalleryID and sort them by ID, Now you have the list of sorted photos, e.g:

$queryResult = $db->query("SELECT GalleryID FROM table WHERE name LIKE '%WHAT I WANT%'")->fetchall();
$GalleryID = $queryResult[0]['GalleryID'];
$photos = $db->query("SELECT ID FROM table WHERE GalleryID = $GalleryID order by ID asc")->fetchall();

Now you can play with INDEX;

$index = 0; // Current photo index
//Get next photo
$index++;
if($index < count($photos))
    $photoUrl = $db->query("select url from table where ID = $photos[$index] limit 1");

// Get the previous photo
$index--;
if($index >= 0)
    $photoUrl = $db->query("select url from table where ID = $photos[$index] limit 1");