How to traverse the PDOStatement object i.e. fetch first, last previous next etc?

I am trying to access data again and again in a webpage. Is there a better way ? some thing like movetofirst(), movetolast(), movetoprevious(), movetonext() could be nice.

right now i am retrieving the resultset as an array (using fetchall()) and resusing the array again and again.

Is there something like below be done? I need not execute the query again and again. keep the data in a array and consume resources if the result/array is of many hundred rows.

$sql = 'SELECT cityname, statename FROM TBLPLACES ORDER BY cityname, statename';
$stmt = $conn->query($sql);
if ($stmt) {
    while($row=$stmt->fetch()){
       // do some thing
    }

    // do some more thing
    //
    // now here, can i access same $stmt object
    // to fetch resultset again without executing
    // $stmt = $conn->query($sql); again ?
    // (no change in query sql, need to fetch the same static data again.)
    //
    // something like below will be nice.
    //
    // $stmt->movetofirst();
    // while($row=$stmt->fetch开发者_运维知识库()){
    //   do some thing;
    // } 
}

To fetch last result you can do:

$row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST);

Or write the row numbers manualy. Other PDO::FETCH_ORI_* constants here, but essentially you want to pay more attention to the 2nd and 3rd parameter in PDOStatement::fetch() method.

Just use fetchAll to get the whole result in an array:

$sql = 'SELECT cityname, statename FROM TBLPLACES ORDER BY cityname, statename';
$stmt = $conn->query($sql);
// You should not silently ignore errors.
// Set PDO error mode to PDO::ERRMODE_EXCEPTION to handle query failure
$data = $stmt->fetchAll();

foreach ($data as $row) {
  // Do one thing
}
foreach ($data as $row) {
  // Do another thing
}

with PDO you don't need to create a query to get previous or next, try juste : PDO::CURSOR_SCROLL

reference (example 2): http://php.net/manual/en/pdostatement.fetch.php