PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
Copy Clear
Copy Format Clear
<?php $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():int { return (int)ceil($this->getNumRecords() / $this->limit); } public function getNumRecords():int { $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):array { $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); } }
Show:  
Copy Clear