Implementing Recursive Comments in PHP/MySQL

Question

I'm trying to write a commenting system, where people can comment on other comments, and these are displayed as recursive threads on the page. (Reddit's Commenting system is an example of what I'm trying to achieve), however I am confused on how to implement such a system that would not be very slow and computationally expensive.

I imagine that each comment would be stored in a comment table, and contain a parent_id, which would be a foreign key to another comment. My problem comes with how to get all of this data without a ton of queries, and then how to efficiently organize the comments into the order belong in. Does anyone have any ideas on how to best implement this?

Answer 1

Try using a nested set model. MySQL has a nice article about storing hierarchical data.

The big benefit is that you don't have to use recursion to retrieve child nodes, and the queries are pretty straightforward. The downside is that inserting and deleting takes a little more work.

It also scales really well. I know of one extremely huge system which stores discussion hierarchies using this method.

Answer 2

Here's another site providing information on that method + some source code.

Answer 3

I normaly work with a parent - child system.

For example:

Table comment( commentID, pageID, userID, comment [, parentID] )

parentID is a foreign key to commentID (from the same table) which is optional (can be NULL).

For selecting comments use this for a 'root' comment:

SELECT * FROM comments WHERE pageID=:pageid AND parentID IS NULL

And this for a child:

SELECT * FROM comments WHERE pageID=:pageid AND parentID=:parentid