带有 foreach 和 fetch 的 PHP PDO
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15385965/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP PDO with foreach and fetch
提问by nut
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?
运行“foreach”两次不是我的目的，我只是好奇为什么两个“foreach”语句只输出一次结果？
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:
但是当我从上面的代码中删除第一个“foreach”时，输出会变得正常：
<?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?
为什么会发生这种情况？
回答by hakre
A PDOStatement(which you have in $users) is a forward-cursor. That means, once consumed (the first foreachiteration), it won't rewind to the beginning of the resultset.
A PDOStatement（你在 中$users）是一个前向光标。这意味着，一旦使用（第一次foreach迭代），它就不会倒回到结果集的开头。
You can close the cursor after the foreachand execute the statement again:
您可以在 之后关闭游标foreach并再次执行该语句：
$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 CachingIteratorwith a fullcache:
或者您可以使用CachingIterator带有完整缓存的定制缓存：
$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 CachedPDOStatementclass as a gist. The caching itertor is probably more sane than storing the resultset into an array because it still offers all properties and methods of the PDOStatementobject it has wrapped.
你发现这CachedPDOStatement门课是一个要点。缓存迭代器可能比将结果集存储到数组中更理智，因为它仍然提供PDOStatement它所包装的对象的所有属性和方法。
回答by Your Common Sense
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
语句上的 foreach 只是常规单向 fetch() 循环的语法糖。如果要多次循环数据，请先将其选择为常规数组
$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..catchthing. Don't use it, but set the proper error reportingfor PHP and PDO
也放弃那try..catch件事。不要使用它，而是 为 PHP 和 PDO设置正确的错误报告
回答by Kevin Garman
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 fetchAllto read the results into a true array and then it would work as you are expecting.
如果您确实想多次读取结果，您可以使用fetchAll将结果读入一个真正的数组，然后它会按您的预期工作。
回答by deceze
$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 $usersis a PDOStatementobject 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 foreachis essentially shorthand for:
这$users是一个PDOStatement可以迭代的对象。第一次迭代输出所有结果，第二次不执行任何操作，因为您只能迭代一次结果。那是因为数据是从数据库流式传输的，并且对结果进行迭代foreach基本上是以下内容的简写：
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 fetchwill return false, since you have already exhausted the result set (see above), so you get an error trying to loop over false.
这里所有结果都由第一个循环输出。对 的调用fetch将返回false，因为您已经用尽了结果集（见上文），因此您在尝试循环时会遇到错误false。
In the last example you are simply fetching the first result row and are looping over it.
在最后一个示例中，您只是获取第一个结果行并对其进行循环。
