How do I fetch the last 15 rows in a table, then the 15 before that?

I want to return only the last 15 rows in my table, then the 15 before that.

Unfortunately while($rows = mysql_fetch_assoc($result)) where the query is SELECT * FROM table returns the data in all rows.

I thought about doing something like:

In my insert script

SELECT * FROM table then $selection_id = mysql_num_rows($result)-14 before inserting any data, then adding column named selection_id which would contain $selection_id, thus each set of 15 rows would have the same selection_id.

In my select script

SELECT * FROM table then $num_rows = mysql_num_rows($result)/15 then SELECT * FROM table开发者_运维百科 WHERE selection_id='$num_rows' and SELECT * FROM table WHERE selection_id='$num_rows-1'.

I could then perform while(..) on both results as usual.

However, I'm not sure this is the most efficient way (chances are it's not), so if not, I'd really appreciate some suggestions to cut down the amount of code I'll have to use :)!!

Use a LIMIT clause in your query, order by your auto-incrementing primary key in descending order. E.g.

SELECT * FROM `table` ORDER BY `selection_id` DESC LIMIT 0,15

...will get the last 15 rows, and:

SELECT * FROM `table` ORDER BY `selection_id` DESC LIMIT 15,15

...will get the 15 rows before that.

Selecting the last 15 rows:

SELECT *
    FROM `table`
    ORDER BY `id` DESC
    LIMIT 0,15

Selecting the 15 rows before the previous ones:

SELECT *
    FROM `table`
    ORDER BY `id` DESC
    LIMIT 15,15

And you can continue in a while cycle.

You need to check out mysql LIMIT. To get the last 15, you'd need to know the number of total rows.

$offset=$rowcount-15;
$sql="SELECT * FROM mytable LIMIT $offset,15";

This is just for example, you'd want to make sure there are at least 15 rows, I'm not sure how mysql would deal with a negative offset. I'll let you figure out how to count the rows.

Edit:

Oh, haha, you could also just sort it descending, that will save you having to query twice.

SELECT * FROM mytable ORDER BY id DESC LIMIT 15;