mysql评论表如何查询,如何在MYSQL中发表评论回复查询？

I am having comment reply (only till one level) functionality. All comments can have as many as replies but no replies can have their further replies.

So my database table structure is like below

Id ParentId Comment

1 0 this is come sample comment text

2 0 this is come sample comment text

3 0 this is come sample comment text

4 1 this is come sample comment text

5 0 this is come sample comment text

6 3 this is come sample comment text

7 1 this is come sample comment text

In the above structures, commentid, 1 (has 2 replies) and 3 (1 reply) has replies. So to fetch the comments and their replies, one simple method is first I fetch all the comments having ParentId as 0 and then by running a while loop fetch all the replies of that particular commentId. But that seems to be running hundreds of queries if I'll have around 200 comments on a particular record.

So I want to make a query which will fetch Comments with their replies sequentially as following;

Id ParentId Comment

1 0 this is come sample comment text

4 1 this is come sample comment text

7 1 this is come sample comment text

2 0 this is come sample comment text

3 0 this is come sample comment text

6 3 this is come sample comment text

5 0 this is come sample comment text

I also have a comment date column in my comment table, if anyone wants to use this with comment query.

So finally I want to fetch all the comments and their replies by using one single mysql query. Please tell me how I can do that?

Thanks

解决方案

You can use an expression in an ORDER BY. Try this:

SELECT *

FROM comments

ORDER BY IF(ParentId = 0, Id, ParentId), Id

This will first sort by Id, if ParentId = 0, or by ParentId otherwise. The second sort criterion is the Id, to assure that replies are returned in order.