25

I'm having trouble moving from MySQL SELECT methods to PDO methods. I want to iterate through a fetched array twice, both times starting with row zero. In MySQL I would use:

mysql_data_seek($result,0);

Using PDO methods, I'm not sure how to accomplish the same thing. The code below is how I am trying to do this. The first while loop works fine but the second while loop returns nothing.

$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();

while($row = $stmt->fetch())
{
    //do something starting with row[0]
}
while($row = $stmt->fetch())
{
    //do something else starting with row[0]
}
Dharman
  • 30,962
  • 25
  • 85
  • 135
user1028866
  • 795
  • 2
  • 8
  • 19

7 Answers7

32

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
}
Marwelln
  • 28,492
  • 21
  • 93
  • 117
  • Wow! That seems obvious. Don't know why I was stuck on using a while loop. Thanks for the kick in the head! – user1028866 Feb 24 '12 at 20:06
  • Because you ignored [the documentation](http://php.net/manual/en/ref.pdo-mysql.php#pdo.constants.mysql-attr-use-buffered-query). – AnrDaemon May 31 '16 at 18:44
  • 4
    This is not good idea while working with a large result-set. All data will be stored in array. – Aniket Singh Sep 27 '17 at 14:14
9

According too the php manual, you can issue a query multiple times,if you prepare a PDOStatement object using PDO::prepare(), you can issue the statement with multiple calls to PDOStatement::execute(). So your code will look like that.

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

//First execute
$stmt->execute();
while($row = $stmt->fetch())
{
    //do something starting with row[0]
}

//Second execute
$stmt->execute();
while($row = $stmt->fetch())
{
    //do something else starting with row[0]
}

source: http://php.net/manual/en/pdo.query.php

Cedriga
  • 3,860
  • 2
  • 28
  • 21
  • 1
    I gave you my +1, this is the correct answer, we don't want fetchAll() some thousand rows nevertheless the question was "Resetting array pointer in PDO results": this really resets the cursor to the first row, thank you. – Robert Aug 24 '17 at 20:31
  • Only a good idea if you're pretty sure you're dealing with SELECT... In my case, I was using it to debug all database queries, so INSERTs were getting always duplicated when in debug mode, it took me a while to figure out why. – Alexandre T. Jan 28 '20 at 03:41
  • @Robert This answer is really, really weird. If you need to issue the same select query that returns a huge result set multiple times you are doing something really weird. And should rethink your approach, instead of applying *such* a brute force. May be you need to add a WHERE or LIMIT clause to your queries to make them different. – Your Common Sense May 01 '20 at 05:46
  • @YourCommonSense the point here is different. It is self explained in the question and in the solution. I had a similar issue at the time of my comment and the reply marked as good, was not the "good" one. A WHERE clause is present, that is not the point, the point was resetting the pointer. – Robert May 02 '20 at 06:26
  • @YourCommonSense ok so fix the reply marked as good, since it was not working. I can't edit my original comment, otherwise I would add "working with small databases", that was my case and unfortunately they were 3 years ago, so I don't even know if they fixed the original reply. At that time this reply was working, while the marked as good was not. – Robert May 02 '20 at 06:38
  • @Robert don't you understand that adding such a condition makes your approach in-scalable? And the accepted answer works perfectly when you select a *sensible* amount of data. While in case you need to select a lot of data, then you should rethink the algorithm to avoid the second pass. THIS would be the correct solution, not such a brute force with fetching the same data again and again. – Your Common Sense May 02 '20 at 06:45
2
fetch — Fetches the next row from a result set

so when it exits the first while it already arrived to the last element of your resultSet that's why the second while returns nothing .

use fetchAll to store all of your results then go through them .

Mouna Cheikhna
  • 38,870
  • 10
  • 48
  • 69
1

sometimes storing the result of fetchAll() is not an option. Instead you can just clone the pdo object before calling fetchAll() like this. 

$pdo_copy = clone $pdo;

$num_rows = count($pdo_copy->fetchAll());

Now I can still use the pdo object to do statements like fetchObject();

Josh Woodcock
  • 2,683
  • 1
  • 22
  • 29
0

You need to tell the statement that it has to be scrollable by providing PDO::CURSOR_SCROLL as option in prepare. And then you use PDO::FETCH_ORI_FIRST to get to the first element again after reaching the end so you can start over.

Full code:

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

//Making cursor scrollable
$options = array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL);
$stmt = $pdo->prepare('SELECT * FROM mytable WHERE active = 1 ORDER BY name ASC',
                $options);

$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->execute();

while($row = $stmt->fetch())
{
    //do something starting with row[0]
}

//For the first row we use PDO::FETCH_ORI_FIRST 
//to get the first element again. This will also 
//move the cursor to that element
$first = true; 
while($row = $stmt->fetch(null, $first ? PDO::FETCH_ORI_FIRST : PDO::FETCH_ORI_NEXT)))
{
    $first = false;
    //do something else starting with row[0]
}
Torge
  • 2,174
  • 1
  • 23
  • 33
-2

Other solution is maybe if you run query again on result queryString and fetch that:

public function fetch_again( $query_result, $fetch_type='fetch' )
{
    return $this->pdo->query($query_result->queryString)->$fetch_type();
}
Harkály Gergő
  • 733
  • 8
  • 18
-4
if($stmt->fetchColumn() >= 0)
{
    $stmt->execute(); //Reset cursor
    while($rs = $stmt->fetchObject())
    {
        echo "Data: ".$rs->data;
    }
}
else
{
    echo '0';
}
user3753956
  • 1