Ryan D Ryan D - 2 months ago 5
PHP Question

Why does Inner join statement show duplicate comments when posting multiple replies

I have this comment and reply system -

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


It all works great, I am joining the two tables I have, comments and replies..

But when I leave 2 replies on one comment it duplicates the comment to show both replies instead of posting both replies under the one corresponding comment.. Is my sql statement wrong?

I am joining the 2 tables based on the comment id which is the primary key for comments and inserted into the replies table when a reply is left..

enter image description here

Thanks

Answer

Every row of the query result contains information from both the comments and replies table. If you don't want to see the comment information repeated for each reply, just print it on the first iteration of the while loop.

$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>';

}

BTW, you should use htmlentities($comment) and htmlentities($reply) to prevent cross-site scripting attacks.