论坛 - 由多个页面上的最后一个活动排序的主题

Question

I am currently creating a forum with php and sql. My problem is how to get the topics into the right order assuming that there are a total of 200 and 15 per Page and I'm on page number 10.

I can't select per id because if someone posts into one of these topics the timestamp of that specific topic gets updated -> the newest on top.

For the first page something like that could be okay:

select * from topics order by time desc limit 15 

but for the next page I would need the timestamp of the last topic on the page before.

If I could select a specific table by index relative to the timestamp order without the need for the actual index.

Answer 1

I suggest you check the MySQL documentations for the second argument of the LIMIT clause:

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1)

Syntax:

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

Example:

-- page 2:
SELECT * FROM topics ORDER BY time DESC LIMIT 15, 15;
SELECT * FROM topics ORDER BY time DESC LIMIT 15 OFFSET 15;

-- page 3:
SELECT * FROM topics ORDER BY time DESC LIMIT 30, 15;
SELECT * FROM topics ORDER BY time DESC LIMIT 15 OFFSET 30;

-- page n:
SELECT * FROM topics ORDER BY time DESC LIMIT (n-1)*15, 15;
SELECT * FROM topics ORDER BY time DESC LIMIT 15 OFFSET (n-1)*15;