...

View Full Version : Resolved Shoutbox: Get 20 latest inputs SQL



[vengeance]
08-29-2011, 08:35 PM
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?

:thumbsup:

Old Pedant
08-29-2011, 08:45 PM
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.)

Old Pedant
08-29-2011, 08:47 PM
Whoops...missed what you said "but still in ascending order".

Minor trick:


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.)

[vengeance]
08-29-2011, 08:47 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum