Geek asks and answers

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

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

The main question: the presence of message tables , comments , user ... can you select one selection operator and show all messages and all comments (with commentary commenter, comment.text, comment.timestamp)? What would such a choice look like? If not, what is the easiest solution?

I also tried a JOINtable commentswith a table postsand use it GROUP BY, but I received either only one comment on each line, or each comment, but also these messages several times !?

I tried solving the first link (nested mysql_query, and then fetching), as well as the second link (with arrays). However, the first of them caused a bunch of errors (the syntax in this message seems to be wrong, and I could not figure out how to solve it), and in the second I had problems with arrays.

My query looks like this:

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 problem is too common when there are posts and comments ...?

Thanks for any help in advance!

+3
source share
1 answer

, . , * , .

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

, , .., . , , . / .

EDIT: , . , . . , , :

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
+7

More articles:


All Articles