CODEWITHSUNDEEP
mysqlsql
Eyad Fallatah
Eyad Fallatah

Reputation: 1948

MySQL: Multiple SELECT statements using LIMIT

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?

Upvotes: 0

Views: 45

Answers (1)

elixenide
elixenide

Reputation: 44823

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).

Upvotes: 1

Related Questions