Digg style comments systems

damasta

I'm trying to put together a comments system like Digg's or Slashdot's where a user can comment on an article. I have that working fine. I'd like for a user to be able to comment on another user's comment. I would like for it to indent a little. I can do all that. My problem comes in when displaying the data properly.
I can list all the comments according to their comment id, but that just puts them in sequential order and not under their respective reference comment.
In my db, I have the comment's id and a reference id which is the referenced comment's originating id.
Does this make any sense at all? I hope so. If not, I can try to elaborate a little more. Thanks in advance for any help.

Roger_Ramjet

When using parent/child relationships like this, you have to join the table to itself to get the list of children for any/all parents

table comments
cid autoinc id column
aid int FK of articles table
pid int parent id when child or NULL when parent
uid int FK of users table
comment text

// to get a list of all parents
"SELECT * FROM comments WHERE aid=$aid AND pid IS NULL";

// to extend it to include all children
"SELECT *  FROM comments p LEFT JOIN comments c ON p.cid=c.pid
WHERE p.aid=$aid AND p.pid IS NULL
ORDER BY p.cid, c.cid";

That will give you output like this

p.cid | p.aid | p.pid | p.uid | p.comment | c.cid | c.aid | c.pid | c.uid | c.comment

1|21|NULL|18|whatever|NULL
2|21|NULL|19|rhubbarb|18|21|2|57|pie
2|21|NULL|19|rhubbarb| 29|21|2_|38|custard
3|21|NULL|57|waffle|14|21|3|29|pancake
3|21|NULL|57|waffle_|19|21|3|18|scone
3|21|NULL|57|waffle|39|21|3|24|damper
4|21|NULL|33|whatever|8_|21|4|24|you say
5|21|NULL|52|i say___|NULL

The main reason you have to do it this way is because of the sorting. If you just selected all the comments with the same article id then you would have no way to sort the children directly under their respective parents.

damasta

Okay, this might be a little beyond my grasp and I'm a little confused. I see what you're saying but I'm kind of lost on how to implement it. Let me show you what my code currently looks like.

$Comments_Query = "SELECT * FROM comments where a_id = '$_GET[a]' Order by timestamp";
$Comments_Result = mysql_db_query($DBName, $Comments_Query, $Link) 
       or die ("Couldn't execute query.".mysql_error());
while ($row = mysql_fetch_array($Comments_Result)) {
		$Comment_ID = $row['c_id'];
		$Article_ID = $row['a_id'];
		$Username = $row['u_name'];		
		$Comment = $row['comment'];
		$Timestamp = $row['timestamp'];

I really appreciate your help.

Roger_Ramjet

Loop control is what this is about. Lots of discussions about that here.
Basically you track the parent and alter the display when it changes

Whoops, just looked back at my query and :o

Should be

$query = "SELECT *  FROM comments p LEFT JOIN comments c ON p.c_id=c.p_id
     WHERE p.a_id=$aid ORDER BY p.ci_d, c.c_id"; 

$result // etc

$current_id = NULL;

while ($row = mysql_fetch_array($result)) {
   // check for new parent
   if ($row['p.c_id'] != $current_id) {
      // new parent so display the parent comment
      echo '<div class="parent">';
           // html for the parent comment
      echo '</div>';
     // now record the current parent
      $curent_id = $row['p.c_id'];
   }
   // but always display any child
   if (!isnull($row['c.c_id'])) {
      echo '<div class="child">';
          // html for the child comment
      echo '</div>';
   }
}

You can make it more obvious, and easier to code, if you alias the column names according to whether it is a parent or child column.