Page 1 of 1

Logic question

Posted: Sun Jul 02, 2006 8:36 am
by t0ta11ed
Greetings,

I have a small problem trying to figure out the logic for an app that will be retrieving records from MySQL within the last 30 days and then sending an email for each one based on the info in each row. I hope this makes some sense:

Code: Select all

$query = "select id,domain,renewal_date,contact from domains where renewal_date >= CURDATE() AND renewal_date <= ADDDATE(CURDATE(),INTERVAL 30 DAY) ORDER by 'id' DESC";
$result = mysql_query($query) or die ('Query failed: ' . mysql_error());

while ($row = mysql_fetch_row($result)) {


}
I need to be able to send a single email for each domain, renewal date, and contact in the results. If the mail code is placed in the while, it doesn't work right because it wants to send an email to every contact for every record. Don't say to use a foreach because that just duplicates the problem. I'm not a PHP newbie and have been racking my brains trying to figure this out both in PHP and Perl. Any pointers would be greatly appreciated. If you need clarification, let me know.

Posted: Sun Jul 02, 2006 10:05 am
by RobertGonzalez
First of all, does your query work? If you are looking for records from the most recent 30 days, you can always create that variable in PHP, then send that variable to query as the where clause. Then read the enitire result into an array, and for loop that array sending mail for each item. A member here created a mailing application called SwiftMailer. There is alink to it in my signature. You may want to take a look at that because it can handle multiple addressees without sending the same mail to all of them at the same time. Anyway, try this logic and see if it helps.

Code: Select all

<?php
$checkdate = date("Y-m-d", strtotime('-30 days')); // returns a YYYY-MM-DD string from 30 days ago

$sql = "SELECT id, domain, renewal_date, contact 
        FROM domains 
        WHERE renewal_date >= '$checkdate' 
        ORDER by 'id' DESC";

if (!$result = mysql_query($sql))
{
    die('There was a problem with the query selecting data since ' . $checkdate . ': ' . mysql_error());
}

$rs = array();
while ($row = mysql_fetch_array($result))
{
    $rs[] = $row;
}

$rscount = count($rs);
for ($i = 0; $i = $rscount; $i++)
{
    // Send you mails to $rs[$i]['contact'] or what ever the database field name is
}
?>

Posted: Sun Jul 02, 2006 10:11 am
by t0ta11ed
Yes, the query works. I'm checking for any error. I've tried something similar to this with no success but I'll give it a whirl. Thanks.

Re: Logic question

Posted: Sun Jul 02, 2006 10:17 am
by RobertGonzalez
t0ta11ed wrote:I need to be able to send a single email for each domain, renewal date, and contact in the results. If the mail code is placed in the while, it doesn't work right because it wants to send an email to every contact for every record.
This actually doesn't sound right. In the while loop, you tell it which email address to send it to. What should be happening is each iteration of the while loop should be on a different record. While in that record, $row should be containing the data for that row in the database. If in that loop you are calling mail() to the email address in $row, it should send to that email address only.

You may want to use mysql_fetch_array instead of mysql_fetch_row. Then, as a test, echo out each iteration of what you have now to see what is being output. That might help fi it faster than rewriting it.

Posted: Sun Jul 02, 2006 10:41 am
by t0ta11ed
Got it all partially working except for one thing...each mail goes out separately without a problem, but the funky part is that using two records with two different addresses...the second email arrives with BOTH addresses in it and it doesn't appear to go to the second address's inbox. This is totally weird. I need to have just the single address for each message go to each mailbox. It's really odd since it all works as it's intended except this part. If anyone has any ideas, I'm all ears...The current code:

Code: Select all

$query = "select customer,domain,renewal_date,contact from domains where renewal_date >= CURDATE() AND renewal_date <= ADDDATE(CURDATE(),INTERVAL 30 DAY) ORDER by 'id' DESC";

$result = mysql_query($query) or die ('Query failed: ' . mysql_error());

while ($row = mysql_fetch_array($result)) {

$mail->Subject  =  "THIS IS A TEST - Upcoming domain renewal for $row[1]";
$mail->Body     =  "Domain $row[1] belonging to $row[0] is up for renewal on $row[2].";
$mail->AddAddress("$row[3]");
$mail->Send();

}

Posted: Sun Jul 02, 2006 4:35 pm
by RobertGonzalez
I think you can take the qotes off the $row[3] var when passing it to the addAddress method. Otherwise, you may want to consider unset()ing the $row array at the end of the loop so when it starts again, it will be like filling an empty array.

Posted: Sun Jul 02, 2006 4:57 pm
by t0ta11ed
Works fine without the quotes. I don't see how either suggestion will fix the current problem though.

Posted: Sun Jul 02, 2006 7:00 pm
by Robert Plank
Call the constructor at the beginning of the while loop. I have a feeling "AddAddress" adds that email address IN ADDITION to the ones already there, so it's doing this:

Send email #1 to user #1
Send email #2 to user #1 and #2
Send email #3 to user #1, #2, and #3

Code: Select all

while ($row = mysql_fetch_array($result)) { 
$mail = new Mailer();
$mail->Subject  =  "THIS IS A TEST - Upcoming domain renewal for $row[1]"; 
$mail->Body     =  "Domain $row[1] belonging to $row[0] is up for renewal on $row[2]."; 
$mail->AddAddress("$row[3]"); 
$mail->Send(); 
}

Posted: Sun Jul 02, 2006 8:01 pm
by t0ta11ed
I had a feeling it was staring me right in the face. I hadn't thought of that yet. I tend to bounce things off others when I hit a problem just to help myself think LOL I'll test and let you know. Thanks!

Btw..

Code: Select all

or ($i = 0; $i = $rscount; $i++)
{
That's quite a loop. It would work so much better with <= :wink:

Posted: Mon Jul 03, 2006 12:45 am
by RobertGonzalez
Sorry dude. I was working on a computer that has a bum keyboard. I posted here once for an entire morning and managed to never enter a 'p' or a capital letter. Go figure. Thanks for spotting the issue. It was not what I intended to write.

Posted: Mon Jul 03, 2006 8:41 am
by t0ta11ed
Robert - You nailed it! It's working the way it should now with the constructor in the while loop.

Everah - No biggie on the loop, I figured it was a typo. =)