Passing parameter in Mysql LIMIT

Question

I am creating a paging system and in mysql I am using this way

I take from record 1 and walk up to the 20

SELECT * FROM sistema LIMIT 1 , 20

I pick up from record 2 and walk up to 21

SELECT * FROM sistema LIMIT 2 , 21

and so on but I want you to have one more LIMIT for example if you have 500 record to do this way

SELECT * FROM system LIMIT 300

more in the same query I want to paginate within this 300 record something like

SELECT * FROM sistema LIMIT 1 , 20 , 300 

Answer 1

The SQL language does not allow it. What it allows is to do UNION ALL:

(SELECT * FROM sistema LIMIT 1, 300)
UNION ALL
(SELECT * FROM sistema LIMIT 20, 300)
ORDER BY alguma_coisa;

The UNION ALL together the results of the two SELECTs and the ORDER BY (optional) sorts based on some criteria to be more consistent.

Optionally you can use only UNION instead of UNION ALL, will depend on what you want to do. UNION merges the results of SELECTs eliminating duplicates, UNION ALL does not eliminate.

Answer 2

Friend, from what I understand you want 300 paginated records of 20 in 20 right?

If so, you can normally return the 300 records with SQL.

SELECT * FROM sistema LIMIT 300;

And then break into parts of 20 in the programming. In PHP you can use the function array_chunck:

$stmt = $pdo->prepare('SELECT * FROM sistema LIMIT 300;');
$stmt->execute();

$resultados = $stmt->fetchAll(PDO::FETCH_ASSOC);
$porPagina = 20;
$manterIndice = true;
$paginados = array_chunk($resultados, $porPagina , $manterIndice);

foreach($paginados as $paginado){
    foreach($paginado as $index => $item){

    }
}

Another option to break into parts of the programming would be to work with some Collections library, such as the Laravel for example.

I hope it helps.