CODEWITHSUNDEEP
phpmysqlhierarchical-data
Ryan
Ryan

Reputation: 133

Query for post and comments all in one?

I've been reading about hierarchical database structure and am wondering how I can apply it to my current code. At the moment, I am querying for all posts, then in the loop for that query, I do another query for all comments for that post. So an extra query for every post; that seems inefficient to me. My table structure:

posts.id, posts.userid, posts.text

comments.id,comments.postid,comments.userid,comments.text

I saw some posts where people mention doing all this in one query, where the returned query uses sql's CONCAT() to add spaces to indent comments. Then in appcode you'd check for spaces and display accordingly. Any ideas on a query for my data style?

Upvotes: 0

Views: 129

Answers (1)

galchen
galchen

Reputation: 5290

you can concat any fields you like, but it will be messy to concat the comments.text rows

what i normally do is put posts and comments in the same table: posts (postid, userid, text, parent_postid)

you know if it's a post when parent_postid == 0, and you can easily fetch all posts/comments for the same "post" i think is a more common way in most systems

Upvotes: 1

Related Questions