Select Last 10 Results In Asc Order

[chibineku]

I want to select the most recent 10 results from a table, but to fetch them in reverse order. I am playing around with a chat app and so far it fetches the most recent 10 items with this line:$new = "SELECT * FROM `buffer` ORDER BY time_added DESC LIMIT 10";This returns the correct results, but when I echo them they are in the wrong order - most recent is at the top. I want to have the most recent message display at the bottom, as is the norm for a chat room/IM window. I am aware of array_reverse, but can't get it to work. I store the mysqli array in a variable, reverse it and try to while through the array and my ajax call times out. I would like to create a single query that selects in ascending order but uses as the start limit the total number of rows -10. I tried LIMIT (COUNT(*)-10), 10 and a few other ways of putting it but got nowhere.

[justsomeguy]

Try this:SELECT * FROM (SELECT * FROM `buffer` ORDER BY time_added DESC LIMIT 10) ORDER BY time_added ASC

[chibineku]

Apparently, 'Every derived table must have its own alias'. I guess I have to say something like FROM `buffer` AS `temp` in the sub-query?

[justsomeguy]

SELECT * FROM (SELECT * FROM `buffer` ORDER BY time_added DESC LIMIT 10) AS tmp_table ORDER BY time_added ASCSort of a useless requirement because it doesn't return the table name or anything.

[chibineku]

Excellent, thank you very much. I have thus far stuck to one-dimensional queries. Makes me wonder if there are other instances in which I could be more economical and make one instead of two queries.

[chibineku]

Another refinement I would like to make would be to only display messages posted after the user opened the chat window. So, on the chat room page, I need to set a variable to hold the logged_in time. I've done that by echoing a PHP created timestamp in the same format as that stored in the buffer table. Now, I need to add the relevant WHERE to the query. The query as it stands is:$new = "SELECT * FROM (SELECT * FROM `buffer` ORDER BY time_added DESC LIMIT 10) AS temp_table ORDER BY time_added ASC";I figured the WHERE clause would go here:$new = "SELECT * FROM (SELECT * FROM `buffer` WHERE time_added > '".$_POST["logged_in"]."'ORDER BY time_added DESC LIMIT 10) AS temp_table ORDER BY time_added ASC";But that doesn't work - I get messages older than my logged_in time (passed, cunningly, in $_POST["logged_in"]). Entries on a postcard.

[justsomeguy]

Make sure you have a space before ORDER. What data type is the time_added column?

[chibineku]

Okay, the space was what was needed. Thanks!!

[chibineku]

Anyone want to try my new chat room?! *excited child with new toy eyes*I mean, it sucks, but it works and it really only took a couple of hours, which is fast for me.