MYSQL Select many to many

Question

I was working with two tables: comment, user

comment table had a recipient field holding a user id

Now we must change the comment/recipient to a many-many relationship as multiple users can receive the same comment, so I added a link table, comment_recipient

However, now I'm not sure how to select a list of recipients when selecting comments

AND

I'm not sure how to select comments based on a specific recipient

Here's what I have right now:

$sql = "SELECT c.*, [RECIPIENTS]
FROM comment AS c
JOIN user AS u2 ON c.sender = u2.id
WHERE c.type<=?";
if(isset($args['location'])) $sql .= " AND u2.location=?";
if(isset($args['userId'])) {
  if(isset($args['given'])) {
    $sql .= " AND c.sender=?";
  }else {
    $sql .= " AND c.recipient=?"; //need to see if in recipient list now?
  }
}

Edit:

Thanks to Barry's answer I've now got this:

$sql = "SELECT c.*
FROM comment_recipient AS cr
JOIN comment AS c ON c.id=cr.comment_id
JOIN user AS u2 ON c.sender = u2.id
WHERE c.type<=?";
if(isset($args['location'])) $sql .= " AND u2.location=?";
if(isset($args['userId'])) {
  if(isset($args['given'])) {
    $sql .= " AND c.sender=?";
  }else {
    $sql .= " AND cr.user_id=?";
  }
}
$sql .= " GROUP BY c.id";
$sql .= " ORDER BY c.date DESC";
$sql .= " LIMIT ?";
$sql .= " OFFSET ?";

I'll then use a second query to select recipients for each comment

Answer 1

Assuming you have the following tables:

• User ( ID, Name )

• Comment ( ID,Name )

• CommentRecipient ( User_ID, Comment_ID ) - where these are both foreign keys to the table above.

Is it not as simple as: To get recipients when you know the Comment query the linking table:

SELECT User_ID from CommentRecipient where Comment_ID= mycommentid;

and vice versa

SELECT Comment_ID from CommentRecipient where User_ID= myuserid;

Sorry if I have misunderstood the question.