mysql/php: show posts and for each post all comments

Question

I know this question has been asked multiple times (however, I could still not find a solution):

• PHP MYSQL showing posts with comments
• mysql query - blog posts and comments with limit
• mysql structure for posts and comments ...

Basic question: having tables posts, comments, user... can you with one single select statement select and show all posts and all comments (with comment.user, comment.text, comment.timestamp)? How would such a select statement look like? If not, what is the easiest solution?

I also tried to JOIN the comments table with the posts table and use GROUP BY, but I got either only one comment in each row or each comment but also those posts multiple times!?

I tried the solution of the first link (nested mysql_query and then fetch) as well as the second link (with arrays). However, the first caused a bunch of errors (the syntax in that post seems to be not correct and I could not figure out how to solve it) and in the second I had problems with the arrays.

My query looks like this till now:

SELECT p.id, p.title, p.text, u.username, c.country_name, (SELECT SUM(vote_type) FROM votes v WHERE v.post_id = p.id) AS sum_vote_type FROM posts p LEFT JOIN user u ON ( p.user_id = u.id ) LEFT JOIN countries c ON ( c.country_id = u.country_id ) ORDER BY $orderby DESC

I was wondering if this issue was not very common, having posts and comments to show...?

Thank you for every help in advance!

Answer 1

Not knowing your database structure, it should look something like this. Note that you should replace the * characters with more explicit lists of columns you actually need.

SELECT p.*, c.*, u.* FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
LEFT JOIN users u ON u.id = p.author_id

Note that if you're just trying to get counts, sums and things like that it's a good idea to cache some of that information. For instance, you may want to cache the comment count in the post table instead of counting them every query. Only count and update the comment count when adding/removing a comment.

EDIT: Realized that you also wanted to attach user data to each comment. You can JOIN the same table more than once but it gets ugly. This could turn into a really expensive query. I also am including an example of how to alias columns so it's less confusing:

SELECT p.*, c.*, u.name as post_author, u2.name as comment_author FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
LEFT JOIN users u ON u.id = p.author_id
LEFT JOIN users u2 ON u2.id = c.author_id