CODEWITHSUNDEEP
phpmysqlpagination
swamprunner7
swamprunner7

Reputation: 1319

MySQL PHP Pagination

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

Upvotes: 1

Views: 3640

Answers (3)

Martin Bean
Martin Bean

Reputation: 39439

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.

Upvotes: 2

Pekka
Pekka

Reputation: 449813

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:

Upvotes: 1

halfdan
halfdan

Reputation: 34254

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

Upvotes: 0

Related Questions