PHP MySQL Comment System With Replies

Question

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:

id name comment reply_id
1 BigBadProducer1 I love this vst! I use it all the time! 0
2 DrummaBoy504 Hey, this is Drumma from Drum Squad! 0
3 Mike Smith How did you get the vst to sound so good like that... 1
4 BigBadProducer1 Yes, I learned how to tweak it from YouTube Mike S... 3
5 SmoothBeatz3 Dude, Ive been looking for a vst like this for a l... 0
6 FanBoy123 Hey Drumma, when are you going to release a new hi... 2
7 Mike Johnson Hey Fanboy123, why are you such a fanboy of Drum S... 6

Here's the code:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
</head>
<body>
<?php
// Create connection
$conn = new mysqli('localhost', 'root', 'mypassword', 'commentsystem2');

$sql1 = "SELECT * FROM comments WHERE reply_id = 0";
$result1 = mysqli_query($conn, $sql1);

while ($comment = mysqli_fetch_array($result1)) {
    $id = $comment['id'];
    $name = $comment['name'];
    $comment = $comment['comment'];
    
echo '
<div class="comments" style="position:relative; margin:auto; width:500px; border:1px solid black; margin-top:1px;">
<div>'.$name.'</div>
<div>'.$comment.'<br><br></div>
</div>
';

$sql2 = "SELECT * FROM comments WHERE reply_id = $id";
$result2 = mysqli_query($conn, $sql2);
while ($reply = mysqli_fetch_array($result2)) {
$id_reply = $reply['id'];
$reply_name = $reply['name'];
$reply_comment = $reply['comment'];
$reply_id = $reply['reply_id'];


echo '
<div class="replies" style="position:relative; margin:auto; width:500px; border:1px solid black; margin-top:1px;">
<div style="width:80%; text-align:center;">'.$reply_name.' replied to '.$name.'</div>
<div style="width:80%; text-align:center;">'.$reply_comment.'<br><br></div>
</div>
';

 }//end of replies while loop

}//end of comments while loop

?>
</body>
</html>

A few foundational thoughts: recursion is not the only way to do it. Check out “materialized path”: you’d have to redo the reply_id, but the benefits might well be worth it. If you do choose to go forward with recursion, you’ll need to take steps toward a more sophisticated style of coding. Currently, you are using a very procedural style: deal with things as they occur in the presentation of the page. You’ll need to adopt more of an OOP style; at the least you’ll need a function that calls itself. I would suggest using the style in https://stackoverflow.com/a/58806465/2129574 — Tim Morton, Nov 28 '19 at 00:33
When you get the logic at the top and the presentation at the bottom, then it’s easier to incorporate the function to do recursion. — Tim Morton, Nov 28 '19 at 00:38
Thanks for the concepts Tim. Do you have an example or code snippet I can test? I need to keep it inside of the database though, but I'm open to trying your ideas. THanks again. — user3005420, Nov 28 '19 at 00:51

Tim Morton · Answer 1 · 2019-11-28T18:39:10.230

Recursion

Here is a quick example of the recursive solution, using arrays as a mockup of database queries.

Some important notes:

Note the structure: All data fetching and manipulation is done before a single thing is printed out.
This would be far better as an object, which would avoid the GLOBAL tags inside the functions, but this gets you pointed in that direction.
Recursive functions should always have a test to stop recursion in case something goes awry. This example does not! You could use a static variable to count recursions and stop if some limit is reached.

<?php

// mock data of "select * from comments"
$dbRows = [
  1 => ['name' => 'BigBadProducer1', 'comment' => 'I love this vst! I use it all the time!', 'reply_id' => 0],
  2 => ['name' => 'DrummaBoy504', 'comment' => 'Hey, this is Drumma from Drum Squad!', 'reply_id' => 0],
  3 => ['name' => 'Mike Smith', 'comment' => 'How did you get the vst to sound so good like that...', 'reply_id' => 1],
  4 => ['name' => 'BigBadProducer1', 'comment' => 'Yes, I learned how to tweak it from YouTube Mike S...', 'reply_id' => 3],
  5 => ['name' => 'SmoothBeatz3', 'comment' => 'Dude, Ive been looking for a vst like this for a l...', 'reply_id' => 0],
  6 => ['name' => 'FanBoy123', 'comment' => 'Hey Drumma, when are you going to release a new hi...', 'reply_id' => 2],
  7 => ['name' => 'Mike Johnson', 'comment' => 'Hey Fanboy123, why are you such a fanboy of Drum S...', 'reply_id' => 6],
  ];

