获取mysql查询中的下一行

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?

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

Hmm, you could do something like

SELECT id,
       ...
       FROM images
       WHERE name LIKE '...'
             ...
             AND (:id IS NULL
                   OR id > :id)
       ORDER BY id
       LIMIT 1;

:id is the parameter for the ID. On your first page set it to NULL. Because of the OR branch in the WHERE the records won't be filtered by the ID. On any subsequent page set it to the ID of the previous page. The records will get filtered for larger IDs. Because of the ORDER BY id and the LIMIT 1 you'll always get the record with the least possible ID (if existent of course).

You can also omit the :id IS NULL part and do just

SELECT id,
       ...
       FROM images
       WHERE name LIKE '...'
             ...
       ORDER BY id
       LIMIT 1;

on the first page and

SELECT id,
       ...
       FROM images
       WHERE name LIKE '...'
             ...
             AND id > :id
       ORDER BY id
       LIMIT 1;

on any subsequent one, setting :id to the ID of the current page.

For the other direction you can use

SELECT id,
       ...
       FROM images
       WHERE name LIKE '...'
             ...
             AND id < :id
       ORDER BY id
       LIMIT 1;

and just set id to the ID of the current page.

This is never a good way to navigate between elements by IDs... Because ID are unique, but not ordered as you want.

A better approach is to store your result in an array (or other iterable structure), then you can navigate by elements (row in your case).

A minimalist algorithm :

var results[];
results = query("Your SQL query here");
var currentIndex = 0;

var nextRow = results[currentIndex + 1];
var previousRow = results[currentIndex - 1];