带有 foreach 和 fetch 的 PHP PDO

2021-12-26 00:00:00 foreach fetch php mysql pdo

以下代码:

<?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"语句只输出一次结果?

Running "foreach" twice is not my purpose, I'm just curious why TWO "foreach" statements only output the result once?

以下是类似的情况:

<?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"时，输出会变得正常:

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

输出:

Connection is successful!

user_id-0000000001
name-person A
sex-male

为什么会发生这种情况?

Why does this happen?

推荐答案

A PDOStatement(您在 $users 中有)是一个前向光标.这意味着，一旦使用(第一次 foreach 迭代)，它就不会倒回到结果集的开头.

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.

可以在foreach之后关闭游标，再次执行语句:

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

或者你可以使用定制的 CachingIterator 和完整缓存来缓存:

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

您找到CachedPDOStatement 类作为要点.缓存迭代器可能比将结果集存储到数组中更合理，因为它仍然提供它所包装的 PDOStatement 对象的所有属性和方法.

You find the CachedPDOStatement class as a gist. The caching iterator is probably more sane than storing the result set into an array because it still offers all properties and methods of the PDOStatement object it has wrapped.

相关文章