How to get last 5 rows in a table?

[Anon]

I have this following script that will display to me the last 5 rows and its specific fields in a table. Problem is, it is displaying the first 5 rows. I want to display the last 5 so i get the latest comments in those rows. What do i have to enter in my SQL query to be able to pull that up?

Thanks guys for any help.

Vinnie

//Start display of shortened comments
MYSQL_CONNECT( "localhost", "root", "blah");
mysql_select_db("mysql");
$comments=mysql_query("SELECT owner, comment FROM parts_ratings WHERE parts_id='$id' limit 5");
if(mysql_num_rows($comments)) {
while ($r = mysql_fetch_array($comments)) {
$comment = $r["comment"];
$owner = $r["owner"];
$short_comment=substr($comment,0,30)."...";
echo "<font color=$font_color_body face=$font_face size=$font_size_small>$short_comment &nbsp;&nbsp;
&nbsp;-$owner</font><br>";
} echo "<br><center><font color=$font_color_body2 face=$font_face size=$font_size_small><img src=ima
ges/arrow.gif align=middle> Read more comments!</font></center>";
...
..
..
..
.

[Anon]

If parts_ratings has a PRIMARY KEY 'id' field that is a MySQL auto_increment field or a date/time stamp then you could use either of these fields to order the query in descending order. Then the LIMIT would give you last 5 entries in reverse order.

[Anon]

Yes it has primary key 'id', and it is a mysql auto_increment'ed field. There are no date/time stamps unfortunately. But how can i specify to list only 5 of the greatest id's from that table??

Basically if i can pull a reversed query i can then achieve what I need..but I dont have a clue how to "reverse" the query. I've searched around but no luck in finding a function in mysql to do so...

thanks again,

[Anon]

do this:
SELECT owner, comment FROM parts_ratings WHERE parts_id='$id' ORDER BY DESC limit 5";

this will get the result in reverse order, and the limit 5 will give you the last 5 entries.. 🙂

good luck,
Daarius...

[Anon]

SELECT * from sometable ORDER by ID DESC LIMIT 0, 5

Should work

[Anon]

it should be:

SELECT owner, comment FROM parts_ratings WHERE parts_id='$id' ORDER BY parts_id DESC limit 5";

forgot the parts_id after ORDER BY ....

but still i am not sure if you need the field name again if there is only 1 field to retrieve.. but anyway try both.
1 of them has to work!

bye

[Anon]

Great! It works. The code I actually used was:

$comments=mysql_query("SELECT id, owner, comment FROM parts_ratings WHERE parts_id='$id' ORDER BY id DESC limit 5");
....
..
..
.

Where id = the index id of each row, instaed of "parts_id". parts_id is the id of rows from another table...for cross-referencing sake 😉

Thanks again guys!

Ps> if anyone is looking for code on a parts cataloging php3-based script, let me know, i'll be glad to share...

[Anon]

That would work, although I'd prefer to use a count variable...

$sql = "select * from yourtable order by id desc";
$results = mysql_db_query("database", "$sql");
$row_count = 0;
while ($rows = mysql_fetch_row($results) and $row_count < 5) {
echo "$rows[0]";
echo "$rows[1]";
// etc...

$row_count = $row_count+1;
}