PHP PDO与foreach和fetch

Question

如下代码：

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

两次运行“ 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();
}

输出：

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();
}

输出：

Connection is successful!

user_id-0000000001
name-person A
sex-male

为什么会这样？

Answer 1

一个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的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/>";
}

您会发现本CachedPDOStatement课程的要点。缓存迭代器可能比将结果集存储到数组中更为理智，因为它仍提供PDOStatement已包装对象的所有属性和方法。