The way I am doing my pagination now is as follow:
===============
First I do the normal query, and get all the result back.
After that I count the results, lets say 300. I do this with num_results
Now I check what page the user wants, and the limit, lets say 30,10.
Last I do the new query, with the limit selected.
===============
Using this method I do almost the same query twice, is there no other way to do this, in just one go.
Yes, you can use the SQL_CALC_FOUND_ROWS for exactly this purpose.
select SQL_CALC_FOUND_ROWS something from table limit 30,10
This way you can still get the amount of rows normally retrieved when NOT using the limit clause.
SQL_CALC_FOUND_ROWS means that the row count is returned after the user has selected which page to view.
$num_pages * $records_per_page.I solved it using something like the following with one query - you get limit x,y+1 so a extra item in limit and interrogate it with array_slice - if it is empty there are no more items.
<?php
/*
CREATE TABLE test ( f1 varchar(23), f2 varchar(23) );
INSERT INTO test VALUES( 'one','testfdsfsdf');
INSERT INTO test VALUES( 'two','ssasfsdff');
INSERT INTO test VALUES( 'three','wewefferwr');
INSERT INTO test VALUES( 'four','wer3rtetet');
INSERT INTO test VALUES( 'five','sdfsfsdffsdf');
INSERT INTO test VALUES( 'six','sdasdadasd');
INSERT INTO test VALUES( 'seven','testfdsfsdf');
INSERT INTO test VALUES( 'eight','ssasfsdff');
INSERT INTO test VALUES( 'nine','wewefferwr');
*/
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "eztrades";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$start=2;
$items_per_page = 3;
$n=$items_per_page+1;
$sql = "SELECT * FROM TEST LIMIT ".$start.','.$n;
$result = $conn->query($sql);
print_r( $result);
while( $r = $result->fetch_assoc()) {
print_r( $r);
$last_item = array_slice( $r , $items_per_page +1, 1 );
}
echo 'LAST ITEM::::';
print_r( $last_item );
if(empty($last_item)) {
echo 'NO MORE ITEMS';
}
?>
