PDO在表中显示结果

Question

我正试图将准备好的语句的结果显示到一个表中。

以下是查询和尝试的表：

<?php
$s = "SELECT username,fullname,email,userlevel FROM users";
$sth = $dbc->prepare($s, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute();
echo "<div><table class="demoTable">";
echo "<thead><tr>" .
"<th>Username</th>" .
"<th>Fullname</th>" .
"<th>Email</th>" .
"<th>Userlevel</th>" .
"</tr></thead>";
echo "<tbody>";

while($Row = $sth->fetchAll(PDO::FETCH_ASSOC)) {
echo "<tr><td>".$Row['username']."</td>";
echo "<td>".$Row['fullname']."</td>";
echo "<td>".$Row['email']."</td>";
echo "<td>".$Row['userlevel']."</td>";
echo "</tr></tbody>";
}
echo "</table></div>";
?>

使用以上内容，我在屏幕上没有任何不愉快的地方。

当我使用以下内容时：

print_r($Row);

我可以在数组中看到这样的结果：

Array ( 
[0] => Array ( [username] => usr.sname [fullname] => Some Name [email] => [e-mail] [userlevel] => 9 ) 
[1] => Array ( [username] => usr.aname [fullname] => Another Name [email] => [e-mail]  [userlevel] => 1 ) 
[2] => Array ( [username] => usr.fname [fullname] => Final Name [email] => [e-mail] [userlevel] => 1 ) // a few more

因此，查询工作正常。我就是无法将数据显示在表中。

我怎样才能做到这一点？

Answer 1

FWIW，以下对我来说很好…

<?php
/*
DROP TABLE users;

CREATE TABLE users(user_id SERIAL PRIMARY KEY
,username VARCHAR(12) UNIQUE
,fullname VARCHAR(20) NOT NULL
,email VARCHAR(20) NOT NULL
,userlevel INT NOT NULL);

INSERT INTO users VALUES 
(1,'John','John Lennon','[e-mail]',1),
(2,'Paul','Paul McCartney','[e-mail]',1),
(3,'George','George Harrison','[e-mail]',2),
(4,'Ringo','Ringo Starr','[e-mail]',3);
*/

require('path/to/connection/stateme.nts');
$query = "SELECT username,fullname,email,userlevel FROM users";
$data = $pdo->query($query)->fetchAll(PDO::FETCH_BOTH);
// my connection is something like '$pdo = new PDO($dsn, $user, $pass, $options);'
echo "
<div><table class="demoTable">
<thead>
<tr>
<th>Username</th>
<th>Fullname</th>
<th>Email</th>
<th>Userlevel</th>
</tr>
</thead>
<tbody>
";
foreach($data as $row){
echo "
<tr>
<td>{$row['username']}</td>
<td>{$row['fullname']}</td>
<td>{$row['email']}</td>
<td>{$row['userlevel']}</td>
</tr>
";
}
echo "</tbody></table></div>";
?>

输出：

Username Fullname        Email            Userlevel
John     John Lennon     [e-mail]    1
Paul     Paul McCartney  [e-mail]    1
George   George Harrison [e-mail]  2
Ringo    Ringo Starr     [e-mail]   3

Answer 2

我认为问题在于您使用的是fetchAll，它返回查询的所有行，而不是一行接一行。所以用这个代码替换while循环，我认为它应该可以工作。

<?php
$s = "SELECT username,fullname,email,userlevel FROM users";
$sth = $dbc->prepare($s, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute();
echo "<div><table class='demoTable'>";
echo "<thead><tr>" .
"<th>Username</th>" .
"<th>Fullname</th>" .
"<th>Email</th>" .
"<th>Userlevel</th>" .
"</tr></thead>";
echo "<tbody>";
$allRows = $sth->fetchAll();
foreach($allRows as $Row) {
echo "<tr><td>".$Row['username']."</td>";
echo "<td>".$Row['fullname']."</td>";
echo "<td>".$Row['email']."</td>";
echo "<td>".$Row['userlevel']."</td>";
echo "</tr>";
}
echo "</tbody></table></div>";
?>