[MySQL] Getting 1st element of list

Discussion in 'Scripts, 3rd Party Apps, and Programming' started by Markstar, Jan 26, 2010.

  1. Markstar

    Markstar New Member

    Messages:
    45
    Likes Received:
    0
    Trophy Points:
    0
    Hi,
    I would like to allow visitors of my website to "turn the page" where there are n news items per page. Implementing the "Next " button/link is easy, but I struggle to get the "Previous" button/link to work.

    Here is an example with 3 items per page:
    news item # (nitem FROM news)
    ...
    16 // previous previous page
    15
    14
    13 // first item of previous page (13-11)
    12
    11
    10 // first item of current page (items 10 - 8)
    9
    8
    7 // first item of next page (that's easy)
    6
    ...

    My problem was that nested queries didn't work with LIMITs and I would really like to get this with one SQL-statement and not have to go through a temporary result list in PHP.

    Thank you for your time!
     
  2. xav0989

    xav0989 Community Public Relation Community Support

    Messages:
    4,467
    Likes Received:
    95
    Trophy Points:
    0
    The aproach I currently use includes 2 MySQL queries: one to fetch the total count of entries and the second one to fetch the actual entries using an offset & limit. As for the actual code to generate the navigation, you can do some searches for pagination in php on google. The code I use is pretty big, so it'll take a lot of space to post it here.
     
  3. farscapeone

    farscapeone Community Advocate Community Support

    Messages:
    1,165
    Likes Received:
    27
    Trophy Points:
    48
  4. misson

    misson Community Paragon Community Support

    Messages:
    2,572
    Likes Received:
    72
    Trophy Points:
    48
    What's your current query? Sub-selects can often be rewritten as joins.
     
  5. Markstar

    Markstar New Member

    Messages:
    45
    Likes Received:
    0
    Trophy Points:
    0
    Thanks guys, I have looked into pagination and while there is plenty of stuff out there, it is a little too much for what I'm trying to do. Additionally, most solutions require getting ALL news entries, which is exactly what I wanted to avoid. ;)

    @mission: Here is what I was thinking (but obviously doesn't work):
    Code:
    SELECT nitems FROM news WHERE nitem IN (SELECT nitms FROM news WHERE nitems > 10 ORDER BY nitems ASC LIMIT 3) ORDER BY DESC LIMIT 1;
    As I said, I know this doesn't work because MySQL doesn't support LIMIT in the nested query and also, ORDER BY is done at the very end. :(
     
  6. misson

    misson Community Paragon Community Support

    Messages:
    2,572
    Likes Received:
    72
    Trophy Points:
    48
    I assume "nitem" and "nitms" are misspellings of "nitems", and you're trying to get a specific range of rows à la the head/tail strategy. As it turns out, neither subselects nor joins are needed. MySQL supports an extended LIMIT clause, where you can specify an offset before the row count:
    Code:
    SELECT nitems FROM news
        ORDER BY nitems ASC
        LIMIT :start, :length
    
    When asking about code, always include short, illustrative examples. It's part of being precise and informative.
     
  7. Markstar

    Markstar New Member

    Messages:
    45
    Likes Received:
    0
    Trophy Points:
    0
    Ups, sorry. I was writing this at the bus station, so I was a little bit in a hurry. Yes, but it's nitem. ;)

    Hmm,
    Code:
    SELECT nitem FROM news
        ORDER BY nitem ASC
        LIMIT 10, 3
    
    Returns an empty result. :happysad:

    Here is what I currently have:
    Code:
    SELECT nitem
    FROM news
    WHERE cat =   'f'
    AND nitem >10
    ORDER  BY nitem ASC 
    LIMIT 3
    The output for this is 11, 12, 13 (but in practice could also be 12, 42, 134). Now I need to get the 13, so currently I go through the list in PHP until I reach the last element and save this as the variable that I use for the link to the previous page. :dunno:


    Edit: Ahh, I think I finally understood LIMIT x, y. If LIMIT get 2 values, the first one is the offset (not really the start).

    Since I don't really need to have a full list of all the pages (first, previous, next and last are enough), this will do nicely and I already have it up and running. :biggrin:

    Thank you!


    P.S.: But sadly it seems x10 is having some difficulties right now. Oh well, I hope it'll work again shortly. :cool:
     
    Last edited: Jan 29, 2010

Share This Page