CODEWITHSUNDEEP
phpmysqlpagination
Saif Bechan
Saif Bechan

Reputation: 17121

PHP/MySQL Pagination with one query

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.

Upvotes: 2

Views: 2676

Answers (2)

Ian
Ian

Reputation: 900

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';
}


?>

Upvotes: 0

Dennis Haarbrink
Dennis Haarbrink

Reputation: 3760

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.

Upvotes: 9

Related Questions