Efficient Previous/Next item query from database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Efficient Previous/Next item query from database

Post 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?
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Efficient Previous/Next item query from database

Post 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
User avatar
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

Post 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.
Post Reply