// mock data of "select id from comments where reply_id=?"
$children = [];
foreach($dbRows as $id => $row) {
  $reply_id = $row['reply_id'];
  $children[$reply_id][] = $id;
}

// format row into html
function formatRow($row, $reply_name='unknown') {
    $out =<<<FORMATROW
<div class="replies" style="position:relative; margin:auto; width:500px; border:1px solid black; margin-top:1px;">
<div style="width:80%; text-align:center;">{$row['name']} replied to {$reply_name}</div>
<div style="width:80%; text-align:center;">{$row['comment']}<br><br></div>
</div>
FORMATROW;

    return $out;
}

// mock of database CRUD function "select * from comments where id=?"
function find($id) {
    global $dbRows;
    if(array_key_exists($id, $dbRows)) {
        return $dbRows[$id];
    }
}

// mock of database CRUD function "select id from comments where reply_id=?"
function findChildren($id) {
    global $children;
    if($id == 0) { return []; }

    $out = [];
    if(array_key_exists($id, $children)) {

        $out = $children[$id];
    }
    return $out;
}

function getRepliesTo($id) {

    // test to end recursion
    // if(!$id) { return; }

    // get parent name
    $row = find($id);
    $parent_name = $row['name'];

    // start indented list every time a traversal is called
    $out = "<ul>\n";

    // list of child ids. if no children are found, 
    // assigns an empty array so that foreach won't barf
    $children = findChildren($id);

    foreach($children as $cid) {

        // if there are children, capture their reply and then call this same function to get replies to this reply            
        if($reply= find($cid)) {
            $out .= '  <li>' . formatRow($reply, $parent_name);
            $out .= getRepliesTo($cid);
            $out .= "</li>\n";
        }
    }

    $out .= "</ul>\n";
    return $out;
}

// print_r(find(1));
// print_r(findChildren(1));
// print_r(findChildren(2));
// print traverse(1);

$id = 1; // $id = (int)$_GET['id'];

$commentData = find($id);
$replyHtml = getRepliesTo($id);

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
</head>
<body>
  <div class="comments" style="position:relative; margin:auto; width:500px; border:1px solid black; margin-top:1px;">
    <div><?= $commentData['name'] ?></div>
    <div><?= $commentData['comment'] ?><br><br></div>
  </div>
  <?= replyHtml ?>
</body>
</html>

Materialized Path

Another method would be to use Materialized Path query, which would require a slight change to the reply_id field:

$dbRows = [
  1 => ['name' => 'BigBadProducer1', 'comment' => 'I love this vst! I use it all the time!',               'reply_id' => '/0/1'],
  2 => ['name' => 'DrummaBoy504',    'comment' => 'Hey, this is Drumma from Drum Squad!',                  'reply_id' => '/0/2'],
  3 => ['name' => 'Mike Smith',      'comment' => 'How did you get the vst to sound so good like that...', 'reply_id' => '/0/1/3'],
  4 => ['name' => 'BigBadProducer1', 'comment' => 'Yes, I learned how to tweak it from YouTube Mike S...', 'reply_id' => '/0/1/3/4'],
  5 => ['name' => 'SmoothBeatz3',    'comment' => 'Dude, Ive been looking for a vst like this for a l...', 'reply_id' => '/0/5'],
  6 => ['name' => 'FanBoy123',       'comment' => 'Hey Drumma, when are you going to release a new hi...', 'reply_id' => '/0/2/6'],
  7 => ['name' => 'Mike Johnson',    'comment' => 'Hey Fanboy123, why are you such a fanboy of Drum S...', 'reply_id' => '/0/2/6/7'],
  ];

Want to find all descendants of 2? select * from comments where reply_id like '/0/2%'

PHP MySQL Comment System With Replies

1 Answers1

Recursion

Materialized Path