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();
}
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?
$users = $dbh->query($sql);
foreach ($users as $row) {
print $row["name"] . "-" . $row["sex"] ."<br/>";
}
foreach ($users as $row) {
print $row["name"] . "-" . $row["sex"] ."<br/>";
}
Here $users
is a PDOStatement
object over which you can iterate. The first iteration outputs all results, the second does nothing since you can only iterate over the result once. That's because the data is being streamed from the database and iterating over the result with foreach
is essentially shorthand for:
while ($row = $users->fetch()) ...
Once you've completed that loop, you need to reset the cursor on the database side before you can loop over it again.
$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/>";
}
Here all results are being output by the first loop. The call to fetch
will return false
, since you have already exhausted the result set (see above), so you get an error trying to loop over false
.
In the last example you are simply fetching the first result row and are looping over it.
foreach over a statement is just a syntax sugar for the regular one-way fetch() loop. If you want to loop over your data more than once, select it as a regular array first
$sql = "SELECT * FROM users";
$stm = $dbh->query($sql);
// here you go:
$users = $stm->fetchAll();
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
This is because you are reading a cursor, not an array. This means that you are reading sequentially through the results and when you get to the end you would need to reset the cursor to the beginning of the results to read them again.
If you did want to read over the results multiple times, you could use fetchAll to read the results into a true array and then it would work as you are expecting.
Source: Stackoverflow.com