Paging, count to calculate limit and offset by MYSQL

Question

Paging, count to calculate limit and offset by MYSQL

Navigation

#1 by (2 votes)

2

I have a query with 7 relationships in a relatively large table, and the list of records uses as a base the total of records, so it would be a query to calculate the total and another to display the records between limit and offset .

PHP calculation for offset ...

$perpage = 10
$current = 1;
$records = 100; // select count()...
$average = ceil( $records / $perpage );
$offset  = ($current > $average) ? $perpage * ( $average - 1 ) : $perpage * ( $current - 1 );

I do not really know the MYSQL operations, and only find the total being done via query.

I was wondering if there is any way to make MYSQL calculate $offset without having to run a query just for this?

mysql

asked by anonymous 06.01.2015 / 17:23

1 answer

Problems with SSH: IP being banned after consecutive successful x logins Verify that a device has GPS and Location Service is active

score 2 · Answer 1

I've been looking for a solution and found sql_calc_found_rows and found_rows() .

In the example I used in the question, it basically calculates based on the total number of records found and validates the current page to see if it is larger than the calculated total pages. However this can be bypassed using the result of found_rows () later.

** QUERY 1
select sql_calc_found_rows id from TABELA limit ? offset ?
bindValue( $perpage )
bindValue( ( ( $current - 1 ) * $perpage ) )

** QUERY 2
select found_rows() as total

Output

Array
(
    [0] => Array( [id] => 4 )
    [1] => Array( [id] => 5 )
    [2] => Array( [id] => 6 )
)

Array
(
    [0] => 16
)

Manual

mysql> SEELCT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id> 100 LIMIT 10; 
mysql> SEELCT FOUND_ROWS();

1) The second select returns a number that indicates how many rows the first select would have returned if it had been written without the LIMIT clause.

2) If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many records there are in the complete result set. However this is faster than running the query again without LIMIT , because the result set does not need to be sent to the client.
     3) SQL_CALC_FOUND_ROWS and FOUND_ROWS () can be useful in situations where you want to restrict the number of rows a query returns, but also determine the number of rows in the entire result set without running the query again. An example is a web script that displays a paged display containing links to pages that show other sections of a search result. Using FOUND_ROWS () allows you to determine how many other pages are needed for the rest of the result.