Use LIMIT to paginate results in MySQL query

Question

I want to fetch my results a 'page' at a time; I want the page number to be a parameter (in a JDBC prepared statement). Consider the following snippet

SELECT * FROM thread t ORDER BY t.id LIMIT ((? - 1) * 20), 20

So ideally, this would result, for page 1, to LIMIT 0, 20.

When I test

SELECT * FROM thread t ORDER BY t.id LIMIT ((1 - 1) * 20), 20

I am told I have a syntax error. I don't see what it could be, though - it's just some simple math. All it tells me is

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '((1 - 1) * 20), 20' at line 1

What am I doing wrong with my LIMIT clause, and how can I fix it?

Answer 1

This cannot be done.

See solution here: MySQL Math and COUNT(*) in LIMIT

I would recommend using javascript or something to handle the first parameter (i.e. offset) such as: limit 0,20 on first page and limit 21,20 on second...

For example if your first page has a get variable in the url www.example.com?page=1

offset = (page - 1)*20 ;
row_count = 20;
select * from table limit (offset, row_count);

Answer 2

Define Offset for the query using the following syntax

SELECT column FROM table 
LIMIT {someLimit} OFFSET {someOffset};

For example, to get page #1 (records 1-10), set the offset to 0 and the limit to 10;

SELECT column FROM table 
LIMIT 10 OFFSET 0;

To get page #2 (records 11-20), set the offset to 10 where the limit to 10

SELECT column FROM table 
LIMIT 10 OFFSET 10;