Multilooping: Comment Thread

$threadquery=mysql_query("SELECT * from threads where topic_id='$id'");
			  while($getthreadrows=mysql_fetch_row($threadquery))
			  {
				  echo "<div class=singlepost>".$getthreadrows[2]."</div>";
			  }

You can see Its fetching each comment into a div. But I need to do one additional thing too. Each comment may have some reply comment. How to get em like simple thread style as we see in wordpress comments.
Here is trying to give a visual example how output should be,

XYZ says: hsdahfkasydf astdfsa dftasdf astdfasdf
TYU replied: why you writing wirdo?
IOP replied: wtf!
VBN says: I love daniweb.
DFG replied: me too.

I guess I need two query, one for mother comment and one for child comment(or reply whatever you say) and 2nd query need to be looped with for(....as...) ? Anyother ideas?

[vibhaJ]

You can design your database this manner:

commentId| parentCommentId |user |comment
1		 |	0			   |XYZ  |hsdahfkasydf astdfsa dftasdf astdfasdf
2		 |	1			   |TYU  |why you writing wirdo?
3		 |	1			   |IOP  |wtf!
4		 |	0			   |VBN  |I love daniweb.
5		 |	4			   |DFG  |me too.

JUST ensure that you use the ORDER BY to good effect - if using the table structure above (standard comment table). Perhaps adding datetime useful. You could use recursion, but you may find placing data into an array would be better and then looping over those, e.g.

pseudocode:

...ORDER by comment_id:
while loop over recordset{
   if($parent_id == 0){
      $thread[$comment_id]['first'] = array(...data for thread starter...);
   }else{
      $thread[$parent_id]['replies'][] = array(...data for replies...);
   }
}

foreach(..thread.. as $key => $value){
   echo "<h3>THREAD ID: $key</h3>";
   if(isset(...any replies...)){
     foreach(..replies.. as $anotherkey => $anothervalue){
       echo "<p>REPLY ID: $anotherkey</p>";
   }
}

Nothing tested, just a few thoughts while I wait for some chemicals to dry.

@vibhadevit I already have. Even I can fetch replies set by set but I'm poor at looping and thats why I can't figure out how to fetch a particular set of replies just below the main comment. Any help? Btw thank for your effort :)

@ardav

...ORDER by comment_id:
while loop over recordset{ //do you mean using mysql_fetch_row or array or something like this?
   if($parent_id == 0){ 
      $thread[$comment_id]['first'] = array(...data for thread starter...); //data for thread starter? my bad i didn't get it :S
   }else{
      $thread[$parent_id]['replies'][] = array(...data for replies...);
   }
}
 
