CODEWITHSUNDEEP
mysql
NedStarkOfWinterfell
NedStarkOfWinterfell

Reputation: 5153

How to retrieve records in reverse order?

For news-ticker applications, like the one in Facebook, we see that as we scroll further, older news appear. Now surely the news are inserted into the table as they occur, so a normal selection would always retrieve older records earlier, whereas here the reverse occurs. I assume the way it is done is that when the user scrolls down to the end, FB sends an Ajax request with the id of the last news-id currently present in the ticker (couldn't identify for sure in Firebug, FB sends loads of data!), the PHP queries the DB, flips the result set according to the time column, then extracts the say next 5 records following the one with the received id. Now such tables are huge, so flipping them frequently surely takes a heavy toll on the DB. So is there any way to achieve this without flipping?

Upvotes: 0

Views: 1359

Answers (3)

Neel Basu
Neel Basu

Reputation: 12904

specify order by in your sql statement to get the oldest first. and do where id > last_id to get the rows after the last_id

Upvotes: 1

octern
octern

Reputation: 4868

If you don't specify an ORDER BY variable in your query, MySQL is not guaranteed to return the records in order of insertion. See this answer for more detailed information. If you want to be sure you're getting the most recent rows, you need to have an insertion time column and sort on that.

If you're sorting on time desc, then you can use the usual LIMIT clause to request just the first 5 records (i.e., the 5 most recent), or the 5 most recent after a certain ID, etc.

Upvotes: 3

OYRM
OYRM

Reputation: 1415

Yes, keeping in mind that we're speaking about this very abstractly. If you wanted to access an indexed collection in reverse order.

$collection = array(); // Filled through request to server
....
for ($i = sizeof($collection)-1; $i >= 0; $i--) {
    echo $collection[$i]
    .... // execute some action based on access to 
}

Which is to say, there's no reason you can't access an array from its last index.

Upvotes: 1

Related Questions