4
$data=$stmt->fetchAll(); //Dumping the data shows the result. It is also setting the cursor at the end

while($data=$stmt->fetch())
{
//Does not enters loop
//If fetchAll() removes it work as usual
}

I know It dont need to fetch data twice. But my main question is How to reset cursor position in PDO?

varuog
  • 3,031
  • 5
  • 28
  • 56

1 Answers1

8

AFAIK there is no possibility to reset cursor position with PDO - that might something to do with compatibility with some databases, that don't support resetting internal cursors.

If you want to iterate twice over the results, fetch it to the array and iterate over this array:

<?php 
$results = $stmt->fetchAll();  
foreach($results as $row) {
    // first
}

foreach($results as $row) {
    // second
}

Edit Some databases support scrollable cursors. To use that, add PDO::CURSOR_SCROLL flag to prepare method (see examples at PDOFetch documentation page). But that only adds possibility to move forward or backward, not rewind completely. Also, not all databases support that type of cursor (e.g. MySQL doesn't).

leafnode
  • 1,382
  • 7
  • 15
  • is there anything like http://www.php.net/manual/en/function.mysql-field-seek.php to use with PDO? – varuog Feb 27 '13 at 12:55
  • 1
    You can use additional arguments to ``PDOStatement::fetch`` method with ``PDO::FETCH_ORI_ABS`` flag as linked above, but this won't work with MySQL as described here https://bugs.php.net/bug.php?id=34625 – leafnode Feb 27 '13 at 13:05
  • @fallenAngel Mysql does not support cursors. So even PDO supports them if available, you can't use them with Mysql. http://stackoverflow.com/questions/12044636/php-pdo-mysql-scrollable-cursor-doesnt-work – djot Sep 29 '13 at 10:04
  • 1
    @djot: Late comment, but _MySQL does not support cursors_ was as wrong a statement when you commented, as it is today: [mysql does support cursors](https://dev.mysql.com/doc/refman/5.0/en/cursors.html) – Elias Van Ootegem Oct 19 '15 at 15:20