Solved

Using PHP and MySQL to do some dynamic looping

Posted on 2004-10-11
17
247 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How Complex Is This Java Course ? 9 95
recursion example 16 112
Device same like our heart 12 79
simplest php form 3 64
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
A short article about problems I had with the new location API and permissions in Marshmallow
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 fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

864 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

20 Experts available now in Live!

Get 1:1 Help Now