[RESOLVED] Emailing a list of commenters from mysql db, no duplicates

[juniper747]

I am trying to come up with a way to send a notification email to people associated with a story that was posted. The people to be emailed are:

-the owner of the post (unless the current commenter is the owner)
-everyone else who has ever commented on the post

Also, I need to be careful not to send duplicate emails for anyone who may have posted more than one comment on a given post.

My database comment table is set up with the columns:
comment_id (just a unique id for each comment ever)
post_id (unique id for the posted story)
commenter_id (id of person who is commenting)
commentee_id (id of person who owns the posted story)
comment (the comment itself)
comment_date (date of comment post)

My database members table is basically 2 columns:
id (unique member id)
email (members email)

So far I have:

$post_id = 25; // id of post
$commentee_id = 9; // id of post owner

$get_all_commenter_ids = mysql_query("SELECT commenter_id FROM comments WHERE post_id='$post_id");
while($row = mysql_fetch_array($get_all_commenter_ids)){ 
    $commenter_id = $row["commenter_id"];
}
foreach($commenter_id as $member_to_email) {
    if($commentee_id != $member_to_email) {
                 $get_commenter_email = mysql_query("SELECT email FROM members WHERE id='$member_to_email");
                 while($row = mysql_fetch_array($get_commenter_email )){ 
                     $commenter_email = $row["email"];
                 }
                 // I am not sure what goes next!
        }
} 

Please let me know if there is a more elegant way to do this.

[NogDog]

I think something like this would work (minus whatever typos I made and you replacing the ":post_id" place-holders with however you like to safely get parameters into your queries):

SELECT DISTINCT tmp.email FROM
(
  (
    SELECT DISTINCT email
    FROM members
    INNER JOIN comments ON comments.commenter_id = members.id
    WHERE comments.post_id = :post_id
  )
  UNION
  (
    SELECT DISTINCT email
    FROM members
    INNER JOIN comments ON comments.commentee_id members.id
    WHERE comments.post_id = :post_id
  )
) AS tmp

[juniper747]

Thank you, that seems like it would work... however, could you show me how the mail() function would look. Would I have to do some kind of concatenation to send it to each email or put it in a foreach loop or something? Thanks.

[NogDog]

I'd probably just put it in a loop. I'd also probably use PHPMailer instead of just mail(), if for no other reason than that I like to leave the details to it. You could then do something like:

/*
  Do your DB query, in this example result is in $result
*/
$mail = new PHPMailer();
/*
  Do everything to set up email except set the "To:" address and actually send it
*/
while($row = mysql_fetch_assoc($result)) {
  $mail->ClearAddresses();
  $mail->AddAddress($row['email']);
  $mail->Send();
} 

[juniper747]

Thank you very much!