Mysql: i need to get the offset of a item in a query.
I have a image gallery: this show 6 image per stack, so when i request image 22 it shows images from 18 to 24. It should first get the offset of the image 22, then get the images from 18 to 24.
Another example: i request the image number 62(and offset 62), it will select images with offset from 60 to 66.
Is possible with a single query?
The main important thing is to get the offset value of the item that has its id equal to a number.
Thanks ;)
EDIT:
select * from images order_by updated_at offset(here i need to get the offset of the image id in this query, and the make some calculation... this is what i need, if is possible.. :d)
EDIT2:
Now I understand that I need 2 queries:
1º: get the offset of the image within the query with my custom order
2º: get the images using the offset from the first query... this I can make it alone, the first one is the problem.. :s
解决方案
If your images have sequential IDs, you may want to do the following:
SELECT *
FROM images
WHERE id >= ((? DIV 6) * 6) AND
id < (((? DIV 6) + 1) * 6)
ORDER BY id;
Replace the ? parameter in the above query with the ID of the image requested.
UPDATE: It seems that your images are not ordered by a sequential ID, but by a timestamp. Unfortunately it looks like MySQL does not support variable expressions in the LIMIT clause (Source). One option would be to use a prepared statement:
PREPARE stmt FROM
" SELECT *
FROM images
ORDER BY updated_at
LIMIT ?, 6";
SET @lower_limit := ((22 DIV 6) * 6);
EXECUTE stmt USING @lower_limit;
Another option could be:
SET @row = 0;
SELECT *
FROM images
WHERE (@row := @row + 1) BETWEEN ((3 DIV 6) * 6) + 1 and (((3 DIV 6) + 1) * 6)
ORDER BY updated_at;