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.

I would think something like:

for each top-level post "id" {
  show top-level post
  call show_posts_with_r_to = "id"
  }
function show_posts_with_r_to = this-id {
  for each post where r_to = this-id {
    show this post
    call show_posts_with_r_to = "this_id"
    }
  }

As you can see, that’s pseudo code, but it gives you a bit of an idea of how it could work.

If I were you I’d have a look at your database structure, though - there’s no way you should be storing usernames in that table, you should have user-ids that reference a separate user table to store name, email and so on, and use a JOIN to get that information.

Thanks for your reply, but I don’t quite understand.
Are you saying to use a “for each loop” instead of a while loop or
use the for each loop inside of the while loop? Also, where do I
echo out each $row data in that “for each loop”? Thanks again for
the idea.

To get replies you should first prepare the statment for efficient reuse.

$sql = $conn->prepare("SELECT * FROM comments WHERE r_to = ?");

Then execute in a while which runs while the query returns a result, each time replacing the “reply to ID” parameter in the execute with the ID of the last result.
Along the lines of…

while($sql->execute([$r_to])){ // while you are getting results
   $result = $sql->fetch();   // Fetch the data
   $replies[] = $result;     // Store the replies in an array
   $r_to = $result['id'] ;    // Set the next reply ID
}

Agreed.

It’s best to keep your HTML output separate from your working logic.
My example should build an array of data which can be output later in the “view” script.

I posted pseudo-code, the choice of exactly what syntax to use is down to you.

On the lines that start “show”. That’s pseudo-code to say here’s where you would display each comment that you retrieve.

It might be wise to set a limit on how many levels of recursion you will go into - that may well be why a lot of forums that allow nested comments like this make you manually open more than the first few.

I’m totally lost now. I’ve never used a while loop with prepared statements the way you’re using it.
I’ve never even seen an example or know how you’ve come up with this. I would need you to show me an example of how to echo out the results with the loop example you’ve given.

I’m using PDO syntax, not mysqli, so it may be a bit different to what you are used to.
It’s not tested, more thinking out loud the sort of thing that may work.

Getting the data and displaying the data are two separate things, so they will be kept separate.
My example was only concerned with getting the data.
Outputting it would be handled elsewhere, but may use a foreach to go through the array that was made.

It was actually quite wrong now I come to test it, been along day.
Should be more like:-

$sql = $conn->prepare("SELECT * FROM comments WHERE r_to = ?");

while($sql->execute([$r_to]) && $sql->rowCount()){
				$result = $sql->fetch();
				$r_to = $result['id'];
				$replies[] = $result ;
			}

But what I did not account for is I notice now, it’s not a liniar conversation with one reply follows the last reply. A comment can have multiple replies, so the conversation goes off in branches.
That makes it a bit more tricky.

Can you give me an example by combining the code that I’ve originally shared, with your “foreach loops”? I’d like to see exactly how your implement both into what I’ve shared. At this point, I’m still confused.

Can you combine the code that I’ve written with your code, except using MySQLi? I’ve never used PDO. I’d like to see you combine your statements with the original structure I’ve shared above.

I will if I get a chance, but really what you’re saying there is “can you write it for me?”. The idea of showing pseudo-code is that I can give you an idea of what I mean, and you can do the actual coding.

Part of the reason I do that is that I struggle to remember syntax when I haven’t coded in PHP for a while (I’ve been doing other things for a bit), and I don’t want to post code that won’t work because of something silly that I’d have seen if I had time to test it. I also use PDO instead of mysqli, so I’m not quite familiar with how the latter works.

Possibly with many syntax errors due to lack of mysqli knowledge:

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

show_posts("0", $conn);

    
$conn->close();

function show_posts($parent, $db) {
  $query = "select id, name, comment, r_to from comments where r_to = ?";
  $stmt->prepare($query, $db);
  $stmt->bindParam("s", $parent);
  $stmt->execute();
  while ($row = $stmt->fetch_assoc()) { 
    echo $row['name']. " " . $row['comment'];
    show_posts($row['r_to'], $db);
    }
  }
