Page 1 of 1

Efficient Previous/Next item query from database

Posted: Wed Jun 22, 2011 7:01 pm
by flying_circus
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?

Re: Efficient Previous/Next item query from database

Posted: Wed Jun 22, 2011 8:09 pm
by twinedev
Assuming that log entries can be removed, ie. you have 6, 8,11 as them...

Code: Select all

// For 'Next'
SELECT id FROM table WHERE id>8 ORDER BY id ASC LIMIT 1
(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

Code: Select all

// For 'Previous'
SELECT id FROM table WHERE id<8 ORDER BY id DESC LIMIT 1  
When you get the data for id=8:

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

Re: Efficient Previous/Next item query from database

Posted: Thu Jun 23, 2011 12:47 am
by flying_circus
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
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.