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] }
Improve this question

7 Answers 7

Reset to default
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 }
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Wow! That seems obvious. Don't know why I was stuck on using a while loop. Thanks for the kick in the head!
Because you ignored the documentation.
This is not good idea while working with a large result-set. All data will be stored in array.
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

Improve this answer

6 Comments

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

Improve this answer

Comments

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

Improve this answer

1 Comment

"Fatal error: Uncaught Error: Trying to clone an uncloneable object of class PDOStatement"
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] }
Improve this answer

Comments

-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(); }
Improve this answer

Comments

-4 
if($stmt->fetchColumn() >= 0) { $stmt->execute(); //Reset cursor while($rs = $stmt->fetchObject()) { echo "Data: ".$rs->data; } } else { echo '0'; }
Improve this answer

2 Comments

Can you explain in the text of the Answer?
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?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.