I have a series of email newsletters. Some of them are to be sent to only one customer list while others are to be sent to multiple customer lists. (The customers have paid to receive these newsletters.) Some customers subscribe to more than one newsletter list.
I store the newsletters and the customer lists in a MySQL database on a Linux server. With each newsletter, I have a field that holds the ids of all the lists to which that newsletter should be sent. The idlist field might be something like: 1||3||35||6
When it is time to send the newsletter, I retrieve the list ids and split them into an array.
arID[0] = 1
arID[1] = 3
arID[2] = 35
arID[3] = 6
I need to query the database for the email addresses subscribed to each of the list ids. I originally set it up as a foreach loop.
$recipient = array();
$ctr = 0;
foreach($arID as $i){
$getlist = "SELECT emailaddress FROM emaillists WHERE emaillisttypeid = ".$i;
$getrec = mysql_db_query($dbname,$getlist);
while ($qryemails = mysql_fetch_array($getemailrec)){
$recipient[$ctr] = trim($qryemails[emailaddress]);
$recct++;
}
}
However, that means multiple queries and since one newsletter can potentially be sent to up to 23 lists, that's not efficient.
How do I write a single select query to get the email addresses that includes all the list ids? Here are the three requirements.
The number of lists for each newsletter is not constant. One newsletter may go to 2 lists while another might go to 5 lists.
The email addresses must be assigned to an array because I mail merge the newsletters so that each customer gets a personalized newsletter.
Because customers can subscribe to multiple lists, there will almost always be duplication of email addresses in the list of recipients. There must be some kind of grouping so that there are no duplicates in the resulting array of email addresses.
