Jump to content

Select Last 10 Results In Asc Order


chibineku
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Edited by chibineku
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...