• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Using PHP and MySQL to do some dynamic looping

I was wondering if somebody might have a solution for this problem, which I'm sure comes up often.

I have a table where "Reminders" are entered throughout the day.  Each Reminder has a mailTo address, that is completely arbitrary and not linked to anything in the database.  Once per day, I have to go to this table and summarize all the reminders for each UNIQUE email address - then send the summary to the that recipient.  

In other words, if bob@barker.com has 10 Reminders in the the Reminders table, I need to compile the bodies of all 10 reminders and mail them to bob@barker.com in a SINGLE email message.  Naturally, just looping over the entire table and sending 10 different messages to bob@barker.com is cake.  

I'm kind of stumped, and sort of embarrassed since this seems like it should be easy!  I've tried using things such as UNIQUE and GROUP to arrive at my solution, but nothing has come to me yet.
0
christopherutley
Asked:
christopherutley
  • 6
  • 6
  • 3
  • +1
1 Solution
 
rsripracCommented:
Hi,

Your query should be something like:

SELECT email FROM table_email
GROUP BY email;

This should give you the unique emails, then just loop the result of the query.

0
 
drew1978Commented:
I'm not quite sure that this is what you're looking for and if it works for you. It might depend on the size of the database and how the table is indexed:

I assume that the table is like the following:
table Reminder
 -- mailTo varchar(255),
 -- reminder text
 -- date..?

First, do a select and order them by the email adress. Loop trough the result using a piece of code like this:

$mailTo = false; // no email in the beginning
$reminderText = ""
while($row = mysql_fetch_object($result)){
  // the email has changed and this is not the first row from the resultset
  if($mailTo != $row->mailTo && $mailTo != false) {        
    // send email to $mailTo with the text in $reminderText
    $reminderText = "";
  }
  $reminderText .= "</br>" . $row->reminder; // add the reminder to the string of reminder to this email
  $mailTo = $row->mailTo;
}
0
 
rsripracCommented:
I guess if your 'reminder' table has columns of 'email' and 'reminder_message' then you do the query:

SELECT email, reminder_message FROM reminder
ORDER BY email;

This will put out all the reminders in order of email, then in PHP loop through the records and keep concatnating the reminder_message into a single string ($all_reminder) UNTIL you hit a different 'email' result, i.e. you have gotten all the reminder for the previous email.  Then you send out the $all_reminder varible, clear $all_reminder, and start the loop again on the next record.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
christopherutleyAuthor Commented:
That's kind of where I'm stuck ... I understand that the Reminders will be sorted by email address, but what would the actual code look like in PHP?  Since I don't know what email addresses exist or in what quantity, what does the PHP look like to build a summary of all the Reminder bodies for a unique email address?

I figure inside a loop I am building a variable by doing something like $reminderBody .= $reminderBody, but how would you code it to say "Oh here's a new address, so send the existing reminder body to the previous address, now reset the $reminderBody to empty and repeat until you are out of unique email addresses"?
0
 
pratap_rCommented:
select distinct email from table_email will work too

but you should consider using a single query rather than using multiple queries, assuming theres around 1000 users you will be firing 1001 sqls totally

you are better off using the logic like..

$result = mysql_query("select email,remindertext from reminders order by email");
$oldemail="";
$reminder="";
while ($rec = mysql_fetch_row($result))
{
   if($oldemail == $rec[0])$reminder.="\n".$rec[1];
   else
   {
     if($reminder != "")DispatchEmail($oldemail,$reminder . "\n\nGave a Good Day!");
     $reminder="Greetings, \n\nYour Reminder(s) for today:\n" . $rec[1];
     $oldemail=$rec[0];
   }
}
mysql_free_result($result);

hope i dint make any mistakes in the above code.. just wrote it here.. havent even checked it :-)

and you might want to add the date filter to the query ;-)

Enjoy
Pratap
0
 
rsripracCommented:
You keep a temp varible of the previous email, and if they are unequal, then it has changed and you are in the new section of emails.
0
 
pratap_rCommented:
oops.. sorry guys.. too much activity by the time i posted.. drew1978 sorry i was typing the msg when you posted your code..

