Making MySQL return the table backwards

Question

Hi! I want to make a simple news system using PHP and MySQL, right now I got a working post and read system but there is only one problem, I want it to show the 10 latest news but instead it shows the 10 oldest news.

My question is: Is there a way to make MySQL return the results from the bottom of a table or do I have to first get the number of posts and then limit it to the very last 10 ones?

Here is the insert (title and text is escaped and time is time(), poster is not done yet):

mysql_query("INSERT INTO news (title, poster, text, time) VALUES ('$newstitle', '1', '$newstext', '$time')") or die(mysql_error());

And to retrive it (addnews echos it):

$myqr = mysql_query('SELECT * FROM news LIMIT 10') or die("Error running news query: ". mysql_error());
while($myres = mysql_fetch_array($myqr))
{
addnews($myres['id'], $myres['title'], "admin", date('l jS F Y - H:i:s', $myres['time']), $myres['text']);
}

So, short: I want to read the database backwards, is it possible?

Answer 1

Simple, just add an "ORDER BY" clause to your SQL, e.g.

SELECT * FROM news ORDER BY time DESC LIMIT 10

Answer 2

Check out the ORDER BY clause. It allows you to sort rows by a column in ascending or descending order. The following query will return 10 news items, sorted by time in descending order.

SELECT * FROM news ORDER BY time DESC LIMIT 10

Answer 3

you need to modify your query to sort by the date it was created. something like

SELECT * FROM news  order by time DESC LIMIT 10

should work for you. I think its worth noting that if you do not specify an Order by clause, the order in which results are returned is not guaranteed. Right now, you happen to be getting them ordered by the time they were inserted ascending, however you cannot safely assume that will always be the case.

Answer 4

Assuming that id is the primary key:

SELECT * FROM new ORDER BY id DESC LIMIT 10

Answer 5

Use an ORDER BY clause.

Answer 6

Could you not simply Order By time Descending before LIMIT 10?

Answer 7

Hello,

use

SELECT * FROM news ORDER BY time DESC LIMIT 10

Answer 8

You could also use

SELECT TOP 10 FROM news ORDER BY time DESC

I believe. Not sure if the 'top' clause is SQL Server only, though.