4

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?

Improve this question
7
  • Greetings! Welcome to SO! Even if you do create such a query won't you get repeated content? All the columns up to the 'nested comment' column will contain the same data. You'll then have to write code to 'ignore' the previous columns'. You may want to try looking at the 'dependent mapping' pattern by Fowler - it won't by in MySQL, and may need to be handled in code. Commented May 3, 2011 at 19:01
  • 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. Or as per the above content write some amount of non-trivial code to get it right. AFAIK it may not be possible to have a query without duplication of content. I'd like to know the solution to this too and that's how I've dealt with it so far! Commented May 3, 2011 at 19:04
  • 1
    Try this: stackoverflow.com/questions/5280569/… Commented May 3, 2011 at 19:05
  • I had wondered about that duplicate content issue when using the JOIN but since I couldn't get it to work in the first place, I never got to find out. I had hoped that I could write a query that would be twisted enough to simply sort my rows without repetition... I'll look into dependent mapping, thanks! Commented May 3, 2011 at 19:06
  • This: link is the closest to a solution I found in here but couldn't get it to work, giving me syntax errors Commented May 3, 2011 at 19:08

2 Answers 2

Reset to default
4

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Glad to know it worked. Now that it did...look up dependent mapping and compare your solution with it. It'll match like a charm :)
Based on my assumption that a new concept's implementation headache level rises proportionally with the difficulty of finding information about it, dependent mapping might be the way to go but boy, would it have hurt! I just looked around the web for a while and gave up; either too confusing or too advanced for me. But thanks for the advice! :)
I know this is old, but I'd like for anyone viewing this to know that simply running two separate queries is not a very good answer. At the very least you can do a UNION rather than two separate queries. Performance and efficiency are king, and running multiple queries is a very bad solution to most problems. In this example, you don't even need to do two queries. This is an absolute waste.
3

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.

Improve this answer

2 Comments

Yes, I had read this somewhere else, but decided against it, not wanting to rewrite my WordPress database - that old theme is not worth it. I'll change themes before messing with the database...
To be more specific, Wordpress already supports nested comments, but I am adding inline comment display to a theme and have to bypass the regular comment display system and do my MySQL queries live...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.