[RESOLVED] Ordering Problems

Waffles

This is my first post on these forums, so allow me to introduce myself. I'm new to PHP and MYSQL, having started using it only less than a year ago as a hobby. I'm not quite sure whether I should post this in the Newbies section or here, but it is a database specific problem.

Anyway, my problem is that I want to order MYSQL rows in descending order, seems simple enough, but the problem is, I want to order the latest 20 rows in descending order, and not all of them. The problem I have is when I go to do this, it displays the first 20 rows in descending order, and then just stays that way, never updating.

What I want to happen is new rows to appear at the bottom like so:

If I had a limit of 5 it would look like this:

Row 1
Row 2
Row 3
Row 4
Row 5

And stay that way, even if there were 6 rows in the table, when I want it to look like this:

Row 2
Row 3
Row 4
Row 5
Row 6

The query I'm using right now is

mysql_query("SELECT * FROM table_name ORDER BY id DESC LIMIT 20");

alimadzi

Assuming that 'id' is the primary key of your table, the query you provided should work. Do you understand that descending order means the most recent ones (highest id's) will appear first? So if you're wanting the most recent ones to appear at the end of the list, that is ascending order. Just want to make sure.

Waffles

alimadzi wrote:
Assuming that 'id' is the primary key of your table, the query you provided should work. Do you understand that descending order means the most recent ones (highest id's) will appear first? So if you're wanting the most recent ones to appear at the end of the list, that is ascending order. Just want to make sure.

Yes, id is the primary key of my table, and I understand the difference between descending and ascending. The thing is, I want the most recent entry to the table to appear at the bottom of the list, instead of at the top, which is what ascending is doing.

alimadzi

OK, so you have a few options here. Two I can think of off the top of my head are as follows...

Run the query you have. Loop through the result set and load the entries into an array. Reverse the array and output it.
Run a query to find out how many rows are in the table. Then remove the DESC from the query you've been using and add a LIMIT clause to just get the last 20 rows in ascending order.

bradgrafelman

SELECT * FROM
    (
    SELECT col1, col2, id FROM table_name ORDER BY id DESC LIMIT 20
    )
ORDER BY id ASC

alimadzi

Good solution, Brad. A subquery is better than either of my ideas as long as the original poster has MySQL 4.1+ installed.

Waffles

Thanks for all your help guys, I got it working now.

Final query was:

SELECT sid, sauthor, smessage
FROM (
SELECT author AS sauthor, message AS smessage, id AS sid
FROM chatmessages
ORDER BY id DESC
LIMIT 20
) AS sub_chatmsgs
ORDER BY sid ASC
LIMIT 0 , 30

bradgrafelman

Ah, forgot about the alias for the subquery.

Also - there's no need for the LIMIT clause on the outer-most query.