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.

[Weedpacket]

First, use "SELECT DISTINCT" to eliminate duplicates while selecting.

Second, when you get around to mailing, remember the note on the [man]mail[/man] page that starts "It is worth noting that the mail() function is not suitable for larger volumes of email in a loop."

Third, making queries in a loop is also very bad for performance, and means you probably want to join two tables together (something like "Select members.email from members, comments where members.id=comments.commenter_id and comments.post_id=$post_id").

Fourth, note all the warnings in the manual saying how the MySQL extension is outdated and should not be used for new projects.

[juniper747]

I am not so sure that works well.... or I just do not know how exactly to call the mail function in this case.

As far as using mail() and/or the foreach loop, I am only sending the email to max 8 emails at a time, so I am not too worried about performance here.

If possible could you show me exactly how meant it could be done with either the foreach or the mysql JOIN...?