[php] best way to approach multilevel comments

[foxx]

That's pretty much it, how would you do it, I suppose there is a better way than having replyto_cmt field and while printing them comments checking if there are rows with replyto_cmt = id_cmt.

Was thinking about using

You must be registered to see links

, which I'm using for site structure, any better idea?

 

[s-p-n]

Well loading from SQL efficiently is the hard part.

You want to do this with only one query.

So say you have a comment table with `id`, `reply_to`, and `comment`. (just to keep it simple)

For comments that aren't replies, default the `reply_to` field as 0. (Make sure there are no ID's with a value of 0). Make one and ONLY one row with a blank value in the `comment` field. This row is to be eventually filtered out by whatever you display your data with.

Use this query:

SELECT `cmt1`.*, `cmt2`.`comment` as `reply`
FROM `comments` as `cmt1`,`comments` as `cmt2`
WHERE`cmt1`.`id` = `cmt2`.`reply_to` OR (`cmt1`.`reply_to` = 0 AND `cmt2`.`comment`="")

It should load all of the comments in order with their replies. Filter out the repeating comments, and just show the replies. Start the indent at 0.

Here's some rules for inside the loop you use the above query on:

1. If the indent is 0, show the comment AND the reply ONLY if there is a reply. (Otherwise just show the comment), log the reply and indent.
2. If the comment is equal to the previous reply then show the reply ONLY; continue to indent and to log the replies.
3. repeat step 2 as long as it takes..
4. If the comment is equal to a previous reply, but not the most recent, then adjust the indent to a smaller number accordingly. Example, if it's a reply to the first child comment, make the indent 2, for a second child comment. If it's a reply to a parent comment, make the indent 1, etc.
5. If the reply field is blank, display nothing. dispose of the log, set the indent to 0, repeat step 1.


Adjust them as you see fit, I haven't tested any of this except for the query itself.

 

[foxx]

Well considering id_cmt is primary key serial, it could never be 0 anyway.

So far using tree structure seems much better than your method though.. There's still this little problem which would occur with both method, let's say I'd want last 20 comments, but 20 parent comments, it might be a little tricky to limit it in sql..

 

[s-p-n]

Are you using a query like this to do hierarchy..?

SELECT * FROM comments as cmt1
LEFT JOIN comments AS cmt2 ON cmt2.parent = cmt1.id
LEFT JOIN comments AS cmt3 ON cmt3.parent = cmt2.id
LEFT JOIN comments AS cmt4 ON cmt4.parent = cmt3.id
LEFT JOIN comments AS cmt5 ON cmt5.parent = cmt5.id
WHERE cmt1.parent IS NULL

That only allows for 5 level hierarchy, not exactly what I consider multilevel.. If not, how did you achieve it?

You must be registered to see links

Edit:
Or you can do it the drupal method, which is pretty cool:

Say the first comment has an id of 1. A reply to that comment is given the id 1.1.. Another reply to comment 1 is given the id 1.2.. A reply to the 1.1 comment is given the ID 1.1.1.. Another is given 1.1.2.. Etc..

1
1.1
1.1.1
1.1.2
1.2
2
2.1
2.2
2.2.1
2.2.1.1
2.2.1.1.1
3

I'd go with that method

 

[foxx]

I'm on postgres not mysql that's all I can say, I'm drunk as duck

---------- Post added at 03:50 AM ---------- Previous post was at 03:46 AM ----------

I'm pretty sure I could go over 5 levels though, we're using some corporate extended class for zend tree structure..

 

[jMerliN]

There are ways to implement data structures in databases. There are essays, papers, and books on the subject and a lot of the times they're accompanied by the use of dialect-specific SQL capabilities such as recursive CTEs, table-valued variables and the APPLY operator.

You can always just store the data structure in your table and in each node store the depth of that node in the tree so you can perform shallow scans and manipulate the results in PHP to be in the desired structure.