Correct SQL query for pagination?

Question

PHP

Hello.
I’m doing pagination, everything went well until I ran into an SQL query for output ...
I would not want to do 2 sql queries, 1 for pagination itself (find out the total number of elements), and the other for limiting records, and the question arose. how to build it correctly?
So far the request looks like this:
SELECT * FROM table WHERE name LIKE 'banny%';
But how to limit the output, through php itself?
or still make 1 more request something like this:

SELECT * FROM table WHERE name LIKE 'banny%'; LIMIT 40,60

40 - for example, there are 20 records on a page, and this 3rd page
is 60 - respectively, too. because start at record 40 and show 20
I think it would be more logical to somehow beat this in PHP itself without resorting to an extra SQL query
All right?

Answer 1

> I think it would be more logical to somehow beat this in PHP itself without resorting to an extra SQL query
Better vice versa :)
Get the number of rows in the table where name starts with banny as rows_count column.
SELECT COUNT(table.ID) AS rows_count FROM table WHERE name LIKE 'banny%';
Where ID is the name of the Primary Key.
SELECT * FROM table WHERE name LIKE 'banny%' LIMIT 40.60;
Get rows from 40 to 60.
Just think, if you have 1 million rows in a table, will you drag them all from the database into PHP memory? It's not rubber...

Answer 2

For mysql there is such a thing FOUND_ROWS () dev.mysql.com/doc/refman/5.7/en/information-functi...
It is declared for such purposes. In this case, limit optimization does not work, the query will always be executed in full, but usually it is better than performing a separate count.
Another option might work for you: don't find out how many pages are available at all. Request not a limit of 20, but a limit of 21. If 21 lines are returned, display the first 20 and the "show more" button. Less than 21 is only the result, this is the last page.