I wish to display a picture gallery, as well as on the vista page, you ought to have the ability to take a look at a lot of pictures: the present picture, wrapped using the two previous records and also the two next ones.
The issue of fetching two next/prev is the fact that I can not (unless of course I am mistaken) choose something similar to MAX(id) WHERE idxx.
note: obviously the ids don't follow as they must be caused by multiple WHERE instances.
You will need to forgive the SQL Server style variable names, I do not remember how MySQL does variable naming.
SELECT * FROM photos WHERE photo_id = @current_photo_id UNION ALL SELECT * FROM photos WHERE photo_id > @current_photo_id ORDER BY photo_id ASC LIMIT 2 UNION ALL SELECT * FROM photos WHERE photo_id < @current_photo_id ORDER BY photo_id DESC LIMIT 2;
This question assumes which you may have non-contiguous IDs. It might become problematic over time, though, for those who have lots of photos inside your table since TOP is frequently examined after the whole result set continues to be retrieved in the database. YMMV.
Inside a high load scenario, I'd most likely begin using these queries, however i would also prematerialize them regularly to ensure that each photo were built with a PreviousPhotoOne, PreviousPhotoTwo, etc column. It's a little more maintenance, however it is effective if you have lots of static data and want performance.
in case your IDs are continuous you could do this
where id >= @id-2 and id <= @id+2
Otherwise I believe you'd need to
union 3 queries, one to obtain the record using the given id and 2 others playing about with
order by such as this
select * from table where id = @id union select top 2 * from table where id < @id order by id desc union select top 2 * from table where id > @id order by id
Performance won't be bad while you aren't locating massive teams of data however it will not do well because of utilizing a
If you discover performance begins as being a problem you could include posts to carry the ids from the previous and then products calculating the ids utilizing a trigger or overnight process or something like that. This can mean you simply perform the hard query once instead of every time you require it.
I believe this process should work acceptable for non-continguous ID's and really should become more effecient than utilizing a UNION's. currentID could be set either utilizing a constant in SQL or passing out of your program.
SELECT * FROM photos WHERE ID = currentID OR ID IN ( SELECT ID FROM photos WHERE ID < currentID ORDER BY ID DESC LIMIT 2 ) OR ID IN ( SELECT ID FROM photos WHERE ID > currentID ORDER BY ID ASC LIMIT 2 ) ORDER BY ID ASC
If you're just interested in the earlier and then records by id could not you've just got a where clause that limits WHERE id=xx, xx-1, xx-1, xx+1, xx+2 using multiple WHERE clauses or using Whereby ?