Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts

    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
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    Send an email to everyone who has commented on the thread except for the person below. All ID's have to be unique, no duplicates.


    Code:
    commenter_id (id of person who is commenting)


    ----
    Last edited by Len Whistler; 08-24-2012 at 03:46 AM.
    Leonard Whistler

  • #3
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Yes, this is what I am trying to do (not manually though!)... any suggestions?

  • #4
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    MySQL can return unique records or you can get PHP to sort through the results, I think it's usually better to get MySQL to do most of the work.


    PHP Code:
    <?php

    //0002 has three posts, 0764 has two posts.
    $thread_posts_ids = array("0002","0024","0014","0002","0764","8888","0764","4321","0002","7632");
    $thread_posts_unique_ids array_unique($thread_posts_ids);

    foreach (
    $thread_posts_unique_ids as $value){
    echo 
    "Send email to: $value<br>";
    }
    ?>
    Output
    Code:
    Send email to: 0002
    Send email to: 0024
    Send email to: 0014
    Send email to: 0764
    Send email to: 8888
    Send email to: 4321
    Send email to: 7632
    Last edited by Len Whistler; 08-24-2012 at 04:06 AM.
    Leonard Whistler

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Code:
    SELECT DISTINCT commenter_id FROM comments WHERE post_id=$post_id
    Remember that you don't need and shouldn't have quotes around a number, if its being compared to a numeric type.


  •  

    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
    •