Using PHP/SQL, how do I store and retrieve comments stacked/nested under each other?

Imagine the following sutation:

Comment ID 1
|
|--- Comment ID 2 (reply to ID 1)
     |
     |--- Comment ID 3 (reply to ID 2)
     |
     |--- Comment ID 4 (reply to ID 2)
          |
          |--- Comm开发者_开发技巧ent ID 5 (reply to ID 4)

(example in action)

How do I store the above into SQL so that I can retrieve and list it later?

I'm relatively good in PHP/SQL, but this situation is recursively killing my head. Any ideas?

---

It looks like this is all about data modling rather that php or mysql.

You could represent this into a tree structure. Just think that a comment just needs to have a parent. Each comment, one parent. However the first item in the tree (the root element) won't have a parent)

So, to model this in OOP you would use a class Comment that will have itself associtated and the name of that relationship will be is_child_of. One of them would have a null value. If you want to get a step further into this solution check the Composite pattern ;)

Regarding the database model, you just need a table Comments that will have whatever you need about them plus a field called is_child_of. So each comment will have a father but every comment on the highest level of the tree.

As comments have an order, (in your example it wouldn't be the same to swap comments 3 and 4) you should take into account that you will have to sort them in some way (ID if it is an incremental value or a datetime).

Hope that helps :)

---

You could store your data as a nested set. This is a very common way to represent tree-like data in a SQL table.