foreach(..thread.. as $key => $value){ //"..thread.." would it be a variable? $key => $value??....please don't mind could you explain little further?
   echo "<h3>THREAD ID: $key</h3>";
   if(isset(...any replies...)){ //checking if not null or something right?
     foreach(..replies.. as $anotherkey => $anothervalue){
       echo "<p>REPLY ID: $anotherkey</p>";
   }
}

If you need any additional info like database design or something please let me know.

OK, I set up a dummy table like this:

comment_id (int, PK/autoincrement)
user_id (int, FK/ on users table - not included for brevity)
titled (varchar-255)
comment (text)
datetm (datetime)

Here's the code (I tried it - it works for me)

//THIS IS TO POPULATE AN ARRAY
$rs = mysql_query("SELECT * FROM comments ORDER by comment_id");
while($d = mysql_fetch_array($rs)){
   extract($d); //this turns $d array into keyname variables, e.g. $d['parent_id'] into $parent_id
   if($parent_id == 0){ //if a thread starter
      $thread[$comment_id]['first'] = array("id" => $comment_id,"title" => $titled,"comment" => $comment,"user_id" => $user_id, "datetm"=> $datetm); 
   }else{ //if a reply
      $thread[$parent_id]['replies'][] = array("id" => $comment_id, "comment" => $comment,"user_id" => $user_id,"datetm"=> $datetm);
   }
}
//THIS IS FOR HTML OUTPUT. Notice I've included class attributes, for easy CSS styling 
foreach($thread as $th){
   echo "<h3 class=\"starter\">{$th['first']['title']} by {$th['first']['user_id']} on {$th['first']['datetm']}</h3><div class=\"comment\">{$th['first']['comment']}</div>";
   if(isset($th['replies'])){ //check to see if there are any replies 
     foreach($th['replies'] as $rep){
       echo "<h4 class=\"reply\">REPLY by {$rep['user_id']} on {$rep['datetm']}</h4><div class=\"commentRep\">{$rep['comment']}</div>";
     }
   }
}

NOTE - this uses user_id - you'll want username - so use an INNER JOIN to get this info in the SQL statement.
Also I've used datetime - this isn't the nicest date format for output, so you could play with this.
In addition, comments and titles may contain bad text so you may need to use striptags() or htmlentities() to make it safe.
mysql_real_escape_string() should be used BEFORE the data is placed into the DB. You can even use the comment_id as a permalink for the title.
I've neglected to allow a title for replies, but you can do so.

??EDIT
forgot, perhaps you should have a 'status' field as well. If you want to moderate any posts before allowing them. Or if you somebody wants to flag it up as offensive, etc.

//EDIT

Your users table could have an email field. If an user has a gravatar - you can do it so that their gravatar is displayed automatically. Search gravatar if this is new to you.

Hope it helps.

You are crystal clear now, and seems this gonna work pretty fine...but yet I could not start messing with this cuz

if($parent_id == 0)//where is $parent_id defined??Until I get this i can't start doin somethin :/

Plus seems you are using only one table for both main comment plus replies. But I thought two table would do better. One for only main comments and one for replies. Of course comment_id will be defined in reply table rows.

PS:Your table structure is basically same as my main comment table. Still not trying gravatar and thanks for other tips. I already have a status column, default value is 0, if its set 1 then that comment is banned. Anyway these aren't our headache though :)

I'd use the one table, no real need for two. If you have two, probably even more of a headache. The one table approach utilises the parent_id field.

If a comment is a thread starter (new), then it has a value of 0. If it is a reply, the parent_id refers to the comment_id of the thread starter. Simple.

This is my final table layout:
comment_id| topic_id| comment| comment_date| commentor_id| thread_starter

And this is modified code:

$rs = mysql_query("SELECT * FROM threads ORDER by comment_id");
while($d = mysql_fetch_array($rs)){
   extract($d); //this turns $d array into keyname variables, e.g. $d['parent_id'] into $parent_id
   if($thread_starter==""){ //if a thread starter
      $thread[$comment_id]['first'] = array("id" => $comment_id,"comment" => $comment,"user_id" => $commentor_id, "datetm"=> $comment_date); 
   }else{ //if a reply
      $thread[$thread_starter]['replies'][] = array("id" => $comment_id, "comment" => $comment,"user_id" => $commentor_id,"datetm"=> $comment_date);
   }
}
//THIS IS FOR HTML OUTPUT. Notice I've included class attributes, for easy CSS styling 
foreach($thread as $th){
   echo "<h3 class=\"starter\">Posted by {$th['first']['user_id']} on {$th['first']['datetm']}</h3><div class=\"singlepost\">{$th['first']['comment']}</div>";
   if(isset($th['replies'])){ //check to see if there are any replies 
     foreach($th['replies'] as $rep){
       echo "<h4 class=\"reply\">REPLY by {$rep['user_id']} on {$rep['datetm']}</h4><div class=\"postcomment\">{$rep['comment']}</div>";
     }
   }
}

Working but we made a mistake I think because this code can't detect which replies belongs to which comment. I made some change so take a look if I messed it. Btw whenever thread_starter=="" argument returns false its outputting a row as reply (thats fine) but it puts that reply right below the very first comment. And we din't not put any column in the table to determine the comment_id of a reply, did we?

Seems we are near to the solution, need just little more work :)

OH sorry I think I got it....I just need to update thread_starter column with the value of comment_id when its a reply! Right?

THANK YOU! this thread can be marked as solved now :)