website comments system

[safra]

Hi,

I want to integrate a comments system on my website and I do want to make it possible to reply on someone elses post in an unlimited way. So the initial comment may end up in a tree-like structure of comments.

Initially the results are ordered by descending dates and, I guess, for tree like structured comments in ascending order. The question is, what is the best way to approach this in terms of setting up the mysql table and the mysql SELECT queries.

I belief that when you add a column "topID" and a column "parentID" to the table you should have enough data to make the queries. When a comment is a reply on another comment you put the id of the first comment in that tree structure in the "topID" column and the id of the comment which is being replied on in the "ParentID" column. Am I thinking right here? I have absolutley no idea how to do the MySQL SELECT query. Would it be possible to get all data ordered correctly with one MySQL query?

Hope someone can guide me a bit in the right direction with this!

Thanks,
Raoul

[tomhath]

The design you have will work, but you won't be able to build the tree in one query. Fetch the rows into an array and build the tree with a loop or (better) a recursive function.

[safra]

Thanks, I will try that! Just in case, is there a design structure that will allow one query only?

[tomhath]

Not in SQL. You could do it with a stored procedure but it would be just as easy to do it in php. Since you're storing the topID in each record, just read them all and build the tree with what you get back.