Pagination helper class

Question

The most nauseous problem with pagination is the fact that you have to write two almost identical queries. For the various reasons I am not inclined to use the SQL-only way (such as select count(*) from (original query goes here)). Hence I am trying to modify the original query. Being aware that this method is not fully reliable, I am doing this in a semi-automatic way, offering the automated method for the regular queries with the occasional fallback to setting the count query automatically.

The goal was to make the application code simple and at the same time explicit. The full compatibility with PDO's fetchAll() was a requirement.

The code works perfectly for me so far, but obviously I may have overlooked some edge cases. Also I am not sure about the code distribution between methods.

Here is the code

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() {
        $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);
    }
}

And here is a simple testing snippet, assuming you have a working PDO instance

$pdo->query("create temporary 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));

Answer 1

Someone using your class might not be aware that every time the getPageCount() method is used a SQL query is executed. Ideally it should only be executed once. This can be achieved with code like this:

class Pagination
{
    protected int $numRecords = -1;

    public function getNumRecords() {
        if ($this->numRecords < 0) {
            $this->countSql = $this->countSql ?? $this->getAutoCountQuery();
            $stmt = $this->pdo->prepare($this->countSql);
            $stmt->execute($this->params);
            $this->numRecords = $stmt->fetchColumn();
        }
        return $this->numRecords;
    }  

}

I left out all the properties and methods irrelevant to this example.

Question: How can you bind PHP parameters to your queries?