Solved

Using PHP and MySQL to do some dynamic looping

Posted on 2004-10-11
17
251 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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
 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help to convert powershell script into a gui 9 195
Turning python script into an applet 12 115
printf performancy 11 67
asp Google Map 2 23
I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
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…
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…

792 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