PHP Comment System With Replies

I have code that is able to capture the initial comment and the 1st level of replies, but it doesn’t seem to capture the reply to a reply. I know that it requires an indefinite code using some form of recursion, but not quite sure how to properly implement it. Here’s what my phpMyAdmin table looks like:

 

Here’s the code:

<?php
$conn = new mysqli('localhost', 'root', 'Jordan123', 'commentsystem2');

$sql1 = "SELECT * FROM comments WHERE r_to = 0";
$result1 = $conn->query($sql1);

   while($row1 = $result1->fetch_assoc()) {
       $c_id = $row1['id'];
       $c_name = $row1['name'];
       $c_comment = $row1['comment'];

       echo '
       <div class="comments" style="position:relative; margin:auto; width:25%;">
          <div>ID# '.$c_id.'</div>
          <div style="font-weight:bold;">'.$c_name.'</div>
          <div>'.$c_comment.'<br><br></div>
       </div>
       ';

       $sql2 = "SELECT * FROM comments WHERE r_to = $c_id";
       $result2 = $conn->query($sql2);
       
          while($row2 = $result2->fetch_assoc()) {
              $r_id = $row2['id'];
              $r_name = $row2['name'];
              $r_comment = $row2['comment'];
              $r_to = $row2['r_to'];
              echo '
              <div class="comments" style="position:relative; margin:auto; width:25%; padding-left:80px;">
                 <div>ID# '.$r_id.'</div>
                 <div style="font-weight:bold;">'.$r_name.' replied to '.$c_name.'</div>
                 <div>'.$r_comment.'<br><br></div>
              </div>
              ';
       
          }//end of 1st while loop that captures comments.


   }//end of 1st while loop that captures comments.

    
$conn->close();
?>

Notice how some of the replies to replies that are in the table, are missing on output.

 

Perhaps you should use a nested set model? It would mean you don't have to recursively look for child comments. 

https://www.sitepoint.com/hierarchical-data-database-2/

Hmm might be worth thinking about a depth and parent column? A new comment would have $r_to = 0, so any replies, regardless of who to would all go back to a single comment. So if you made parent for example, this would be set to 0 for any initial comment. Then any replies would have the parent set to the id of the initial comment, and you can add a depth so you know how far down you are and then use r_to to know the rest. Might be worth adding a datetime stamp for ordering or showing when they commented.

• id: 1 | r_to = 0 | parent = 0 | depth = 0 | added = 2019-11-01 23:45:16
  • id: 3 | r_to = 1 | parent = 1 | depth = 1 | added = 2019-11-02 10:11:53
    • id: 4 | r_to = 3 | parent = 1 | depth = 2 | added = 2019-11-02 13:15:14
  • id: 9 | r_to = 1 | parent = 1 | depth = 1 | added = 2019-11-02 18:22:06
• id: 2 | r_to = 0 | parent = 0 | depth = 0 | added = 2019-11-02 16:10:03
  • id: 6 | r_to = 2 | parent = 2 | depth = 1 | added = 2019-11-02 17:04:01
    • id: 7 | r_to = 6 | parent = 2 | depth = 2 | added = 2019-11-03 18:16:12
    • id: 8 | r_to = 6 | parent = 2 | depth = 2 | added = 2019-11-04 15:22:22
• id: 5 | r_to = 0 | parent = 0 | depth = 0 | added = 2019-11-06 11:48:27

This should at least mean only 2 calls, 1 for the initial comments and one inside to find all where the parent is the initial comment id.

Just a thought  

Edited November 30, 20196 yr by BrowserBugs