I have three tables blogs, comments and replies.
I have the blog_id primary key in the blogs tables, comment_id primary key in the comments tables, and reply_id primary key in the replies tables.
In the blogs table I have one column named as-
blog_id int(11)
In the comments table I have the following columns-
comment_id int(11),
comment_viewer_id int(11),
comm_blog_id int(11),
comment_message longtext,
comment_on datetime
In the replies table I have the following columns-
reply_id int(11),
reply_viewer_id int(11),
reply_blog_id int(11),
reply_comm_id int(11),
reply_message longtext,
reply_on datetime
Suppose I have a blog_id = "2", and
I have a comment_id = "5".
The query which I have used to fetch the comment_message is as follows:
SELECT * FROM `comments`
INNER JOIN `blogs`
ON comments.comm_blog_id = blogs.blog_id
WHERE comments.comm_blog_id = '2'
ORDER BY comments.comment_on
DESC
The query which I have used to fetch the reply_message is as follows:
SELECT * FROM `replies`
INNER JOIN `blogs`
INNER JOIN `comments`
ON replies.reply_blog_id = blogs.blog_id
AND replies.reply_comm_id = comments.comment_id
WHERE replies.reply_blog_id = '2'
AND replies.reply_comm_id = '5'
ORDER BY replies.reply_on
DESC
The query that I have used to fetch the data from the database uses a while-loop inside the while-loop(i.e. Nested-While-Loop) inside the PHP as follow:
$sql_comm = "SELECT * FROM `comments` INNER JOIN `blogs` ON comments.comm_blog_id = blogs.blog_id WHERE comments.comm_blog_id = '2' ORDER BY comments.comment_on DESC";
$result = mysqli_query($conn, $sql_comm);
while ($row = mysqli_fetch_assoc($result)) {
$comment_message = $row["comment_message"];
echo '
<div class="comment_container">
<div class="comment_box">
<div class="comment_card">
<div class="user_details">
<div class="user_dp">
<img src="../img/users_img/black-soil.jpg" alt="">
</div>
<div class="user_name">
<h3>@hello viewer</h3>
</div>
</div>
<p>'.$comment_message.'</p>
<div class="comment_footer">
<div class="like">
<button type="submit">
<i class="fa-regular fa-thumbs-up"></i>
</button>
<p>50</p>
</div>
<div class="dislike">
<button type="submit">
<i class="fa-regular fa-thumbs-down"></i>
</button>
<p>5</p>
</div>
<div class="reply">
<button type="submit">
<p>reply</p>
</button>
<p>0</p>
</div>
</div>
</div>';
$sql_reply = "SELECT * FROM `replies` INNER JOIN `blogs` INNER JOIN `comments` ON replies.reply_blog_id = blogs.blog_id AND replies.reply_comm_id = comments.comment_id WHERE replies.reply_blog_id = '2' AND replies.reply_comm_id = '5' ORDER BY replies.reply_on DESC";
$result = mysqli_query($conn, $sql_reply);
while ($row = mysqli_fetch_assoc($result)) {
$reply_message = $row["reply_message"];
echo '
<div class="comment_box">
<div class="comment_card">
<div class="user_details">
<div class="user_dp">
<img src="../img/users_img/black-soil.jpg" alt="">
</div>
<div class="user_name">
<h3>@hello viewer</h3>
</div>
</div>
<p>'.$reply_message.'</p>
<div class="comment_footer">
<div class="like">
<button type="submit">
<i class="fa-regular fa-thumbs-up"></i>
</button>
<p>50</p>
</div>
<div class="dislike">
<button type="submit">
<i class="fa-regular fa-thumbs-down"></i>
</button>
<p>5</p>
</div>
<div class="reply">
<button type="submit">
<p>reply</p>
</button>
<p>0</p>
</div>
</div>
</div>
</div>
';
}
';
</div>
</div>';
}
Using the above query I have get multiple replies_comment but I have didn’t get multiple comment_message.
I want the result just like this-
Blog-1-Comment-1
Blog-1-Comment-1-Reply-1
Blog-1-Comment-1-Reply-2
Blog-1-Comment-1-Reply-3
Blog-1-Comment-2
Blog-1-Comment-2-Reply-1
Blog-1-Comment-2-Reply-2
Blog-1-Comment-3
Blog-1-comment-4
I want the result just like this-
Blog-1-Comment-1
Blog-1-Comment-1-Reply-1
Blog-1-Comment-1-Reply-2
Blog-1-Comment-1-Reply-3
Blog-1-Comment-2
Blog-1-Comment-2-Reply-1
Blog-1-Comment-2-Reply-2
Blog-1-Comment-3
Blog-1-comment-4
>Solution :
You’re overwriting the $result and $row variables in the inner loop. Use different variable names and you should be OK:
$sql_comm = "SELECT * FROM `comments` INNER JOIN `blogs` ON comments.comm_blog_id = blogs.blog_id WHERE comments.comm_blog_id = '2' ORDER BY comments.comment_on DESC";
$result = mysqli_query($conn, $sql_comm);
while ($row = mysqli_fetch_assoc($result)) {
# large echo snipped for brevity
$sql_reply = "SELECT * FROM `replies` INNER JOIN `blogs` INNER JOIN `comments` ON replies.reply_blog_id = blogs.blog_id AND replies.reply_comm_id = comments.comment_id WHERE replies.reply_blog_id = '2' AND replies.reply_comm_id = '5' ORDER BY replies.reply_on DESC";
$result_reply = mysqli_query($conn, $sql_reply); // Don't overwrite $result
while ($row_reply = mysqli_fetch_assoc($result_reply)) { // Don't overwrite $row
# echo snipped here too
}
}