All geek questions in one place

MySql paginated results, find the "page" for a specific result

I have an application using the MySql database that displays a broken list of records to which the user can add new ones. I know how to get paginated results using LIMIT, etc., but the question is how to go to a specific page when adding a new record. If the page size is 20, and the user views the first page, and they add a record that is 23 (out of 100), how do we determine which page to show the user.

So, essentially, given the page size and the specific record, how to determine which page will be displayed on the pages. Shared pages do NOT include all records for the table to which they belong, but the criteria for obtaining them are static.

Edit: I should have been more specific. Entries contain a unique identifier field and a row name field. The resulting records are sorted by name in alphabetical order. It is also a Java application.

The only thing I can think of is to select all the necessary records sorted by name, and then some, how to find the position in these results of a particular record. From this position, the page number can be easily calculated, since we know the page size, but I'm not sure if this is the MySql syntax for getting the record position in the results.

A “dumb” solution would be to simply take them all, and then into the application code (java), determine the specific position of the records in all the results. But it seems like they should be in a more efficient way.

+4
source share
1 answer
SELECT COUNT(*) as CNT FROM tbl WHERE name < (SELECT name FROM tbl WHERE id = 42)

After that, you simply divide the CNT value by the number of lines per page and round ( Math.floor() ).

Or you can even do this in sql:

 SELECT FLOOR(COUNT(*) / 20) AS page_number ...
+13
source

Source: https://habr.com/ru/post/1394059/

More articles:


All Articles