Displaying last "x" number of records from a MySQL database

[JustForWebmasters]

Hello.

I have set up a MySQL database, and want to have a thing on my main page that displays the last 10 articles from the database. What command do I use for this?

Any help would be greatly appreciated.

[petesmc]

Hi,

Is this what you are looking for:

SELECT * FROM articles LIMIT 10

Then you'd mysql_query it, then

while($result=mysql_fetch_array($query)) {
extract($result);
print $title;
}

[pedro_gb]

Since you want the last 10 articles you'd probably want your query to be:

SELECT * FROM articles LIMIT 10 ORDER BY last_updated DESC

It might depend on how you store the date, though. (Probably the best way is to store the timestamp, because then you can do all sorts of funky things with it.)

Pedro

[petesmc]

Or you can do what I do and order by ID as sometimes the date is unreliable.

So just add ORDER BY ID DESC

[Paul S]

Originally posted by petesmc
Is this what you are looking for:

SELECT * FROM articles LIMIT 10

That would only display the first 10 rows of article table.

Corbbmacc,

Just include the OFFSET parameter in the pete's code,

SELECT * from articles LIMIT N, 10

where N is the total number of articles minus 10.

[Paul S]

Pedro, pete

wow, guys you are really fast (I was writing my post)

[Karl]

The best way is Pedro's way, as it doesn't involve any other queries or a complex query to find out how many items are already in the database.

[freakysid]

Originally posted by pedro_gb
SELECT * FROM articles LIMIT 10 ORDER BY last_updated DESC

I think you will find that the limit clause will need to come after the order by clause. Something like this:

SELECT *
FROM articles
ORDER BY last_update DESC
LIMIT 10

[JustForWebmasters]

Thanks a lot everybody!

[TWTCommish]

Yes, and actually, nothing personal Pete, but the ID is much more unreliable than the date. The date is far more reliable. In fact, the only way it can be considered unreliable is that there's a problem when you have two entries from the same day at times.

This can be fixed by simply storing a timestamp in the database though. This narrows the time of entry down to the very last second. You can assign the timestamp a variable like this:

$time = mktime();

Good luck.