1

Is it possible to create pagination without getting all elements of table? But with pages in GET like /1 /666…

swamprunner7
  • 1,241
  • 6
  • 16
  • 25
  • possible duplicate of [How do you implement pagination in PHP?](http://stackoverflow.com/questions/267892/how-do-you-implement-pagination-in-php) – Pekka Sep 20 '10 at 10:01
  • It's all great, but total number of records is required. – swamprunner7 Sep 20 '10 at 12:41
  • it's all great. So just read the amount off your Database: `SELECT count(*) from table` – Jan. Sep 20 '10 at 12:53
  • i can`t, it`s too slow now, searching optimization ways http://stackoverflow.com/questions/3749831/mysql-records-count-with-condition – swamprunner7 Sep 20 '10 at 13:02

3 Answers3

2

It usually involves issuing two queries: one to get your "slice" of the result set, and one to get the total number of records. From there, you can work out how many pages you have and build pagination accordingly.

A simply example:

<?php
$where = ""; // your WHERE clause would go in here
$batch = 10; // how many results to show at any one time
$page  = (intval($_GET['page']) > 0) ? intval($_GET['page']) : 1;
$start = $page-1/$batch;
$pages = ceil($total/$batch);

$sql = "SELECT COUNT(*) AS total FROM tbl $where";
$res = mysql_query($sql);
$row = mysql_fetch_assoc($res);
$total = $row['total'];

// start pagination
$paging = '<p class="paging">Pages:';
for ($i=1; $i <= $pages; $i++) {
    if ($i==$page) {
        $paging.= sprintf(' <span class="current">%d</a>', $i);
    } else {
        $paging.= sprintf(' <a href="?page=%1$d">%1$d</a>', $i);
    }
}
$paging.= sprintf' (%d total; showing %d to %d)', $total, $start+1, min($total, $start+$batch));

And then to see your pagination links:

...
// loop over result set here

// render pagination links
echo $paging;

I hope this helps.

Martin Bean
  • 33,901
  • 20
  • 114
  • 183
  • Just FYI: it's not necessary to do two queries, you could use the SQL_CALC_FOUND_ROWS select option: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows – Dennis Haarbrink Sep 20 '10 at 10:17
1

Yes, using mySQL's LIMIT clause. Most pagination tutorials make good examples of how to use it.

See these questions for further links and information:

Community
  • 1
  • 1
Pekka
  • 418,526
  • 129
  • 929
  • 1,058
0

You can use LIMIT to paginate over your result set.

SELECT * FROM comments WHERE post_id = 1 LIMIT 5, 10

where LIMIT 5 means 5 comments and 10 is the offset. You can also use the longer syntax:

... LIMIT 5 OFFSET 10
halfdan
  • 31,036
  • 8
  • 73
  • 85