使用绝对位置游标的PDO pgsql获取失败

Question

I am trying to implement a paging feature using PDO's ability to create cursors.

Currently, my code looks a bit like this (very complicated, I know that):

$pdo = new PDO();
$pdo->setAttribute(PDO::ATTR_CURSOR, PDO::CURSOR_SCROLL);
// prepared select-query omitted
$pdoStatement = $pdo->execute();

$start_index = MAX_THINGS_PER_PAGE * $current_page - MAX_THINGS_PER_PAGE;
$stop_index = MAX_THINGS_PER_PAGE * $current_page;
$row_count = $this->statement->rowCount(); // works for the PgSQL driver
$index = $start_index;
while (($row_count > 0) && ($index < $stop_index))
{
    // try-catch block omitted
    $values[] = $this->statement->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, $index);

    --$row_count;
    ++$index;
}

However, seemingly, no matter what $start_index is, the query only fetches the first 10 (which is the value of MAX_THINGS_PER_PAGE) rows of the resultset. Always.

Probably I am doing something wrong, but the art of using cursors for pagination seems to be somewhat arcane and underdocumented...

Answer 1

I just ran into this problem today. PDOStatement::rowCount() does not work for SELECT on some databases. From PDOStatement::rowCount:

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

It took me quite a bit to realize this, as I thought that it was a problem with using cursors.

This is the approach I took: Replace the use of PDOStatement::rowCount() with the following:

<?php
$row_count = count($stmt->fetchAll());
?>

It is not efficient memory-wise, but this is what many databases do to calculate the total number of rows anyway.