PHP PDO with foreach and fetch

Question

The following code:

<?php try {     $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);     echo "Connection is successful!<br/>";     $sql = "SELECT * FROM users";     $users = $dbh->query($sql);     foreach ($users as $row) {         print $row["name"] . "-" . $row["sex"] ."<br/>";     }     foreach ($users as $row) {         print $row["name"] . "-" . $row["sex"] ."<br/>";     }     $dbh = null; } catch (PDOexception $e) {     echo "Error is: " . $e-> etmessage(); } 

Output:

Connection is successful!  person A-male person B-female 

Running "foreach" twice is not my purpose, I'm just curious why TWO "foreach" statements only output the result once?

Following is the similar case:

<?php try {     $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);     echo "Connection is successful!<br/>";     $sql = "SELECT * FROM users";     $users = $dbh->query($sql);     foreach ($users as $row) {         print $row["name"] . "-" . $row["sex"] ."<br/>";     }     echo "<br/>";     $result = $users->fetch(PDO::FETCH_ASSOC);     foreach($result as $key => $value) {         echo $key . "-" . $value . "<br/>";     }     $dbh = null; } catch (PDOexception $e) {     echo "Error is: " . $e-> etmessage(); } 

Output:

Connection is successful!  person A-male person B-female  SCREAM: Error suppression ignored for Warning: Invalid argument supplied for foreach() 

But when I delete the first "foreach" from the above codes, the output will become normal:

<?php try {     $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);     echo "Connection is successful!<br/>";     $sql = "SELECT * FROM users";     $users = $dbh->query($sql);      echo "<br/>";     $result = $users->fetch(PDO::FETCH_ASSOC);     foreach($result as $key => $value) {         echo $key . "-" . $value . "<br/>";     }     $dbh = null; } catch (PDOexception $e) {     echo "Error is: " . $e-> etmessage(); } 

Output:

Connection is successful!  user_id-0000000001 name-person A sex-male 

Why does this happen?

Answer 1

A PDOStatement (which you have in $users) is a forward-cursor. That means, once consumed (the first foreach iteration), it won't rewind to the beginning of the resultset.

You can close the cursor after the foreach and execute the statement again:

$users       = $dbh->query($sql); foreach ($users as $row) {     print $row["name"] . " - " . $row["sex"] . "<br/>"; }  $users->execute();  foreach ($users as $row) {     print $row["name"] . " - " . $row["sex"] . "<br/>"; } 

Or you could cache using tailored CachingIterator with a fullcache:

$users       = $dbh->query($sql);  $usersCached = new CachedPDOStatement($users);  foreach ($usersCached as $row) {     print $row["name"] . " - " . $row["sex"] . "<br/>"; } foreach ($usersCached as $row) {     print $row["name"] . " - " . $row["sex"] . "<br/>"; } 

You find the CachedPDOStatement class as a gist. The caching iterator is probably more sane than storing the result set into an array because it still offers all properties and methods of the PDOStatement object it has wrapped.

Answer 2

Executing the same query again only to get the results you already had, as suggested in the accepted answer, is a madness. Adding some extra code to perform such a simple task also makes no sense. I have no idea why people would devise such complex and inefficient methods to complicate such primitive, most basic actions.

PDOStatement is not an array. Using foreach over a statement is just a syntax sugar that internally uses the familiar one-way while loop. If you want to loop over your data more than once, simply select it as a regular array first

$sql = "SELECT * FROM users"; $stm = $dbh->query($sql); // here you go: $users = $stm->fetchAll(); 

and then use this array as many times as you need:

foreach ($users as $row) {     print $row["name"] . "-" . $row["sex"] ."<br/>"; } echo "<br/>"; foreach ($users as $row) {     print $row["name"] . "-" . $row["sex"] ."<br/>"; } 

Also quit that try..catch thing. Don't use it, but set the proper error reporting for PHP and PDO