set LIMIT on pagination of results

Question

set LIMIT on pagination of results

Navigation

#1 by (1 votes)

0

I have a problem with a database that I use for testing where I have 1000 records and I have a website that pulls this data to be displayed. I'm using paging in my query this way

$read->FullRead("SELECT * FROM teste LIMIT $p , $qt_por_pg");

In the php file you have the following variables

$p = $_GET['p']; 

$qt_por_pg = 20;

I get via GET the page number. And I defined that I want 20 results per page the system is all working except for one detail where it pulls the 1000 results so that I want to set a limit of 100 results and not 1000 as I already use the LIMIT to make the pagination how could I define that limit .

php mysql

asked by anonymous 21.01.2018 / 15:35

1 answer

Capturing the last 5 characters of a URI Problems with 2 different versions of the v7 library

score 1 · Accepted Answer

The LIMIT clause is used to limit the number of SQL results. So if your SQL returns 1000 rows, but you want only the first 20, you should execute a statement like this:

SELECT coluna FROM tabela LIMIT 20;

Now, let's assume you want only the results from 11 to 20. With the OFFSET statement it's easy, just proceed as follows:

 SELECT coluna FROM tabela LIMIT 10 OFFSET 10;

The OFFSET command indicates the beginning of the reading, and the LIMIT the maximum of records to be read. For records from 61 to 75, for example:

 SELECT coluna FROM tabela LIMIT 15 OFFSET 60;

With this feature, it is easy to page the results of a SQL and show the user only the page, instead of returning all the records in the table. A table with 1000 records, for example, is much better to show the user 20 out of 120, for example, and lowers the load on the database, improving its performance.

Reference: Hallan Medeiros Blog