MySQL: Multiple SELECT statements using LIMIT

Question

I have the following SELECT statement which allows me to display 10 items per page in a descending order:

SELECT * FROM items ORDER BY date DESC LIMIT XXXX, 10

XXXX is value that is calculated for each page where (XXXX = page number * 10). So, XXXX will be 0 for page 0, 10 for page 1, 20 for page 2, etc.

This seems to work perfectly, except that when a new item is added to the database, items from previous pages start to show in the current page.

Could you please suggest a MySQL or software engineering approach to ignore newly inserted items and preserve the initial order of items?

One way is to handle the pagination at the application level, so there's only one trip to the database. — Strawberry
– Strawberry, Commented Jun 29, 2014 at 21:23

elixenide · Accepted Answer · 2014-06-29 21:23:28Z

1

You just need a parameter for the time when the page was requested. For example, pass a time parameter in the URL (if a web app) or simply store it in a variable (if a desktop/server app), make sure you validate it to prevent SQL injection attacks, then add it to a WHERE clause in your query.

Or, as Strawberry says, handle pagination at the application level somehow. How you do this depends on the type of application you are using (web vs. desktop or server).

answered Jun 29, 2014 at 21:23

elixenide

45k16 gold badges79 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL: Multiple SELECT statements using LIMIT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related