PHP PDO with foreach and fetch

fetch, foreach, mysql, pdo, php

Solution

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.

Problem

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?

Original source