Resetting Array Pointer in Pdo Results

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
}

Reset row pointer of a mySQL result (PDO)

while($row = $stmt->fetch()){ 
    $rows[] = $row;  //use $rows later
    //Do something here with $row
}

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

Reuse PDO result set multiple times

You can only loop through the PDO statement once, so you should put the result in a temporary array.

Instead of:

$dataTypes = $dataQuery->rowCount() ? $dataQuery : [];

Do

$dataTypes = $dataQuery->fetchAll(\PDO::FETCH_ASSOC);

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
}

Reset php pointer?

use the function mysql_data_seek

http://www.php.net/manual/en/function.mysql-data-seek.php

Though you should look at upgrading to not use mysql_* functions

PDO query returns blank Array

fetch method use something like a cursor to return the results. Since you are exploring from beginning to the end of the result set (moving cursor from the beginning to the end) inside the

while($rows = $query->fetch(PDO::FETCH_ASSOC)){
    echo '<br>'.$rows['title'];
}

while loop above, when you come to code below that, the result set's cursor is already at the end. therefore, you get an empty array as result.

You have to fetch all the results first.

$rows = $query->fetchAll(PDO::FETCH_ASSOC);

Then loop the results as you want.

foreach($row in $rows){
    // do something
}

//again access results below
echo '<pre>', print_r($rows, true), '</pre>';

So the idea is to not to use the query object since its nature of using a cursor. Just retrieve the results, then use them.

Reset MySqli pointer?

This places unnecessary extra strain on the database server. Rather than rewind and reuse the result set, store the whole thing in an array to begin with. You may then use it as many times and different ways as you like in the PHP application code.

Update Fixed code so it should work with MySQLi rather than PDO. Also, made the results into an associative array.

$results = array();
while($getEvent->fetch())
{
    $results[] = array('eventMember'=>$eventMember, 'eventStaff'=>$eventStaff, 'eventMemberID'=>$eventMemberID, 'eventMemberSuper'=>$eventMemberSuper);
}

// Now use $results however you need to.

PDO multiple fetching of same query

You can't fetch from the DB multiple times like this. Do this instead:

$orders = $ordersQuery->fetchAll(PDO::FETCH_ASSOC);

...

foreach ($orders as $val) {
    // stuff 1
}

...

foreach ($orders as $val) {
    // stuff 2
}

---

Related Topics