Nested while loops

Question

I have a system of comments. Each comment may receive replies, and each reply may receive replies, ad nauseam.

Thus, my database contains a table named "comments" with the following important fields:

id
text
reply_to

...under reply_to, of course, goes the id of the comment to which it is a reply.

So now, the problem is simple: how do I display all the comments, but knowing that after each comment must come the replies to it, and after each reply must come the replies to the reply, etc?

What I tried the most, and what I keep coming back at, is something like this:

$query = mysql_query("SELECT * FROM comments WHERE reply_to=0");
while ($comment = mysql_fetch_array($query))
    include("comment.php");

And in comment.php, what I have is:

foreach ($comment as $key = $value) $$key = $value;
echo $text;
echo "<div style='margin-left:30px;'>"; //A margin for a little indent
$subquery = mysql_query("SELECT * FROM comments WHERE reply_to=$id");
while ($comment = mysql_fetch_array($subquery))
    include("comment.php");
echo "</div>";

But if I reproduced correctly the essence of my code, the problem is this: that after the first reply is echoed, it goes on to the first reply of the first reply, and then the first reply of the first reply of the first reply, but the loop never gets to the second reply of anything. So for example, supposing the table had 3 comments, each of which had 3 replies, and each of which had 3 replies, etc, the above code would output:

Comment
  First reply
    First second-order reply
      First third-order reply
        ...

I hope I have explained it clearly enough. inb4: I cannot add new columns to the table.

Answer 1

In pseudocode:

function display_comment(comm)
  echo comm's info and text
  children = get children of the comment: SELECT from comments WHERE parent = (comm's id)
  echo <div class="comment-thread">
  foreach children as child
    display_comment(comm) // notice this line
  echo </div>

You must make a function, to make it recursively referenced.

Answer 2

I usually do it this way

  function get_comments($text_id, $parent_id,$depth){
         $sql="SELECT * FROM spam WHERE parent_id='".(int)$parent_id."' AND text_id='".(int)$text_id."' ";
         //..query 
         while ($row=mysql_fetch_assoc($query)){
             //some comment output use $depth*pixels to indent
             $depth++; 
             get_comments($text_id,$row['parent_id'],$depth);
             $depth--;

         }

  }

and in the first call

  get_comments($text_id,0,0); //

Answer 3

$subquery = mysql_query(SELECT * FROM comments WHERE reply_to=$comment_id);

May be some mistake. When you described your table definition, field with comment`s id is named id, but here you are using comment_id. Also, you are not enclosed your SQL statement in quote marks (' '). Is it copy-paste related mistakes?

Answer 4

Hmm, interesting stuff. So here's my thoughts (this is kind of a design question, so I'm guessing there are many approaches). Me personally I'd use a dictionary to represent parent comments. Here's some pseudo-code for how I'd approach this:

First the DB:

+--------------------------+
| comments                 |
+--------------------------+
| id (INT)                 | <-- a unique id for the comment
| post_id (INT)            | <-- the original post / article being replied to
| parent_id (INT)          | <-- the comment this is in response to
| commenter_email (VARCHAR)| <-- just some way to identify the commenter
| comment_text (TEXT)      | <-- the actual comment
+--------------------------+

Second the pseudo code:

function print_comment($comment_id, $replies_children, $comments)
{
    // For every comment at this level...
    foreach($reply_id in $replies_children[$comment_id])
    {
        // Create a div container to contain both this comment AND
        // all child comments. We let CSS take care of indenting.
        echo '<div style="padding-left: 10px;">';

        // Print this comment first...
        echo $comments[$reply_id]['comment_text'];

        // And beneath it print all of the replies to this comment
        print_comment($reply_id, $replies_children, $comments);

        // Finally end this level of comment indentation
        echo '</div>';
    }
}

// Fetch all of the comments at once for post id 10
$sql = "SELECT * FROM comments WHERE post_id = 10;";

// Get the results back (associative array, please)
$results = execute_sql($sql);

// Holds a list of replies (comment ids) for each comment
$replies_children = array();

// Lets us map an ID to the actual full sql result
$comments = array();

foreach($result in $results)
{
    // Make sure there's a list for the parent
    if($replies_children doesnt have key $result['parent_id']))
        $replies_children[$results['parent_id']] = array();

    // Append this item to the parent's list
    $replies_children[$result['parent_id']][] = $result['id'];

    // Allows us to get to this result by id directly
    $comments[$result['id']] = $result;
}

// Assume that id = 0 is the root level
print_comment(0, $replies_children, $comments);

This way you only call off to the database once.

Answer 5

Your life will be a little easier if you also include a column for depth.

http://www.devx.com/tips/Tip/22127