Reset Cursor Position in Pdo

Reset cursor position in PDO

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

Resetting array pointer in PDO results

Save your results to an array and then loop that array twice.

$pdo = new PDO('mysql:host=' . $host . ';dbname='.$database, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('SELECT * FROM mytable WHERE active = 1 ORDER BY name ASC');
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->execute();

$rows = $stmt->fetchAll();

foreach ($rows as $r) {
    // first run
}

foreach ($rows as $r) {
    // seconds run
}

What is a cursor in PDO?

A cursor is nothing more than a pointer. The word has different meanings depending on the context, but it always means more or less something that points at another thing.

In the context of databases, there exist database cursors. Implicitly every result set has a cursor. You can also declare special cursors, for example in stored procedures. Usually, cursors are forward-only iterators, but some databases offer more advanced options.

PDO is not a database, so what does cursor mean in the context of PDO?

Depending on the underlying database and the query mode, PDO might prefetch(buffer) all rows into PHP memory or retrieve each row one by one from the database. There might also be multiple result sets after the execution of a single query.

closeCursor() "closes cursor" in two different ways if we can even say it closes anything at all. In case of unbuffered queries, closing means reading all remaining rows from the database server, after which the database will close the cursor itself. In case of buffered queries, PDO will free the memory associated with the result set. It also ensures that all pending results sets from the server are read and traversed.

Internally, PDO's driver will keep a cursor for buffered results. It's just a int field that points to the current index in the result set.

If you are using buffered queries, which is the default, then calling closeCursor() seems useless. If you are using unbuffered queries then calling closeCursor() seems pretty useless too... except in situations where you want to manually free up the database connection. These would be extremely rare as most of the time you are dealing with one statement at a time and buffered queries are the norm.

What is PDO::ATTR_CURSOR?

From php.net:

Selects the cursor type. PDO currently supports either PDO::CURSOR_FWDONLY and PDO::CURSOR_SCROLL. Stick with PDO::CURSOR_FWDONLY unless you know that you need a scrollable cursor.

As mentioned above, some databases support scrollable cursors. Databases that don't might still support simple forward-only cursors for result sets.

While PDO_MySQL doesn't actually support cursors, mysqli does. Let me explain how they work on MySQL example. It doesn't offer scrollable cursors, but you can ask MySQL to open a single non-scrollable read-only cursor. It works just like buffered mode on PHP side, except that the results are not fetched from the server. They are "buffered" on the server with an explicit cursor attached to it. This means that you can still execute other queries on the connection link, while the results are pending on the server-side. You can open only a single cursor at a time, which means you can't SELECT two results sets at the same time anyway. Such cursors might also be open by stored procedures, but this functionality is a little broken...

Of course, all of the above is super-advanced PDO usage. Most users will be happy with buffered result sets in PHP. Don't worry about cursors, they're usually more trouble than their worth.

Cursor position problems when running two consecutive while loops

Use pg_result_seek() http://php.net/manual/en/function.pg-result-seek.php

$result = pg_query($conn, "SELECT author, email FROM authors");
if (!$result) {
  echo "An error occurred.\n";
  exit;
}

while ($row = pg_fetch_row($result)) {
  echo "Author: $row[0]  E-mail: $row[1]";
  echo "<br />\n";
}

pg_result_seek($result, 0);

while ($row = pg_fetch_row($result)) {
  echo "Author: $row[0]  E-mail: $row[1]";
  echo "<br />\n";
}

Is it possible to rewind a PDO result?

I'm pretty sure this is database dependent. Because of that, it is something you should try to avoid. However, I think you can achieve what you want by enabling buffered queries. If that doesn't work, you can always pull the result into an array with fetchAll. Both solutions have implications for your applications performance, so think twice about it, if the resultsets are large.

Is it possible to rewind a PDO result?

I'm pretty sure this is database dependent. Because of that, it is something you should try to avoid. However, I think you can achieve what you want by enabling buffered queries. If that doesn't work, you can always pull the result into an array with fetchAll. Both solutions have implications for your applications performance, so think twice about it, if the resultsets are large.

What is PDO scrollable cursor?

It creates a cursor for the query, which allows you to iterate over the result set without fetching the whole result at once. A scrollable cursor, specifically, is one that allows iterating backwards.

Example use: You can scroll forward until you find the record you need and iterate back to fetch the previous records, if you need them, too.

---

Related Topics