1

I am stuck into a very annoying problem. I have simple Post and Comments section on my website just like a blog i-e, two tables "posts" and "comments":

posts -> idpost, title, text, date comment -> idcomment, idpost_cmt, email, message, idcomment_cmt

Very simple. idpost_cmt -> Foreign Key to posts table

The last field in comment table i-e, "idcomment_cmt" will be used if someone sends comment on an already existing comment:

Post

|_ Comment 1

|_ Comment 2

|_ Comment 3

|_ Comment 4

__|_ Comment 4.1

__|_ Comment 4.2

|_ Comment 5 . . . so on ...

Now what I exactly want is to query all posts and comments in single mysql query. What I am doing now is that first I get all posts and then loop through each post. In each cycle, I query comments using the current post ID. And then loop through comments and on each comment cycle, query sub-comments using current CommentID.

As you can see that this way, a single page request might need 500 or more sql queries which is too much for database server. If anyone could help me how to accomplish this, I would really really appreciate.

Thanks a lot...

D3 K
  • 682
  • 1
  • 12
  • 19
  • Read this about Hierarchical Data in Relational Databases: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database . You are using the **Adjacency List** model. – ypercubeᵀᴹ Mar 16 '11 at 08:08

1 Answers1

0

You should run two queries.

  1. to get all Posts
  2. to get all comments for all posts including sub-comments

you can than put comments to the groups by using the idpost_cmt

something like:

SELECT idpost, 
       title, 
       TEXT, 
       DATE 
FROM   posts 
ORDER  BY DATE DESC 
LIMIT  30

2nd query could be something like:

SELECT idcomment, 
       idpost_cmt, 
       email, 
       message, 
       idcomment_cmt 
FROM   comments 
       JOIN posts 
         ON posts.idpost = comments.idpost_cmt 
WHERE  posts.idpost IN ( 1, 2, 3, 4 ) 
ORDER  BY idpost_cmt, 
          idcomment_cmt, 
          idcomment DESC
Pentium10
  • 204,586
  • 122
  • 423
  • 502
  • Thank you so much ... I am going to try this. – D3 K Mar 17 '11 at 02:09
  • You need to learn how to accept answers in this forum. See on the left of the answer below the voting a check(tick) you can mark. Please do so. – Pentium10 Mar 28 '11 at 08:38