?>

I haven’t split out the retrieval and display parts of this as @SamA74 was doing, which is arguably much nicer.

One of the things with a prepared statement is that you can prepare it once, and then keep calling it with different values. So if you were looping through a CSV file, for example, to retrieve data and insert it into a table, you would prepare the query before the loop, and then execute it inside the loop having got the data into the correct place.

2 Likes

Here’s the error message I’m getting:

Notice : Undefined variable: stmt in E:\xampp\htdocs\commentsystem6\index.php on line 19

Fatal error : Uncaught Error: Call to a member function prepare() on null in E:\xampp\htdocs\commentsystem6\index.php:19 Stack trace: #0 E:\xampp\htdocs\commentsystem6\index.php(15): show_posts(‘0’, Object(mysqli)) #1 {main} thrown in E:\xampp\htdocs\commentsystem6\index.php on line 19

<!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', 'Jordan123', 'commentsystem2');


show_posts("0", $conn);

function show_posts($parent, $db) {
  $query = "select id, name, comment, r_to from comments where r_to = ?";
  $stmt->prepare($query, $db);
  $stmt->bindParam("s", $parent);
  $stmt->execute();
  while ($row = $stmt->fetch_assoc()) { 
    echo $row['name']. " " . $row['comment'];
    show_posts($row['r_to'], $db);
    }
  }
  
$conn->close();
?>
</body>
</html>

I did say there might be syntax errors because I don’t use mysqli so you might need to fix some bits. Did you have a look at the documentation for any of those functions, or have a look at a bit of sample mysqli prepared-statement code? I knocked that code up off the top of my head, and I’d be astounded if it had worked straight off. I figured it might be closer than my pseudo-code.

Well, I’ve just finished going over some MySQLI Prepared Statements for MySQLi SELECT statements, and have managed to parse the first level of OP comments or opening original poster comments using the following code (but I still need to figure out how to implement your function that will continue looping through to collect the related reply comments that could be a reply to a reply, etc.):

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

$stmt = $mysqli -> prepare('SELECT name, comment FROM comments WHERE r_to = ?');

$r_to = '0';
$stmt -> bind_param('s', $r_to);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $comment);

while ($stmt -> fetch()) {
	echo $name . "<br>";
    echo $comment;
    echo "<br><br>";
}
?>

But the above code is the proper structure to loop through using a while loop and collect the 1st level of comments where the “r_to” is equal to level 0.

Please be sure to give the code above a quick test to see how else I can implement your code with proper syntax.

Great, I knew the syntax was a bit different (I use PDO, and I can’t do that without referring to notes as I don’t do it frequently enough) but if you’ve got that working, it should be dead easy to put the query inside a function.

Note that you’ll have to add extra columns into the query, at the very minimum you will have to retrieve the r_to column.

I can’t test your code, as I don’t have your database. If it works for you, that’s a good start.

Presuming the code works as you say it does:


show_posts("0", $mysqli);
exit();

function show_posts($parent, $dbconn) { 

$stmt = $dbconn -> prepare('SELECT r_to, name, comment FROM comments WHERE r_to = ?');

$stmt -> bind_param('s', $parent);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($r_to, $name, $comment);

while ($stmt -> fetch()) {
    echo $name . "<br>";
    echo $comment;
    echo "<br><br>";
    // now call the function recursively to get all the child posts for this post
    show_posts($r_to, $dbconn);
  }
}

See that all I’ve done is encapsulate the query and retrieval into a function, passed in the “parent” number and the database connection object, and keep calling it. Any post that has “child” posts will show those as part of the recursion.

Sorry Droopsnoot, but this didn’t work. All it did was parse the very first comment

id:1
BigBadProducer1
I love this vst! I use it all the time!
r_to:0

then it just kept an infinite loop which repeated “BigBadProducer1” and his comment over and over.
The recursion wasn’t being properly executed and nor was any of the reply messages being parsed.

Remember, the table is structured as shown in the image below: