Logic question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
t0ta11ed
Forum Newbie
Posts: 10
Joined: Sun Jul 02, 2006 8:19 am

Logic question

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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
}
?>
t0ta11ed
Forum Newbie
Posts: 10
Joined: Sun Jul 02, 2006 8:19 am

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Logic question

Post 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.
t0ta11ed
Forum Newbie
Posts: 10
Joined: Sun Jul 02, 2006 8:19 am

Post 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();

}
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
t0ta11ed
Forum Newbie
Posts: 10
Joined: Sun Jul 02, 2006 8:19 am

Post by t0ta11ed »

Works fine without the quotes. I don't see how either suggestion will fix the current problem though.
Robert Plank
Forum Contributor
Posts: 110
Joined: Sun Dec 26, 2004 9:04 pm
Contact:

Post 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(); 
}
t0ta11ed
Forum Newbie
Posts: 10
Joined: Sun Jul 02, 2006 8:19 am

Post 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:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
t0ta11ed
Forum Newbie
Posts: 10
Joined: Sun Jul 02, 2006 8:19 am

Post 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. =)
Post Reply