CODEWITHSUNDEEP
phpmysqljoincomments
hobbywebsite
hobbywebsite

Reputation: 143

PHP query join to get comment replies

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);

Upvotes: 0

Views: 282

Answers (2)

chrislondon
chrislondon

Reputation: 12031

A couple MySQL things:

  1. 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

  2. You don't need to do users_info.user_name AS user_name. That's redundant.

  3. 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!

Upvotes: 1

Shaine Eugene Mednikov
Shaine Eugene Mednikov

Reputation: 395

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}'

Upvotes: 0

Related Questions