MySQL to group child comments under parent comments is not returning correct data result

Question

My JavaScript/PHP/MySQL Project Management app I am building has a panel/div that loads in all Event/Comment records that belong to a Project Task record.

These Activity event log records tell the history of modifications done to a Task record. They alo in the same flow of things have Task Comments.

I am now trying to add Comment threading/child/sub-comments.

My screenshot below show an example on the 5 record down from the top right is an indented comment that appears to be a sub-comment/child of the comment record above it (number 4).

click to view full size image

Now if you view the DOM HTML structure you will see a child comment is no different than the parent comment in the flow of the HTML. Meaning the child comment is not a child node in the HTML. Instead a child comment is below the parent comment and simply has a CSS class to say it is a child comment which applies a margin-left CSS value and a smaller width.

---

MySQL Database Table

--
-- Table structure for table `updates_stream`
--

CREATE TABLE IF NOT EXISTS `updates_stream` (
`id` int(18) NOT NULL,
  `event_type` varchar(10) DEFAULT 'comment',
  `comment_id` int(36) DEFAULT NULL,
  `comment_parent_id` int(36) DEFAULT '0',
  `record_id` char(36) DEFAULT NULL,
  `modified_user_id` int(36) DEFAULT NULL,
  `record_name` varchar(255) DEFAULT NULL,
  `date_entered` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1092 DEFAULT CHARSET=utf8;

SQL Query that I attempted to use to get Child comments under parnet comments (undesired results)

SELECT * FROM updates_stream AS parent
LEFT JOIN updates_stream AS child 
ON child.comment_parent_id = parent.id
WHERE parent.comment_parent_id = '0'
ORDER BY parent.id, child.id;

---

On to my question now...

How can I query MySQL to get all records like normal but now I need to put all child comments under the parent comments.

My MYSQL code above is set up on a SQLFiddle demo to test with here: http://sqlfiddle.com/#!9/08396/4

What I need:

Based on my requirements, the output of my demo which has 6 records should be in this ID order:

-- ID=1
-- ID=2
------- ID=3: child comment ID 3 has a Parent ID comment of 2
------- ID=5: child comment ID 5 has a Parent ID comment of 2
-- ID=4
-- ID=6

What I Got (Broken!):

Instead the result on the demo is like below... Only 5 rows instead of all 6. COmment with ID:2 is listed 2 times in a row which is somewhat correct almost meaning that the 1 comment that has child comments is ID2 which has 2 child comments. Also if you scroll way over to the right side of the page on the rows for ID2 you will actually see ID 3 and 5 to the right side but in the same column!:

-- ID=1
-- ID=2  ==>  ID3
-- ID=2  ==>  ID5
-- ID=4
-- ID=6

Answer 1

I don't know if what you are wanting can be done is SQL. I have done something similar with nested menus in php though and it should work here.

First, a slight change to your query:

SELECT 
    * 
FROM 
    updates_stream
ORDER BY comment_parent_id;

For this to work, we will also need to define a class for your comments:

class Comment{
    public $id;
    public $comment = '';
    ....
    //Do not need any reference to the parent.
    //Used to tell how nested the comment is (Child of child, etc.)
    public $nest_lvl = 0;
    public $children = array();

    public function generateHtml(){...}
}

So what we want to do is loop through our result set, create an instance of the class for every comment. We will have two arrays, one for setting up the format, and another for easy access to parent comments:

$parentComments = array();
$nestedComments = array();

while($row = mysqli_fetch_assoc($result)){
    $comment = new Comment();
    //Set comment values;

    if($row['parent_id'] != 0){
        //Add this comment to the parent comment children array
        $comment->nest_lvl = $parentComments[$row['parent_id']]->nest_lvl + 1;
        $parentComments[$row['parent_id']]->children[] = $comment;
    } else {
        //Add this comment to the nested array. Only comments that are not a child
        //comment should be added to the nested array, otherwise they will display twice.
        $nestedComments [] = $comment;
    }

    //Add this comment to the parent array
    $parentComments[$comment->id] = $comment;
 }

 unset($parentComments);
 mysqli_free_result($result);

Since we are using classes, everything is done by reference. Therefore, when we alter an instance of a class in $parentComments, the change will also reflect in the same instance of the class in $nestedComments.

Back to the function in the Comment class. This function will generate the the HTML needed for the parent comment AND all of its children (recursively). So the implimentation of this function would look something like this:

public function generateHtml(){
    //Generete the html for the current comment.
    //If the children array is not empty, call generateHtml on each of them.
    foreach($this->children as $child){
        $child->generateHtml();
    }
}

You can optionally choose to pass the nest lvl into the function instead of having it in the class.

Finally, to display everything, just loop through the $nestedComments, calling generateHtml() on each of them:

foreach($nestedComments as $comment){
    $comment->generateHtml();
}

While it is not all in SQL, it will get the job done and is a lot easier to do then in SQL.