PHP PDO 与 foreach 和 fetch

以下代码：

<?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

运行“foreach”两次不是我的目的，我只是好奇为什么两个“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);
    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()

但是当我从上面的代码中删除第一个“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

为什么会发生这种情况？

A PDOStatement（你有$users) 是一个前向光标。这意味着，一旦消费（第一个foreach迭代），它不会回退到结果集的开头。

您可以在之后关闭光标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/>";
}

或者您可以使用定制的缓存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 找出CachedPDOStatement类作为要点 https://gist.github.com/hakre/5152090。缓存迭代器可能比将结果集存储到数组中更明智，因为它仍然提供迭代器的所有属性和方法。PDOStatement它所包裹的对象。