Page 1 of 1

Problems with foreach() or while()

Posted: Tue Sep 05, 2006 5:21 pm
by acefactor99
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi everyone,

It seems I've bit off a little more than I can chew and taken on a seemingly simple project that ended up being quite complicated for some reason...

My plan is to send out a sequence of emails to all the registered users of my website, probably a sequence of about 5 emails (but this may grow as time goes on). So on the first day after signing up, you receive message 1, the second day you get message 2 etc... After getting the last email in the sequence, the users don't receive any more emails, at least until I write more.

Currently I have a table in my database that contains all my user's email addresses, called 'email_addresses'. One column stores their email address, another column, called 'email_count' stores which email in the sequence the user will receive next.

I have a second table called 'email_campaign' that holds the actual emails I want to send out. The column 'email_id' corresponds to 'email_count', so that everyone who has an email_count of 1 will receive the email with email_id of 1, email_count of 2 will receive the email with email_id of 2 etc...

If I have a sequence of 5 emails to send out, and someone has already received all 5, then ideally their email_count would be halted at 5. Worded differently, only those who receive emails would have their email_count updated.

Since I'm quite new at this, I've been fumbling around all day trying to make a script that will do what I want it to do. This is what I have so far:

Code: Select all

require_once("Connections/dbconnect.php");

$address_sql = "SELECT email_address, email_count, email_body FROM email_addresses, email_campaign WHERE email_addresses.email_count = email_campaign.email_id";
$address_result = mysql_query($address_sql,$conn) or die(mysql_error());

while($row = mysql_fetch_array($address_result)) {
$address = $row['email_address'];
$message = $row['email_body'];
$subject = "Test";
$headers = "MIME-Version: 1.0\r\n";
$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
$headers .= "From: test@test.com";
mail($address,$subject,$message,$headers) or die("Could not send email");
}

//Add 1 to email_count for every address
$update_sql = "UPDATE email_addresses SET email_count = email_count +1";
mysql_query($update_sql);
The biggest problem is with the last part, where it updates the database. I'd like it to only update those rows to which I actually sent emails to, not every row in the table. Originally I had it within the while loop, but this caused it to add 5 to email_count (because I had 5 test email addresses in my database).

I'm really stumped about how to make it only add 1 to the addresses I sent emails to. Any suggestions, or pointers in the right direction would be much appreciated!


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Sep 05, 2006 6:10 pm
by Ambush Commander
Use the SQL clause WHERE.

Posted: Wed Sep 06, 2006 10:22 am
by acefactor99
Ah yes of course! Thank you very much for your help, I really appreciate it.