MySQL ORDER BY + LIMIT help

[ScopeXL]

Hello,

 

I am making a chat program and to do so I am using mysql to query the results. To make it display in order I am ORDER BY `id` ASC, though I only want to return the last 200 lines. Since these queries can reach in the thousands of results, I was originally thinking to use LIMIT 200, though because I order from `id` ASC, only the first 200 messages show.

 

I am not sure how to do this since my chat program lists messages earliest to latest, any ideas?

 

My query (with no limit)

SELECT * FROM `web_messages` WHERE `user` = '".$userID."' ORDER BY `id` ASC;

[Pikachu2000]

Instead of echoing them in the while loop (or at least that's what I assume your doing), you could still use the ORDER BY `id` DESC LIMIT 200, but store them in an array, array_reverse() the array and then echo them. There might be an easier or more efficient way, but that's what comes to mind immediately.

[ScopeXL]

Thank you Pikachu2000, your solution works great. I also found another way to do it via mysql_query if others have this problem and are in need of this type of solution.

 

SELECT * FROM (SELECT * FROM `table` WHERE . . . ORDER BY `id` DESC LIMIT 200) AS `table` ORDER BY table.id ASC;

 

Thanks again.