SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard frank1's Avatar
    Join Date
    Oct 2005
    Posts
    1,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Idea for "next item"

    Suppose our database has items with id

    1 2 4 18 25 67 (may be between records were deleted for some reasons)

    now when item 18 is being displayed....there should be next item button which should point to 25....then 25 to 67 and so on....in 67 the next item button should not be there (means may be dimmed)


    so what is best and efficient way of doing it...

    best i can think of for now,may be

    select min(id) from table where id>current_id (for next)
    select max(id) from table where id<current(for previous)

    how resource friendly is it?
    and any other better way than this...

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Do a search for "pagination" here or on google. There's a lot of info and examples. The fact that you show only one item at a time is not important, it's still pagination

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by frank1 View Post
    select min(id) from table where id>current_id (for next)
    select max(id) from table where id<current(for previous)
    That's about as good as you can get. Assuming there's an index on id (which there is if it's the primary key), it'll be very efficient.

  4. #4
    SitePoint Wizard frank1's Avatar
    Join Date
    Oct 2005
    Posts
    1,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Do a search for "pagination" here or on google. There's a lot of info and examples. The fact that you show only one item at a time is not important, it's still pagination
    whenever u see word "next" and "prev" it should not always mean pagination.....or paging...
    read question first before answering ....u wont be getting prize for number of posts

    more over i am talking about more advanced performance here..... how would MAX and MIN functions effect things if there were millions of records....?

    any way thanks "Dan Grossman" ...ya they are primary id....
    up to now it has been ok ...lets see if any body has better idea...

    thanks

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by frank1 View Post
    whenever u see word "next" and "prev" it should not always mean pagination.....or paging...
    read question first before answering ....u wont be getting prize for number of posts
    I did read your question... and it is pagination...
    Of course, if you don't like my answers, and think you need to reply like this, I'll just ignore your posts from now on.

  6. #6
    SitePoint Wizard frank1's Avatar
    Join Date
    Oct 2005
    Posts
    1,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    I did read your question... and it is pagination...
    Of course, if you don't like my answers, and think you need to reply like this, I'll just ignore your posts from now on.
    well ,theoritically it may be/can be called of kind of paging BUT the page paginating and this pagination are differnt in many aspects....a programmer knows that........

    so i was just trying to make sure that the paging u r referring (with blind google search suggestion) and this one is different....
    it is to be understood from the question......


    The normal paging may have start ...how many rows to show ....and so on...
    where is this is pratically different....

    any way i dont have any thing to say about last line other than "never mind"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    That's about as good as you can get.
    even better is to grab those while you're getting the current row -- one query instead of three

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •