#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    95
    Rep Power
    12

    Question Threaded Comments


    Can anybody guide me as to the best way to build threaded comments into my site. I have a database table that has CommentID, ParentCommentID and Comment as the fields. Any top level comments have a ParentCommentID of 0 but any threaded comments have the CommentID of its parent in the ParentCommentID. I just don't know how to pull it out of the database so (in theory) I can drill down as many threads as possible, rather than a set ammount.

    Has anybody done this?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2007
    Location
    Charlotte
    Posts
    412
    Rep Power
    144
    Originally Posted by decker27
    Can anybody guide me as to the best way to build threaded comments into my site. I have a database table that has CommentID, ParentCommentID and Comment as the fields. Any top level comments have a ParentCommentID of 0 but any threaded comments have the CommentID of its parent in the ParentCommentID. I just don't know how to pull it out of the database so (in theory) I can drill down as many threads as possible, rather than a set ammount.

    Has anybody done this?
    Code:
    Select * 
    from filename
    where ParentCommentID = 0
    should give you the top level comments (thread starters).
    Let's say somebody "chose" a thread whose commentID = 5. To get all the related comments, you'd simply say
    Code:
    Select *
    from filename
    where parentcommentID = 5
    Of course, that would be a stored procedure where you would pass in the "5" as a parameter, you wouldn't want it hard coded.

    I'm NOT trying to be rude, but this is the most basic of stuff, if you're having a hard time with this, I think maybe you're in over your head already.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    95
    Rep Power
    12
    Thanks for replying Ronster but I don't think you understand the question, imagine my data base is as follows
    Code:
    CommentID	ParentCommentID		Comment
    1		0			This is my first comment
    2		1			This is a reply to my comment
    3		0			This is my second comment
    4		2			This is a reply to my first reply
    So what I want to do is list commentID 1 and 3 as they have a ParentCommentID of 0, then I need to list below CommentID 1 CommentID 2 as its parent comment is 1. Then display CommentID 4 under commentID 2 like so
    Code:
    This is my first comment
    	This is a reply to my comment
    		This is a reply to my first reply
    This is my second comment
    So the end result is a threaded comment. I could do this with data shapping the SQL (to a certain number of levels down) but can this be done for (in theory) an infinate number of levels?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2007
    Location
    Charlotte
    Posts
    412
    Rep Power
    144
    Originally Posted by decker27
    Thanks for replying Ronster but I don't think you understand the question, imagine my data base is as follows
    Code:
    CommentID	ParentCommentID		Comment
    1		0			This is my first comment
    2		1			This is a reply to my comment
    3		0			This is my second comment
    4		2			This is a reply to my first reply
    So what I want to do is list commentID 1 and 3 as they have a ParentCommentID of 0, then I need to list below CommentID 1 CommentID 2 as its parent comment is 1. Then display CommentID 4 under commentID 2 like so
    Code:
    This is my first comment
    	This is a reply to my comment
    		This is a reply to my first reply
    This is my second comment
    So the end result is a threaded comment. I could do this with data shapping the SQL (to a certain number of levels down) but can this be done for (in theory) an infinate number of levels?
    Ah, your example helped alot. I was thinking like a forum (like the one we're using right now), where you have threads, but you can't have a reply to a comment that's at a *different level*. All replies in a thread are at the same level.

    Yes, you could do what you're referring to, I think your best option would be to use a CTE (common table expression). CTE's can be a little difficult to get your mind around, just google for SQL CTEs. You should be able to find a general "how CTEs work", and then one that would fit your problem.

    Because your problem is really nothing but a common hierarchy. That hierarchy could represent the employees of a company: Managers, employees, co-workers, etc. Find an example of a CTE solving for a hierarchy, and you'll be in business. I did this at my last job only a few months ago... took me a day or two, but solved it just fine. Unfortunately I'm not still there and didn't take that code with me.

    http://www.mssqltips.com/tip.asp?tip=1520
    might be a good example.

    Definitely doable, you'll just need to spend a little time researching it and getting your CTE right.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2010
    Location
    Spokane
    Posts
    14
    Rep Power
    0
    It is not hard, but it will test you patience

IMN logo majestic logo threadwatch logo seochat tools logo