I am creating a paging system and in mysql I am using it this way
I get from record 1 and walk to 20
SELECT * FROM sistema LIMIT 1 , 20
I get from record 2 and walk to 21
SELECT * FROM sistema LIMIT 2 , 21
and so on but I want it to have another LIMIT for example if you have 500 record to do this way
SELECT * FROM LIMIT 300 system
More in the same query I want to page within this 300 record something like
SELECT * FROM sistema LIMIT 1 , 20 , 300
The SQL language does not allow. What it allows is to UNION ALL :
(SELECT * FROM sistema LIMIT 1, 300)
UNION ALL
(SELECT * FROM sistema LIMIT 20, 300)
ORDER BY alguma_coisa;
The UNION ALL joins the results of the two SELECT s and the ORDER BY (optional) sorts based on some criteria to be more consistent.
Optionally you can only use UNION instead of UNION ALL , it will depend on what you want to do. UNION merges results of SELECT s deleting duplicates, UNION ALL does not delete.
Friend, so I understand you want 300 paged records of 20 in 20 right?
If this is the case, you can usually return all 300 records with SQL.
SELECT * FROM sistema LIMIT 300;
And then break into parts of 20 in the schedule. 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 parts into programming would be to work with some Collections library, such as the Laravel for example.
I hope it helps.