Resetting array pointer in PDO results

Question

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]
}

Solution 1

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
}

Solution 2

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

Solution 3

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 .

Solution 4

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

View more solutions

Comments

[user1028866]

Wow! That seems obvious. Don't know why I was stuck on using a while loop. Thanks for the kick in the head!

[Russia Must Remove Putin]

Can you explain in the text of the Answer?

[honk]

The OP wants to iterate through a fetched array twice. I can only see one loop in your code. So, in how far is your code a solution to the problem of the OP?

[AnrDaemon]

Because you ignored the documentation.

[Robert]

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.

[Aniket Singh]

This is not good idea while working with a large result-set. All data will be stored in array.

[Stephen R]

"Fatal error: Uncaught Error: Trying to clone an uncloneable object of class PDOStatement"

[Alexandre T.]

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.

[Your Common Sense]

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

[Robert]

@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]

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

[Your Common Sense]

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