Sorting MySQL results based on it's context

Question

I'm trying to create a nested comment system using PHP and MySQL. But I'm stuck

• My Database structure is id, body, time, reply and depth.
• A regular comment's reply field will be '0'. If it's replying to another it will correspond to the id of the comment it's replying to.
• depth means how deep it is from the highest parent

So if this is the contents of my table...

+------+-------------+--------+---------+---------+
|  id  |  body       |  time  |  reply  |  depth  |
+------+-------------+--------+---------+---------+
|   1  |  Some msg1  |   1    |    0    |    0    |
|   2  |  Some msg2  |   2    |    0    |    0    |
|   3  |  aReply1    |   3    |    1    |    1    |
|   4  |  aReply2    |   4    |    1    |    1    |
|   5  |  aReply21   |   5    |    3    |    2    |
+------+-------------+--------+---------+---------+

It would appear as something like this...

- (1) Some msg1
-- (3) aReply1
--- (5) aReply21
-- (4) aReply2
- (2) Some msg2

I hope it's possible using this method, it kind of goes beyond my logic.

Answer 1

If you can't change table structure you can just get all rows (select * from table order by time) then generate tree using PHP.

For tree storage I recommend to use Nested Sets algorithm.