I'm working on a build log which has many log entries in a database.
If I am browsing log entry 8, then obviously the next will be 9 and the previous will be 7.... How do you query a database for record 8 plus an offset to either side?
This is for finding the entry identifier for a "next" and "previous" button, so you can step through the build log, entry by entry.
I could drop all of the records into an array, search for the one I want and then pull the next/previous based on the array key, but this is a common enough task, is there an better way to return this in a SQL statement?
Efficient Previous/Next item query from database
Moderator: General Moderators
- flying_circus
- Forum Regular
- Posts: 732
- Joined: Wed Mar 05, 2008 10:23 pm
- Location: Sunriver, OR
Re: Efficient Previous/Next item query from database
Assuming that log entries can be removed, ie. you have 6, 8,11 as them...
(Note even though ASC is default order, when I will have code that will get some in each direction, I specify ASC just to make reading code easier
When you get the data for id=8:
Code: Select all
// For 'Next'
SELECT id FROM table WHERE id>8 ORDER BY id ASC LIMIT 1Code: Select all
// For 'Previous'
SELECT id FROM table WHERE id<8 ORDER BY id DESC LIMIT 1 Code: Select all
//in programming, you would use a variable instead of the actual number 8 here:
SELECT *,
(SELECT id FROM table WHERE id>8 ORDER BY id ASC LIMIT 1) AS NextID,
(SELECT id FROM table WHERE id<8 ORDER BY id DESC LIMIT 1) AS PrevID
FROM table WHERE id=8 LIMIT 1- flying_circus
- Forum Regular
- Posts: 732
- Joined: Wed Mar 05, 2008 10:23 pm
- Location: Sunriver, OR
Re: Efficient Previous/Next item query from database
Thanks for your input. I ended up with something similar, but running them as seperate queries. I havent written a query quite like what you posted before.twinedev wrote:Code: Select all
//in programming, you would use a variable instead of the actual number 8 here: SELECT *, (SELECT id FROM table WHERE id>8 ORDER BY id ASC LIMIT 1) AS NextID, (SELECT id FROM table WHERE id<8 ORDER BY id DESC LIMIT 1) AS PrevID FROM table WHERE id=8 LIMIT 1