set LIMIT in result paging

Asked

Viewed 319 times

0

I have a problem I have a database that I use for testing where there is 1000 record and I have a website that pulls this data to be presented. I am using paging in my query this way

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

In php file you have the following variables 

$p = $_GET['p']; 

$qt_por_pg = 20;

I receive via GET the number of the page. And I defined that I want 20 results per page the whole system is working except for a detail where it pulls the 1000 results so I want to set a limit of 100 results and not 1000 as I already use the LIMIT to make the paging as I could set this limit.

php mysql

  • The LIMIT only the number of records indicated by offset which is the value to follow the comma, in your case only 20. Now if $p indicates the page you want to just start on the first record of that page which is $p*$qt_por_pg

  • so that you can understand in the database have 1000 records using LIMIT 1 , 20 it will catch from record 1 to the right 20 and if I pass the value LIMIT 2 , 20 it will take from record 2 till and walk over 20 and so on until you get to thousand sure so I want to set a maximum limit of 100 record and not a thousand something like LIMIT $p , $qt_por_pg LIMIT 100

  • 1

    This does not exist. Each select is a separate query. Soon each query brings only 20 records, and the general rule is that it makes sense as they usually only get one page at a time as the user navigates them. If you want to get several pages soon you have to make a query that get the 100 records and page "manually"

  • Mysql already accepts offset takes full syntax.

1 answer

1


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

SELECT coluna FROM tabela LIMIT 20;

Now, let’s assume that you only want the results from 11 to 20. With the instruction OFFSET is easy, just proceed as follows:

 SELECT coluna FROM tabela LIMIT 10 OFFSET 10;

The OFFSET command indicates the start of the reading, and the LIMIT the maximum number 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 paginate the results of a SQL and show the user only the page, instead of returning all the table records. A table with 1000 records, for example, is much better to show the user from 20 to 120, for example, and decreases the load in the database, improving its performance.

Reference: Hallan Medeiros Blog

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.