Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2009
    Posts
    131
    Thanks
    28
    Thanked 7 Times in 7 Posts

    Shoutbox: Get 20 latest inputs SQL

    Hello.

    I'm building a shoutbox in PHP to display the messages in ascending order, like you see in every chat nowadays.

    I'm limiting the site so it only shows the latest 20. However, when doing "ORDER BY `id` ASC LIMIT 20" it retrieves the first 20 (or the "oldest 20") in the database.

    Considering my brain won't work with me right now, I need some help to achieve this. How's the SQL query for getting the LATEST 20 inputs in the database, while still ordering them in ascending order?

    Last edited by [vengeance]; 08-29-2011 at 08:56 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    ORDER BY id DESC LIMIT 20

    (You really do *NOT* need the tickmarks around the name "id"...it's not a reserved word. They don't hurt performance, but they don't help either. And I think they make the code harder to read. I only use them when I *must* and try to avoid the "must" situations.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Whoops...missed what you said "but still in ascending order".

    Minor trick:
    Code:
    SELECT X.* FROM (
        SELECT id, list, of, other, fields
        FROM table 
        WHERE whateverCondition
        ORDER BY id DESC
        LIMIT 20 ) AS X
    ORDER BY X.id ASC
    You need the alias X (well...any name will do, of course). MySQL insists on it. (Other DBs aren't as fussy.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    [vengeance] (08-29-2011)

  • #4
    Regular Coder
    Join Date
    Aug 2009
    Posts
    131
    Thanks
    28
    Thanked 7 Times in 7 Posts
    Quote Originally Posted by Old Pedant View Post
    ORDER BY id DESC LIMIT 20

    (You really do *NOT* need the tickmarks around the name "id"...it's not a reserved word. They don't hurt performance, but they don't help either. And I think they make the code harder to read. I only use them when I *must* and try to avoid the "must" situations.)
    Yeah, but as I mentioned, I'm making my shoutbox in ascending order like you see in other chat programs like MSN, AIM, Skype etc.

    By doing ORDER BY id DESC it puts the order to DESCENDING (oldest on bottom, newest top).

    EDIT: Trying your reply #2.

    EDIT 2: Thanks, worked! Thumbs up.
    Last edited by [vengeance]; 08-29-2011 at 08:56 PM.


  •  

    Posting Permissions

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