PHP query join to get comment replies

Question

I know that there are other posts out there like this, I've been using them. I've got three tables:

users_info
  user_id | user_name

blog_comments
  comment_id | comment

blog_reply
  user_id | comment_id | reply | reply_date

I'm trying to join the tables to get user_name from users_info and to return blog_reply by blog_comments.comment_id

I've been using: mysql structure for comments and comment replies

I'm stuck on the table joins, any help is much appreciated.

    $get_replies = ("SELECT
                      blog_reply.user_id,
                      blog_reply.comment_id,
                      blog_reply.reply,
                      blog_reply.reply_date,
                      users_info.user_name AS user_name
                    FROM blog_reply
                    JOIN users_info ON blog_reply.user_id = users_info.user_id
                    LEFT JOIN blog_comments ON blog_reply.comment_id = blog_reply.comment_id
                    JOIN users_info ON blog_reply.user_id = users_info.user_id
                    WHERE blog_comments.comment_id = '{$comment_id}'
                    ORDER BY blog_reply.reply_date DESC");

    $reply = mysql_query($get_replies);

p.s.w.g · Accepted Answer · 2013-03-22 21:25:23Z

1

A couple MySQL things:

If you don't need the table don't join it. You don't need any data out of the blog_comments table so you shouldn't include it in your query. Since the blog_reply table has the comment_id you can just use that table
You don't need to do users_info.user_name AS user_name. That's redundant.
You can add the where clause to the join.

Here is how I would write the MySQL statement:

SELECT
    blog_reply.user_id,
    blog_reply.comment_id,
    blog_reply.reply,
    blog_reply.reply_date,
    users_info.user_name
FROM 
    blog_reply
JOIN 
    users_info 
ON 
    blog_reply.user_id = users_info.user_id AND
    blog_reply.comment_id = '{$comment_id}'
ORDER BY 
    blog_reply.reply_date DESC

I hope that helps!

edited Mar 22, 2013 at 21:25

p.s.w.g

150k31 gold badges307 silver badges339 bronze badges

answered Mar 22, 2013 at 21:06

chrislondon

12.6k5 gold badges29 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hobbywebsite Over a year ago

yes thanks that helps. Yeah you're right about that not needed table, I think it's left over from trying to get comments and replies at the same time.

Shaine Eugene Mednikov · Accepted Answer · 2013-03-22 21:06:40Z

0

This should work:

FROM blog_reply
JOIN users_info ON blog_reply.user_id = users_info.user_id
JOIN blog_comments ON blog_reply.comment_id = blog_comments.comment_id
WHERE blog_comments.comment_id = '{$comment_id}'

answered Mar 22, 2013 at 21:06

Shaine Eugene Mednikov

3953 silver badges12 bronze badges

Collectives™ on Stack Overflow

PHP query join to get comment replies

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related