带有foreach和fetch的PHP PDO [英] PHP PDO with foreach and fetch

问题描述

以下代码：

 <？php 
 try {
 $ dbh = new PDO （mysql：host = $ hostname; dbname = $ dbname，$ username，$ password）; 
 echo连接成功！< br />; 
 $ sql =SELECT * FROM users; 
 $ users = $ dbh-> query（$ sql）; 
 foreach（$ users为$ row）{
 print $ row [name]。  - 。 $ row [sex]。< br />; 
} 
 foreach（$ users as $ row）{
 print $ row [name]。  - 。 $ row [sex]。< br />; 
} 
 $ dbh = null; 
} 
 catch（PDOexception $ e）{
 echo错误是：。 $ E-> etmessage（）; 
 
 $ / code $ / pre 
 $ b $输出：
 
 
 连接成功！ 
 
个人A-male 
人B-female 
  
跑步foreach两次不是我的目的，我只是好奇为什么两个foreach语句只输出一次结果？
 
 
 
以下是类似的情况：
 <？php 
 try {
 $ dbh = new PDO（mysql：host = $ hostname; dbname = $ dbname，$ username，$ password）; 
 echo连接成功！< br />; 
 $ sql =SELECT * FROM users; 
 $ users = $ dbh-> query（$ sql）; 
 foreach（$ users为$ row）{
 print $ row [name]。  - 。 $ row [sex]。< br />; 
} 
 echo< br />; 
 $ result = $ users-> fetch（PDO :: FETCH_ASSOC）; 
 foreach（$ result为$ key => $ value）{
 echo $ key。  - 。 $值。 <峰; br /> 中; 
} 
 $ dbh = null; 
} 
 catch（PDOexception $ e）{
 echo错误是：。 $ E-> etmessage（）; 
 
 $ / code $ / pre 
 $ b $输出：
 
 
 连接成功！ 
 
个人A-male 
个人B-female 
 
 SCREAM：忽略
的错误抑制警告：为foreach（）提供的无效参数
  
但是当我从上面的代码中删除第一个foreach时，输出就会正常：
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 $ $ dbname，$ username，$ password）; 
 echo连接成功！< br />; 
 $ sql =SELECT * FROM users; 
 $ users = $ dbh-> query（$ sql）; 
 
 echo< br />; 
 $ result = $ users-> fetch（PDO :: FETCH_ASSOC）; 
 foreach（$ result为$ key => $ value）{
 echo $ key。  - 。 $值。 <峰; br /> 中; 
} 
 $ dbh = null; 
} 
 catch（PDOexception $ e）{
 echo错误是：。 $ E-> etmessage（）; 
 
 $ / code $ / pre 
 $ b $输出：
 
 
 连接成功！ 
 
 user_id-0000000001 
 name-person A 
 sex-male 
  
有人知道为什么会发生这种情况？我只是一个PHP初学者，感谢您的帮助！
解决方案 
 A  PDOStatement （你有 $ users ）是一个前向游标。这意味着，一旦消费（第一个 foreach 迭代），它不会倒回到结果集的开始处。
 
 
 <您可以在 foreach 之后关闭游标并再次执行语句： 
 
 
  $ users = $ dbh-> query（$ sql）; 
 foreach（$ users为$ row）{
 print $ row [name]。  - 。 $ row [sex]。< br />; 
} 
 
 $ users-> execute（）; 
 
 foreach（$ users为$ row）{
 print $ row [name]。  - 。 $ row [sex]。< br />; 
 
 $ / code> 
或者您可以使用定制的 CachingIterator  with a fullcache： 
 
 
  $ users = $ dbh-> query（$ sql）; 
 
 $ usersCached =新的CachedPDOStatement（$ users）; 
 
 foreach（$ usersCached as $ row）{
 print $ row [name]。  - 。 $ row [sex]。< br />; 
} 
 foreach（$ usersCached as $ row）{
 print $ row [name]。  - 。 $ row [sex]。< br />; 
 
  
您找到 CachedPDOStatement 类作为要点。缓存itertor可能比将结果集存储到数组中更为理智，因为它仍然提供了它所包装的 PDOStatement 对象的所有属性和方法。 b
The following codes:
<?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
Anyone know why does this happen? I'm just a PHP beginner, thanks for your help!
解决方案
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 itertor is probably more sane than storing the resultset into an array because it still offers all properties and methods of the PDOStatement object it has wrapped.

                        
这篇关于带有foreach和fetch的PHP PDO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！