MySQL PHP Pagination

Question

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

Answer 1

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

Answer 2

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:

• How do you implement pagination in PHP?

• Searching for advanced php/mysql pagination script

• more results

Answer 3

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.

Answer 4

It's all great, but total number of records is required.