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)
|
|--- Comment ID 5 (reply to ID 4)
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.
