为什么内连接语句在发布多个回复时显示重复的注释

Question

我有一个评论和回复系统-

$sql = "SELECT comments.comment_id, comments.user, comments.comment, comments.date, 
comments.post_id, replies.reply, replies.username, replies.replyDate 
    FROM comments 
    LEFT JOIN replies ON comments.comment_id = replies.com_id WHERE comments.post_id=$id";

$result = $conn->query($sql);
if ($result->num_rows > 0) {

while($row = $result->fetch_assoc()) {

    $date = $row['date'];
    $mydate = date("M jS g:i a",strtotime($date));

    $rdate = $row['replyDate'];
    $newdate = date("M jS g:i a",strtotime($rdate));

    $user = $row['user'];
    $username = $row['username'];
    $comment = $row['comment'];
    $reply = $row['reply'];
    $comID = $row['comment_id'];
    $post_id= $row['post_id'];

    echo '<div id="comuser">'.$user.': </div>';
    echo '<div id="icomment">'.$comment.'</div>';
    echo '<div id="comdate">'.$mydate.'</div>';
    echo '<div class="replyBTN">reply</div>';
    echo '<form method="post" class="replyForm" action="get_reply.php?reply='.$comID.'&&title='.$title.'&&post='.$post_id.'">';
    echo '<input type="text" id="addReply" name="addReply" placeholder="add reply">';
    echo '</form>';
    echo '<div id="replyCont">';
    echo '<div id="replyUser">'.$username.'</div>';
    echo '<div id="replyDate">'.$newdate.'</div>';
    echo '<div id="reply">'.$reply.'</div>';
    echo '</div>';

}
} else {
echo "<div id='noCom'>no comments..</div>";
}

这一切都工作得很好，我加入了我的两个表，评论和回复。

但当我在一个评论上留下两个回复时，它会复制评论，以显示两个回复，而不是在一个对应的评论下发布两个回复。我的sql语句是错误的吗？

我基于评论id连接这两个表，评论id是评论的主键，在回复时插入到回复表格中。

​

​

谢谢

Answer 1

查询结果的每一行都包含来自comments和replies表的信息。如果您不想在每次回复中看到重复的注释信息，只需在while循环的第一次迭代中打印它。

$first = true;
while($row = $result->fetch_assoc()) {

    $date = $row['date'];
    $mydate = date("M jS g:i a",strtotime($date));

    $rdate = $row['replyDate'];
    $newdate = date("M jS g:i a",strtotime($rdate));

    $user = $row['user'];
    $username = $row['username'];
    $comment = $row['comment'];
    $reply = $row['reply'];
    $comID = $row['comment_id'];
    $post_id= $row['post_id'];

    if ($first) {
        echo '<div id="comuser">'.$user.': </div>';
        echo '<div id="icomment">'.$comment.'</div>';
        echo '<div id="comdate">'.$mydate.'</div>';
        echo '<div class="replyBTN">reply</div>';
        echo '<form method="post" class="replyForm" action="get_reply.php?reply='.$comID.'&&title='.$title.'&&post='.$post_id.'">';
        echo '<input type="text" id="addReply" name="addReply" placeholder="add reply">';
        echo '</form>';
        $first = false;
    }
    echo '<div id="replyCont">';
    echo '<div id="replyUser">'.$username.'</div>';
    echo '<div id="replyDate">'.$newdate.'</div>';
    echo '<div id="reply">'.$reply.'</div>';
    echo '</div>';

}

顺便说一句，您应该使用htmlentities($comment)和htmlentities($reply)来防止跨站点脚本攻击。