我有以下问题。我有三个表:消息,评论和用户。我希望PHP将MySQL数据库中的以下字段的数据打印为我的网页上的表格:message.subject,user.username ,然后是注释数。其他一切正常,但我还没有设法让 PHP 打印评论数量。
这是我到目前为止尝试做的事情:
<?php
include("info.php");
$connect;
$sql="SELECT * FROM message, user
WHERE message.userID = user.userID AND
ORDER BY message.messageID DESC";
$result=mysql_query($sql) or die(mysql_error());
$sql2="SELECT message.messageID, COUNT(*) as comments FROM comment
INNER JOIN
message ON comment.messageID = message.messageID
GROUP BY comment.messageID";
$result2=mysql_query($sql2) or die(mysql_error());
<table>
<thead>
<tr>
<th>Subject</th>
<th>Sender</th>
<th>Number of comments</th>
</tr>
</thead>
<tbody>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td>
<a href="php/message.php?id=<?php echo $rows['messageID']; ?>">
<?php echo $rows['subject']; ?>
</td>
<td>
<?php echo $rows['username']; ?>
</td>
<td>
<?php while($rows2=mysql_fetch_array($result2)){ echo $rows2['comments'];}?>
</td>
</tr>
<?php
} mysql_close(); ?>
</tbody>
</table>
您不能通过一个查询获取所有这些信息吗?
SELECT
m.messageID,
m.subject,
u.username,
c.numOfComments
FROM
message m
INNER JOIN user u ON m.userID = u.userID
LEFT JOIN (SELECT COUNT(1) AS numOfComments, messageID FROM comments GROUP BY messageID) c ON m.messageID = c.messageID
ORDER BY
m.messageID DESC
<小时 />试试这个脚本:
<?php
include("info.php");
connect();
$sql = "
SELECT
m.messageID,
m.subject,
u.username,
c.numOfComments
FROM
message m
INNER JOIN user u ON m.userID = u.userID
LEFT JOIN (SELECT COUNT(1) AS numOfComments, messageID FROM comments GROUP BY messageID) c ON m.messageID = c.messageID
ORDER BY
m.messageID DESC
";
$result = mysql_query($sql) or die(mysql_error());
echo "<table>
<thead>
<tr>
<th>Subject</th>
<th>Sender</th>
<th>Number of comments</th>
</tr>
</thead>
<tbody>'n";
while ($row = mysql_fetch_assoc($result))
{
$row = array_map("htmlspecialchars", $row); // sanitize to prevent XSS
echo " <tr>
<td><a href='"php/message.php?id={$row["messageID"]}'">{$row["subject"]}</a></td>
<td>{$row["username"]}</td>
<td>{$row["numOfComments"]}</td>
</tr>'n";
}
echo " </tbody>
</table>";
?>
还有其他一些更正。例如,您的链接没有结束</a>
标记,并且您的脚本可能容易受到 XSS 攻击。然后是嵌套的while循环,这导致了不必要的复杂性和错误。
我看到的问题是你正在嵌套你的 while 循环。请参阅:http://www.php.net/manual/en/control-structures.while.php#52733
使用一个 sql 语句。
这是最好的方法(我认为(:
$sql = "
SELECT message.*, user.*, commentsCounter.comments FROM message
INNER JOIN user
ON user.userID = message.userID
INNER JOIN ( SELECT COUNT( 1 ) as comments, comment.messageID FROM comment GROUP BY comment.messageID ) commentsCounter
ON commentsCounter.messageID = message.messageID
ORDER BY message.messageID DESC
";
为什么不做下面这样的事情呢?
$query = "SELECT comment FROM message where userId = $userId";
$result = mysql_query($query);
$numberOfComments = mysql_num_rows($result);
echo "$numberOfComments";
至少我相信这就是你要找的?
手册是这样说的 mysql_num_rows((:
mysql_num_rows — 获取结果中的行数
http://www.w3schools.com/sql/sql_func_count.asp
$result3=mysql_query("COUNT(*) FROM comment") or die(mysql_error());
$row=mysql_fetch_array($result3);
$numcomments = $row[0];
echo $numcomments;
我在几个站点上使用它(使用不同的表名(。
答案 2
假设您想要对特定帖子发表评论,请尝试以下操作:
$result3=mysql_query("SELECT DISTINCT message.messageID FROM comment ") or die(mysql_error());
$row=mysql_fetch_array($result3);
$numcomments = count($row);
echo $numcomments;