Pratap
0
 
rsripracCommented:
my suggestion implies only ONE query.
0
 
pratap_rCommented:
rsriprac looks like we are both on the same track. christopherutley you might want to check my code.. it has the php logic.. what say rsriprac??
0
 
rsripracCommented:
You keep a temp varible of the previous email, and if they are unequal to the current email in the record, then it has changed and you are in the new section of emails.
0
 
rsripracCommented:
Yea I think we are talking about the same thing, I usually avoid code so people actually learn something, but then again I'm bad at explaining things. heh.  Yea, pratap's code does the job.

-Ram
0
 
drew1978Commented:
I agree... pratap's code looks good for this job. Mine has a small bug and misses the first reminder. :( It should not be '$reminderText="";' but '$reminderText=$row->reminder;'
0
 
pratap_rCommented:
heh.. thanks guys :-)
0
 
drew1978Commented:
One thing that we both missed will be the last email address...  None of our codes will send an email to the poor guy that has the last email. This should be covered in a special case after the loop in a similar way as inside the loop.

--Drew1978
0
 
pratap_rCommented:
yeah.. that proves why testing is a must :-)

heres a fix

$result = mysql_query("select email,remindertext from reminders order by email");
$oldemail="";
$reminder="";
$mailsent=true;
while ($rec = mysql_fetch_row($result))
{
   if($oldemail == $rec[0])$reminder.="\n".$rec[1];
   else
   {
     if($reminder != ""){DispatchEmail($oldemail,$reminder . "\n\nGave a Good Day!");$mailsent=True;}
     $reminder="Greetings, \n\nYour Reminder(s) for today:\n" . $rec[1];
     $oldemail=$rec[0];
     $mailsent=false;
   }
}
if($mailsent==false)DispatchEmail($oldemail,$reminder . "\n\nGave a Good Day!");
mysql_free_result($result);

Pratap
0
 
christopherutleyAuthor Commented:
pratap_r, here is my adaptation of your latest example.  There are 3 valid records in my DB, 2 reminders for one email address and 1 for another.  My version of your solution will compile two reminders and successfully send them to the address that has two reminders pending.  For some reason the other reminder never gets sent.  I think I understand this code, but I don't see where it's breaking.  Moreover, I'm not sure if the "bug" is in your example or my adaptation.


$result = mysql_query("select mail_to,subject,message from reminder WHERE delivered != '1' order by mail_to");
$oldemail="";
$reminder="";
$mailsent=true;
while ($rec = mysql_fetch_row($result))
{
if($oldemail == $rec[0])$reminder.="\n".$rec[1]." - ".$rec[2];
else
{
if($reminder != ""){
//echo $oldemail . " " . $reminder . " \n\nGave a Good Day!";
//echo $oldemail; exit;
$messageBody = $oldemail . $reminder . "\n\nEnd Reminders.\n\n";      
$headers = $headers."From: reminders@www.salesleader.net\r\n";      // Set the mail from: address
$headers = $headers."Cc: $cc\r\n";                  // Set the CC: addresses
//echo 'oe: ' . $oldemail;
mail($oldemail, "Reminders", $messageBody, $headers);      

$mailsent=True;
}
$reminder="Greetings, \n\nYour Reminder(s) for today:\n" . $rec[1]." - ".$rec[2];
$oldemail=$rec[0];
$mailsent=false;
}
}
if($mailsent==false) { //echo $oldemail . " " . $reminder . " \n\nGave a Good Day!";
$messageBody = $oldemail . $reminder . "\n\nEnd Reminders.\n\n";      
$headers = $headers."From: reminders@www.salesleader.net\r\n";      // Set the mail from: address
$headers = $headers."Cc: $cc\r\n";                  // Set the CC: addresses
mail($oldemail, "Reminders", $messageBody, $headers);      
}
mysql_free_result($result);
0
 
pratap_rCommented:
umm i dont see anything wrong with the code.. can you check the data being fetched by the query..? also a trace will be helpful.

Pratap
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now