CODEWITHSUNDEEP
phpmysqljoin
jon
jon

Reputation: 1581

How to get comments and replies?

I am trying to make a comments and replies to comments for media posted by users. My comments table is structured like:

commentId : parentCommentId : mediaId : userId : comment

I want to select the most recent 10 original comments and their replies to a mediaId.

To do this I am running 2 sql statements.

SELECT commentId FROM comments
WHERE mediaId='3' AND parentCommentId='0'
LIMIT 10;

(This gets the commentIds of the most recent original 10 posts. I then use these commentIds in the following)...

SELECT c.*,u.* FROM comments AS c
JOIN users AS u on u.userId=c.userId 
WHERE parentCommentId IN --( *****commentIds from previous query***** );

Is there a better way to do this? Perhaphs using a JOIN?

Upvotes: 0

Views: 189

Answers (2)

Ranjit Singh
Ranjit Singh

Reputation: 3735

This will be useful for your problem 

    SELECT c.*,u.* FROM comments AS c
    INNER JOIN users AS u on u.userId=c.userId 
    INNER JOIN comments pc on pc.commentId = c.CommentId
    WHERE pc.mediaId='3' AND pc.parentCommentId='0'
    LIMIT 10;

Upvotes: 0

k102
k102

Reputation: 8079

What if you make it this way:

    SELECT c.*,u.* FROM comments c
    JOIN users u on u.userId=c.userId 
    JOIN comments p_c on p_c.commentId = c.parentCommentId
    WHERE p_c.mediaId='3' AND p_c.parentCommentId='0'
    LIMIT 10;

Upvotes: 1

Related Questions