Solved

Using PHP and MySQL to do some dynamic looping

Posted on 2004-10-11
17
246 Views
Last Modified: 2010-04-17
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
Comment
Question by:christopherutley
  • 6
  • 6
  • 3
  • +1
17 Comments
 
LVL 5

Expert Comment

by:rsriprac
ID: 12277772
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
 

Expert Comment

by:drew1978
ID: 12277830
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
 
LVL 5

Expert Comment

by:rsriprac
ID: 12277833
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
 

Author Comment

by:christopherutley
ID: 12277904
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
 
LVL 11

Expert Comment

by:pratap_r
ID: 12278071
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
 
LVL 5

Expert Comment

by:rsriprac
ID: 12278083
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
 
LVL 11

Expert Comment

by:pratap_r
ID: 12278090
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
 
LVL 5

Expert Comment

by:rsriprac
ID: 12278097
my suggestion implies only ONE query.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

Expert Comment

by:pratap_r
ID: 12278122
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
 
LVL 5

Expert Comment

by:rsriprac
ID: 12278129
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
 
LVL 5

Expert Comment

by:rsriprac
ID: 12278146
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
 

Expert Comment

by:drew1978
ID: 12278216
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
 
LVL 11

Expert Comment

by:pratap_r
ID: 12278236
heh.. thanks guys :-)
0
 

Expert Comment

by:drew1978
ID: 12278284
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
 
LVL 11

Accepted Solution

by:
pratap_r earned 500 total points
ID: 12278348
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
 

Author Comment

by:christopherutley
ID: 12287592
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
 
LVL 11

Expert Comment

by:pratap_r
ID: 12295716
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now