Reputation: 745
I'm working on comments system where display post, comments (parent comments), and comments repliess (child comments).
MySql comments table looks like below:
comment_id int primary_key
post_id int // where i save the post id
author varchar
comment text
parent_id int // where i save parent comment id in case this comment is reply of another comment
Now, My case is when I go through MySql result set to print comments info. exactly when trying to print child comments (comments of other comments ) and parent comments ( comment of the post ).
My current method to achieve this, is selecting all comments that have no parent_id
SELECT * FROM comments WHERE parent_id = null
And loop over all result using PHP, then make another query inside the loop for each comment searching if there is any child comments of it.
<?php
$sql_result_set;
while($comment = mysql_fetch_array($sql_result_set, MYSQL_ASSOC){
// process comment info
$sql = "SELECT * FROM comments where parent_id = ".$comment['comment_id'];
$result = mysqli_query($sql);
if(mysql_num_rows($result)>0){
while($child_comments = mysql_fetch_array($result, MYSQL_ASSOC)){
// child comments of the parent
}
}
}
?>
Now, My question is "Is there any better way to do the same thing? "
Your help is appreciated
All the best,
Upvotes: 0
Views: 194
Reputation: 3289
Another way is to collect all the value in an array i.e select all parent_id in above case and the use
WHERE IN(parent_id list)
Upvotes: 1