mysql select an entry and then the next n entries

Question

I have a webpage from which a user can select an entry from a MySQL DB. After this entry is displayed, I would like to have one button that allows the user to select the next five DB entries and another button that allows the user to select the previous 5 entries.

How can I write the query for these two buttons?

I have a primary key column, _id and a date column date, but there are be gaps in both, i.e., selecting all data from the table results in:

+-----+------------+
| _id | date       |
+-----+------------+ 
|   4 | 2020-11-26 | 
|   5 | 2020-11-28 |
|   6 | 2020-11-29 | 
|   7 | 2020-12-01 | 
|   8 | 2020-12-08 | 
|  10 | 2020-12-22 | 
|  12 | 2020-12-25 |
+-----+------------+

For the first button (the next five entries) I have tried:

select * from Blog where post=1 and _id=5 order by date desc limit 5 offset 5;

which returns 0 records, and

select * from Blog where post=1 and _id>=5 order by date desc limit 5 offset 5;

which also returns 0 records.

I don't have a clue how to write the query for the second button, i.e. the previous five entries.

Any help would be appreciated.

Only change `limit .. offset ..`values. `limit 5 offset 0`wil give the first 5 records, `limit 5 offset 5` will give next 5 records, `limit 5 offset 10` will give next 5 records. — Luuk, Jan 02 '21 at 13:17
If you have a large dataset, you could [remember where you left off](http://mysql.rjweb.org/doc.php/pagination) like is explained here: https://stackoverflow.com/questions/20364349/pagination-using-mysql-limit-offset — Luuk, Jan 02 '21 at 13:55
@Strawberry very small--it's a blog right now with only 11 or so entires, but I plan on blogging weekly, so it will grow over time. — P. James Norris, Jan 02 '21 at 14:56
@Luuk For what I'm trying to do, get the next 5 from the currently selected entry, your answer won't work. — P. James Norris, Jan 02 '21 at 14:59
I would just grab the entire data set and handle the pagination in application code — Strawberry, Jan 02 '21 at 15:01

Luuk · Answer 1 · 2021-01-02T15:32:43.633

A more detailed explanation on using limit and offset (because the comment I gace "won't work" ):

Assume you have a query, with 'lots-of-results', like: SELECT i FROM integers;, and you do want to paginate that into pages of 5 records.

When doing: SELECT i FROM integers LIMIT 5 OFFSET 0; will give the first 5 rows. The first because reading starts from OFFSET 0.

mysql> SELECT i FROM integers ORDER BY i LIMIT 5 OFFSET 0;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+---+
5 rows in set (0.00 sec)

Next 5 records can be obtained by doing SELECT i FROM integers LIMIT 5 OFFSET 5;

Or, in other words, the values after OFFSET should be (pagenummer-1)*5. With pagenumber=1 is the first page.

Example to get page 42 ( (42-1)*5=205 ):

mysql> SELECT i FROM integers ORDER BY i LIMIT 5 OFFSET 205;
+-----+
| i   |
+-----+
| 205 |
| 206 |
| 207 |
| 208 |
| 209 |
+-----+
5 rows in set (0.00 sec)

But, EXPLAIN is showing something of interest:

mysql> explain SELECT i FROM integers ORDER BY i LIMIT 5 OFFSET 205\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: integers
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 210
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

The interserting thing is that 210 rows needs to be read to produce this 5 row result.

Sorry, but as I showed in my data set, my `_id` and `date` are not continuous--there are gaps, unlike your integer example--I don't know if this is why your answer doesn't work for me, but when I do `select _id, date from Blog where _id=5 limit 5 offset 5` I get `Empty set (0.00 sec)` — P. James Norris, Jan 02 '21 at 18:42
You will have to use (or create) a query which will get all Blog entries, something like `select _id, date from Blog`, and add the `limit 5 offset 5` to this query to get the second page of the results. — Luuk, Jan 02 '21 at 18:46

score 0 · Accepted Answer · answered Sep 10 '21 at 10:51

My question had two parts:

How do I get the next 5 entries?
how I get the previous 5 entries?

While I got answers to (1), I had to take parts from various answers to get the result I wanted. What I came up with was:

$query_blog = 'select * from Blog where _id<'.$_POST['_id_last'].' order by date desc limit 5';

where, per one of the answers above, I POSTed the last displayed entry, $_POST['_id_last'], and used that as the starting point for the query.

For (2), which no one addressed, what I came up with was

$query_blog = 'select * from (select * from Blog where _id>'.$_POST['_id_last'].' limit 5) as subquery order by date desc';

where I used a subquery to get the previous 5 entries and wrapped it in a query that reordered the result in descending order.

There may be a more elegant solution for (2), but this is what I came up with and it works for me.

mysql select an entry and then the next n entries

2 Answers2