Solved

Using PHP and MySQL to do some dynamic looping

Posted on 2004-10-11
17
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this post we will learn different types of Android Layout and some basics of an Android App.
Simple Linear Regression
Six Sigma Control Plans

630 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