The title is just an example. When there is a list of products and they are displayed on the pages, it is natural to choose everything, and then it seems to me very inefficient to deal with the pages in the code. The question is not how to do it there, but how to do it beautifully and optimally. The cursor is probably bad? Same on one line for request to pull - it is bad?

On the other hand, you need to know the total number of lines to determine the number of pages. But when the user goes through the pages, again make a request ... What can I do?

    3 answers 3

    Use LIMIT offset, count

    offset from which entry to pull

    count number of records

    To extend the 1st page to 30 records

     SELECT * FROM table LIMIT 0, 30

    To pull out the 2nd page

     SELECT * FROM table LIMIT 30, 30

    When creating the simplest pagination offset = (page - 1) * per_page

    • And how to get the total number of rows at the request? To show the total number of pages. - Uraty
    • @Uraty use COUNT(*) FROM table - Stanislav Grotto
    • Thank you) And the last question. The same products or articles change relatively rarely, so it turns out that the user will make a bunch of identical requests. Do I need to write under this what kind of cache or DB itself will do this? - Uraty
    • @Uraty DB will not do this if the information is rarely updated, then it can be cached, you can cache the entire page, or a specific request, it all depends on the specific task - Stanislav Grotto

    Why reinvent the wheel? There is a LIMIT, OFFSET.

     SELECT * FROM table WHERE condition LIMIT 40, 60 SELECT * FROM table WHERE condition OFFSET 40 LIMIT 60
    • This is not yet a bicycle, I just couldn’t normally google the answer, I just share my thoughts on the topic) - Uraty
    • Just a wrong question. - ilyaplot

    What is MySQL, what is SQL Server, what is Oracle, what is PostgreSQL support syntax for like or allow it to somehow be implemented using SQL. Your bike is not necessary.

    • Postgresql 9.5: select * from table LIMIT 10.10 replied ERROR: LIMIT #, # syntax is not supported. Hint: Use separate LIMIT and OFFSET clauses. - ilyaplot
    • @ilyaplot because select * from table LIMIT 10 OFFSET 10 - Vladislav Khapin
    • I do not argue. I mean that not all databases have the same syntax. - ilyaplot