<?php
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$pdo->query("create table test(i int)");
$pdo->query("insert into test (i) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)");
$pagination = new Pagination($pdo, "select * from test");
$pagination->setLimit(5);
#$pagination->setCountQuery("select count(*) from test");
$pageCount = $pagination->getPageCount();
$data = $pagination->getPageData(1);
$data2 = $pagination->getPageData(2, PDO::FETCH_COLUMN);
$data3 = $pagination->getPageData(3, PDO::FETCH_CLASS, 'stdClass');
var_dump($pageCount, json_encode($data), json_encode($data2), json_encode($data3));
class Pagination
{
protected PDO $pdo;
protected string $sql;
protected array $params;
protected string $countSql;
protected int $limit = 10;
public function __construct(PDO $pdo, string $sql, array $params = []) {
$this->pdo = $pdo;
$this->sql = $sql;
$this->params = $params;
}
public function setCountQuery(string $sql) {
$this->countSql = $sql;
return $this;
}
public function setLimit(int $limit) {
$this->limit = $limit;
return $this;
}
public function getPageCount() {
return (int)ceil($this->getNumRecords() / $this->limit);
}
public function getNumRecords() {
$this->countSql = $this->countSql ?? $this->getAutoCountQuery();
$stmt = $this->pdo->prepare($this->countSql);
$stmt->execute($this->params);
return $stmt->fetchColumn();
}
public function getPageData(int $page, $mode = null, ...$fetchAllParams) {
$offset = ($page - 1) * $this->limit;
$limit = (int)$this->limit;
$mode = $mode ?? $this->pdo->getAttribute(PDO::ATTR_DEFAULT_FETCH_MODE);
$sql = "$this->sql LIMIT $offset, $limit";
$stmt = $this->pdo->prepare($sql);
$stmt->execute($this->params);
return $stmt->fetchAll($mode, ...$fetchAllParams);
}
public function getAutoCountQuery() {
$pat = '~^(select)(.*)(\s+from\s+)~i';
return preg_replace($pat, '$1 count(*)$3', $this->sql);
}
}