MySQL query for sorting comments and their nested replies

I have read over 10 related posts here and elsewhere and still can't figure this one out, being rather new to php and MySQL:

I am using WordPress and trying to implement nested comments within an old theme. My table is all set (let's call it 'comments') and has the following fields:

comment_ID | comment_date | comment_parent, etc.

comment_parent remains equal to 0 in top level comments; it is equal to the comment_ID of the comment replied to in nested replies.

The original MySQL query looks like this:

SELECT * FROM $wpdb->comments 
WHERE comment_post_ID = %d AND comment_approved = '1' 
ORDER BY comment_date ASC LIMIT %d"

Through the php that follows and outputs the comment list, comments are listed by date without respecting nested replies as such:

comment_ID | 开发者_如何学运维comment_date | comment_parent
100          Jan 01         0      (this is a top level comment)
104          Jan 03         0      (this is a top level comment)
106          Jan 04         100    (this is a reply to the first comment)
108          Jan 05         104    (this is a reply to the second comment)

Obviously, the sequence is broken since I am sorting by date. Comment 106 should appear right below comment 100, and comment 108 should be below comment 104.

I am trying not to change my php and I would like to do this with the MySQL query but can't get it right. I have tried using JOIN, GROUP BY, HAVING as suggested in similar questions, without any success. Is there a way for me to achieve the correct sorting right from the query and keep my php intact?

---

Well, I've finally figured it out, thanks partially to Nupul's comment above who said that: "You can first fetch all the top level comments in one go and then the nested comments (all) in the second round and populate your content accordingly.". I had indeed tried that option unsuccessfully, but this motivated me to persevere...

For the record and hoping that this can help someone in the future, here's how I've fixed it. Two separate MySQL queries fetching first the top level comments and second, the nested comment replies (these queries are formatted for WordPress.)

$comments = $wpdb->get_results($wpdb->prepare("
  SELECT * 
  FROM $wpdb->comments 
  WHERE comment_post_ID = %d AND comment_approved = '1' AND comment_parent = '0' 
  ORDER BY comment_date ASC 
  LIMIT %d
",etc,etc)); 

$replies = $wpdb->get_results($wpdb->prepare("
  SELECT * 
  FROM $wpdb->comments 
  WHERE comment_post_ID = %d AND comment_approved = '1' AND comment_parent <> '0' 
  ORDER BY comment_date ASC 
  LIMIT %d
",etc,etc));

Then I go into my main comment loop (FOREACH) looking for top level comments only. Within that loop, at the bottom, I jump into a nested IF loop looking for nested comments where

$reply->comment_parent == $comment->comment_ID (comparing my 2 MySQL queries)

and if true adjusting $comments to equal $replies so that my nested comments are echoed correctly by WordPress. I then return the value of $comments to its original and exit my nested comment loop back to the main FOREACH.

There are probably flaws in my reasoning or execution but it works like a charm! Of course this does not provide for additional nested levels and all nested comments are written one after the other even if they are replies to each other, but that's good enough for me!

For a demo, http://www.vincentmounier.com/blog2/ and click on any of the "Show Comments" links at the the bottom of posts. Nupul, thanks again for the motivation!

---

You may be better off changing your table design. One of the better ways to implement nested comments is with Modified Preorder Tree Traversal (MPTT)

This is an article about an MPTT menu, but you should be able to learn how to implement a comment system using a similar scheme.