Reversing the display of mysql_fetch_array on html page

Question

I have a discussion type page, where users can enter their replies on different subjects. For this, I have a table name replies with fields replyID, topicID, userID, replybody, time

On the page, where I display the result, I use this php command:

$run = mysql_query("SELECT * FROM replies WHERE topicID = $topicnumber ORDER BY time DESC Limit 5");

while($data= mysql_fetch_array($run)){
// do formatting and display data 
}

as you can see, the page initially displays only 5 replies on a topic (after which, if interested, user clicks to visit the page where all replies are displayed).

the thing is, the above code displays correctly the recent 5 replies, but I want to change the order of its display. It shows the recent most reply on top, and older replies are displayed as we go down, but I want to change this order, showing the recent most reply on bottom.

I think I'm missing a very simple point here, since most of the website have this feature where recent most comments go down, but hey, "no question is small".

jeroen · Accepted Answer · 2012-09-07 20:58:32Z

2

I don't think that's very easy in sql.

I would just load the results in an array and use array_reverse() to reverse the order. Then you can loop through the array and display the values like you do now.

Otherwise I think you need to do 2 queries, one to get the total amount and then one to limit your result set to the last x items.

answered Sep 7, 2012 at 20:58

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc B · Accepted Answer · 2012-09-07 21:08:18Z

0

Use a nested query to reverse the order in SQL:

SELECT *
FROM (
    SELECT * FROM replies WHERE topicID = $topicnumber ORDER BY time DESC Limit 5
) AS a
ORDER BY time ASC

The inner query will return your most recent 5 records, while the outer query will reverse the sort order of those 5.

answered Sep 7, 2012 at 21:08

Marc B

362k44 gold badges433 silver badges508 bronze badges

5 Comments

doing_something_wonderful Over a year ago

Thanks Marc B, You saved the Day! :)

doing_something_wonderful Over a year ago

here is another one, how can I use SQL_CALC_FOUND_ROWS within this query, if I also want to get the total number of replies made on that topic?

Marc B Over a year ago

not sure if you can. you'd have to use it on the inner query, but the outer query will be the last one executed, and it won't have a limit, so the found_rows would be 0. e.g you'd have to run the query twice. once for the actual results, and once to calculate the total rows. at least if you do select count(*) ... it'll be relatively fast since it won't be retrieving row-level data

doing_something_wonderful Over a year ago

yes, I did the same. SQL_CALC_FOUND_ROWS returns only the limit the inner query generated, so returned to SELECT COUNT

Marc B Over a year ago

'course, since you're only fetching 5 rows of data in the inner query, you'd be better off fetching into a php array and displaying that in reverse order. that'd save you the second query to get the count().

Collectives™ on Stack Overflow

Reversing the display of mysql_fetch_array on html page

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related