SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Shaw AFB
    Posts
    282
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Emailing to multiple groups

    I have an Address book:

    Table (addressbook)
    id (unique, auto-incrimenting)
    email (recipients e-mail address)

    and I have Groups - a Group is a selection of emails in your address book)
    Table (groups)
    id (unique, auto-incrementing)
    emails (currently set as a string - comma separated addressbook id's, for example: 4,6,8,9. Each of those ID's corresponds to addressbook.id )

    So, now when we're composing an e-mail, and we select two (or more) groups - we'll say group 112 and group 115 (these group id's are unique)

    Group 112 has these email ID's associated with it: 16,35,49,124
    Group 115 has these email ID's associated with it: 86,121,123,124

    What is the best method of querying the database, and then returning all of those email ID's as an array of actual e-mail addresses?

    Let me know if something is unclear and I will try my best to clarify
    ~ Nate L ~

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Location
    Austria
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would suggest:

    table book: id (unique; primary key) | email
    table group: id (not unique) | book_id

    example:

    book:
    id | email
    1 | a@b.de
    2 | c@d.de
    3 | e@f.de

    group:
    id | book_id
    1 | 1
    1 | 2
    2 | 3

    select group 1:
    SELECT email
    FROM book, group
    WHERE book.id=group.book_id
    AND group.id=1;

    result:
    email
    a@b.de
    c@d.de

    and finally PHP-code:

    $arrayResult=array();
    in a loop: array_push($arrayResult,-a-email-adress-of-the-result);
    $allEMailsOfAGroup=implode(',',$arrayResult);

  3. #3
    SitePoint Evangelist simshaun's Avatar
    Join Date
    Apr 2008
    Location
    North Carolina
    Posts
    438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Taking from and making blubb's example more fit to your requirements:
    Code php:
    /**
    * Table addressbook
    * -------------------
    * id | email
    * -------------------
    * 1  | a@b.de
    * 2  | c@d.de
    * 3  | e@f.de
    */
     
    /**
    * Table group_address_map
    * -----------------------
    * group_id | email_id
    * -----------------------
    * 1        | 1
    * 1        | 2
    * 2        | 1
    * 2        | 3
    */
     
    $recipients = array();
    $result = mysql_query("
    	SELECT
    		DISTINCT addressbook.email
    	FROM
    		group_address_map
    	INNER JOIN
    		addressbook
    			ON
    		addressbook.id = group_address_map.email_id
    	WHERE
    		group_address_map.group_id = 1
    			OR
    		group_address_map.group_id = 2
    ") or die(mysql_error());
    if (mysql_num_rows($result) > 0) {
    	while ($row = mysql_fetch_assoc($result)) {
    		$recipients[] = $row['email'];
    	}
    }
     
    // You can now either implode $recipients or loop through it.
    The reason I did this is because you may have an email that is a member of more than 1 group. You don't want more than one email sent to any one person. Its untested, but I don't think you should have problems with the SQL.


Bookmarks

Posting Permissions

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