Sequence number from SQL fetch

Question

MySQL

Good afternoon!

I searched the Internet, but I did not find a suitable solution.
Example task: there is a website with desktop wallpapers. On the page with a list of all wallpaper, something like this is done:
SELECT * FROM images WHERE image_deleted = 0 ORDER BY image_upload_time ASC;

The user can sort and select according to various parameters, for example, can sort by rating, etc., respectively, each time a new set of images is obtained.

Further, the user, when he makes his selection and sorting, can go to the page of some image. The sql query is executed:
SELECT * FROM images WHERE image_id = 100;

Question:you need to somehow tell the user that he is viewing image number 40 out of 300 possible in his sample . Determining the number is not a problem, but for some reason I can’t figure out the serial number ...

There is also a similar question: you need to somehow determine the id of the previous and next images in the current selection , that is, make buttons forward and backward.

The database server is mysql. Thanks in advance for your advice!

Answer 1

Play like this, not a perfect solution but sometimes it saves:

SET <hh user=rank>=0;
SELECT <hh user=rank>:=<hh user=rank>+1 AS rank, id FROM menu;

Answer 2

You draw these pictures to the user, looping through the entire selection. Add the required data to the URL.

Answer 3

May be useful: www.sql-tutorial.ru/ru/book_numbering_by_primary_key_values.html .

Answer 4

If the current image is 100
then the previous one will be:

<code>SELECT * FROM `images` WHERE `image_id` < 100 ORDER BY `image_id` ASC LIMIT 1;</code>

Accordingly, the following

<code>SELECT * FROM `images` WHERE `image_id` > 100 ORDER BY `image_id` DESC LIMIT 1;</code>

And it doesn't matter if there are gaps in the image_id column

Answer 5

Have you tried Union?
SELECT (
SELECT * FROM table WHERE id = 1 ORDER BY… LIMIT 1
UNION
SELECT * FROM table WHERE id > 1 ORDER BY… LIMIT 1
UNION
SELECT * FROM table WHERE id < 1 ORDER BY… LIMIT 1
) ORDER BY…

Answer 6

the problem here is that this is something like this: “WHERE id < 1” cannot be used, since sorting is not done by id, but by other parameters.