www.webdeveloper.com
Results 1 to 5 of 5

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

  1. #1
    Join Date
    Apr 2012
    Posts
    21

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

    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:
    PHP Code:
    $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.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,634
    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):
    Code:
    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
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Apr 2012
    Posts
    21
    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.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,634
    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:
    PHP Code:
    /*
      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();

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Apr 2012
    Posts
    21
    Thank you very much!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles