Finding a range of records from mysql database

Question

I have a message id which i get when i search a text across all my chats. Once i click on it I go into that chat and now I want the messages that where around that message i.e 10 messages before and after. How do i get this limit done

Answer 1

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement.

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.

SELECT * FROM tbl LIMIT 1,10;  # Retrieve rows 1-10

You would have to know how many rows there are in total messages to implement correctly in your case

The alternative is using the limit with offset Define OFFSET for the query. For example

SELECT column FROM table LIMIT 10 OFFSET 10;

You could work out the total records with a server side language and then divide by the offset to work out how many iterations queries you need to perform

...

An alternative is to get all the records and page it with client side language like JS using something like pagination or a similar technique