arinze arinze - 2 months ago 15
MySQL Question

display comment and replies?

I am trying to display comment and replies but I don't really know how to do it. This is my table.

comment_id | byy | user_comment | topic_id | parent_id |
1 |obi |comment 1 | 1 | 0 |
2 |chima |comment 2 | 1 | 0 |
3 |eze |comment 1 reply | 1 | 1 |
4 |david |comment 2 reply | 1 | 2 |


This code I wrote is just to display only the comment but I want the comment to show the replies of the comment if there any. Before it displays the next comment

<?php
$querycomment = comment::find()->where(['topic_id'=> Yii::$app->getRequest()->getQueryParam('id')])->all();

foreach ($querycomment as $detail) {
if($detail['parent_id']==0) {
echo 'Country Name: '.$detail['user_comment'].'</br>';
echo 'State Name: '.$detail['byy'].'</br>';
echo 'City Name: '.$detail['name'].'</br>';
echo '</br>';
}
}
?>

Answer

Here is the actual code of the following pseudocode:

<?php 
// print comments and/or replies body
function print_comments( $topic_id, $parent_id ) {
   $all_comments = Comment::find()
               ->where(
                   'topic_id' => $topic_id,
                   'parent_id' => $parent_id
               )->all();

    if( empty($all_comment) ) {
      return "";
    }

    $comments = '<ul>';
    foreach( $all_comments as $comment ) {
        $comments .= '<li>
            <p> '.$comment->user_comment.' </p>
            <p> by: '.$comment->byy.' </p>';

            // print replies
            $comments .= print_comments( $topic_id, $comment->comment_id ); // recursive

        $comments .= '</li>';
    }
    $comments .= '</ul>';

    return $comments;
}
?>

Put the above code on the top of your view file. Now use tho following line where you want to show/echo your comments and replies.

<?php echo print_comments( Yii::$app->getRequest()->getQueryParam('id'), 0); ?>

(previous answer)

You can try to follow this pseudocode:

print_comments( queryParam(id),  0); // parent_id = 0

// print comments and/or replies body
print_comments ( $topic_id, $parent_id ) {
   $all_comments = Comment::find()
               ->where(
                   topic_id => $topic_id,
                   parent_id => $parent_id
               )->all();

    if( $all_comment count = zero )
      return

    <ul>
    foreach( $all_comments as $comment ) {
        <li>
            <p> $comment->user_comment </p>
            <p> by: $comment->byy </p>

            // print replies
            print_comments( $topic_id, $comment->comment_id ); // recursive

        </li>
    }
    </ul>
}

Pros: Easier to understand and implement.
Cons: Uses a lot of query.

Any other way to overcome cons??
Just keep in mind that when used with pagination this method of doing is trickier to implement.

  1. Use single query to get all comments and replies
  2. format all of them to comment => replies relation
  3. loop through the relation and show it
Comments