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

问题描述：

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...

我正在尝试使用PDO创建游标的功能来实现分页功能。 p> 目前，我的代码看起来有点像这样（非常复杂，我知道）： p>

  $ pdo = new PDO（）; 
 $ pdo-＆gt; setAttribute  （PDO :: ATTR_CURSOR，PDO :: CURSOR_SCROLL）; 
 //准备的select-query省略
 $ pdoStatement = $ pdo-＆gt; execute（）; 
 
 $ start_index = MAX_THINGS_PER_PAGE * $ current_page  -  MAX_THINGS_PER_PAGE; \  n $ stop_index = MAX_THINGS_PER_PAGE * $ current_page; 
 $ row_count = $ this-＆gt; statement-＆gt; rowCount（）;  //适用于PgSQL驱动程序
 $ index = $ start_index; 
while（（$ row_count＆gt; 0）＆amp;＆amp;（$ index＆lt; $ stop_index））
 {
 //省略try-catch块 
 $ values [] = $ this-＆gt; statement-＆gt; fetch（PDO :: FETCH_ASSOC，PDO :: FETCH_ORI_ABS，$ index）; 
 
  -  $ row_count; 
 + $ $ index; 
  } 
  code>  pre> 
 
 但是，看起来，无论$ start_index是什么，查询只会获取结果集的前10行（即MAX_THINGS_PER_PAGE的值）。 总是。 p> 
 
 
可能我做错了什么，但是使用游标进行分页的艺术似乎有点晦涩难懂......  p> 
  div>

答

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.

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

相关推荐