Mass email with MySQL Values

[Call-911]

Hello All,

 

I have figured out how to get all the information from my member database into a table.

 

Now what I need to do is take every entry, and send an email to that member with the information.

 

For example, I have 150 users in my database, with columns for name, email address, and amount due.

 

I can't figure out how to send an email to every user automatically, saying "Hello <name>, you currently owe <amountdue>."

 

I can figure out how to send the same email to every user, but not to send specific values for each user in each email to the user.

 

Any help would be great!! Thanks!

[thomashw]

Can you figure out how to send ONE email using PHP if you had the name, email, and amount due?

 

If so, can you figure out how to query the database for ONE row and send that person an email with the amount due?

 

If so, all you need to do is use a while loop to do it for everyone.

 

If you post some code getting either the first or second questions to work, I'll help with the third.

[Call-911]

Here is my current coding:

 

<?php
// Start a session
session_start();


include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$id = $_POST['studentid'];
$query="SELECT * FROM members WHERE id='200472'";
$result=mysql_query($query);
$num=mysql_numrows($result); 
mysql_close();

$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$username=mysql_result($result,$i,"username");
$firstname=mysql_result($result,$i,"firstname");
$lastname=mysql_result($result,$i,"lastname");
$address=mysql_result($result,$i,"address");
$city=mysql_result($result,$i,"city");
$state=mysql_result($result,$i,"state");
$zip=mysql_result($result,$i,"zip");
$phone=mysql_result($result,$i,"phone");
$email=mysql_result($result,$i,"email");
$iba=mysql_result($result,$i,"iba");
$mbd=mysql_result($result,$i,"marchingband_currentdue");
$ctd=mysql_result($result,$i,"choir_currentdue");
?>
<?php
//define the receiver of the email
$to = '$email';
//define the subject of the email
$subject = '$firstname $lastname IBA';
//define the message to be sent. Each line should be separated with \n
$message = "Hello $firstname $lastname. This is your monthly email reminder. Your IBA Account is $iba, for Marching Band you owe $mbd, and for Choir you owe $ctd. Thank You.";
//define the headers we want passed. Note that they are separated with \r\n
$headers = "From: [email protected]\r\nReply-To: [email protected]";
//send the email
$mail_sent = @mail( $to, $subject, $message, $headers );
//if the message is sent successfully print "Mail sent". Otherwise print "Mail failed" 
echo $mail_sent ? "Mail sent" : "Mail failed";
?>
<br>

<br>

<?php
++$i;
} 
?>

 

I'm pretty sure that's how you would send one email. I just need to figure out where it goes through the database and takes one username, attaches the values, sends, repeat.

 

Thanks!

[bnovak]

hey you do realize that your SELECT statement is pulling only one ID, right? get rid of the WHERE part, and move the mysql_close() to the bottom.

then do

 

 

while($row = mysql_fetch_array($num))

{

your email code

}

mysql_close($con)

 

that should loop through everything and send the emails one by one. You might have to futz with it a bit but that's pretty close.

[Call-911]

I put in an id so I could for sure get one result. I couldn't figure out how to get more then that one result.

 

So you think I should do something like this?

 

<?php
// Start a session
session_start();


include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$id = $_POST['studentid'];
$query="SELECT * FROM members'";
$result=mysql_query($query);
$num=mysql_numrows($result); 

$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$username=mysql_result($result,$i,"username");
$firstname=mysql_result($result,$i,"firstname");
$lastname=mysql_result($result,$i,"lastname");
$address=mysql_result($result,$i,"address");
$city=mysql_result($result,$i,"city");
$state=mysql_result($result,$i,"state");
$zip=mysql_result($result,$i,"zip");
$phone=mysql_result($result,$i,"phone");
$email=mysql_result($result,$i,"email");
$iba=mysql_result($result,$i,"iba");
$mbd=mysql_result($result,$i,"marchingband_currentdue");
$ctd=mysql_result($result,$i,"choir_currentdue");


while($row = mysql_fetch_array($num))
{
//define the receiver of the email
$to = '$email';
//define the subject of the email
$subject = '$firstname $lastname IBA';
//define the message to be sent. Each line should be separated with \n
$message = "Hello $firstname $lastname. This is your monthly email reminder. Your IBA Account is $iba, for Marching Band you owe $mbd, and for Choir you owe $ctd. Thank You.";
//define the headers we want passed. Note that they are separated with \r\n
$headers = "From: [email protected]\r\nReply-To: [email protected]";
//send the email
$mail_sent = @mail( $to, $subject, $message, $headers );
//if the message is sent successfully print "Mail sent". Otherwise print "Mail failed" 
echo $mail_sent ? "Mail sent" : "Mail failed";
}
mysql_close($con)
?>
<br>

<br>

<?php
++$i;
} 
?>

[Call-911]

Okay, changed up the form to use the SendMail method instead of the basic php mail. Works much better for me. So I have this working perfectly to send out the email if I specify the ID, now I need to figure out how it to go through the database row by row and send the specific email with the specific values.

 

<?php
// Start a session
session_start();


include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$id = $_POST['studentid'];
$query="SELECT * FROM members WHERE id='200472'";
$result=mysql_query($query);
$num=mysql_numrows($result); 
mysql_close();

$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$username=mysql_result($result,$i,"username");
$firstname=mysql_result($result,$i,"firstname");
$lastname=mysql_result($result,$i,"lastname");
$address=mysql_result($result,$i,"address");
$city=mysql_result($result,$i,"city");
$state=mysql_result($result,$i,"state");
$zip=mysql_result($result,$i,"zip");
$phone=mysql_result($result,$i,"phone");
$email=mysql_result($result,$i,"email");
$iba=mysql_result($result,$i,"iba");
$mbd=mysql_result($result,$i,"marchingband_currentdue");
$ctd=mysql_result($result,$i,"choir_currentdue");

$fd = popen("/usr/sbin/sendmail -t","w") or die("Couldn't Open Sendmail");
fputs($fd, "To: $email \n");
fputs($fd, "From: \"LWE Music\" <[email protected]> \n");
fputs($fd, "Subject: $firstname $lastname IBA \n");
fputs($fd, "X-Mailer: PHP3 \n\n");
fputs($fd, "To the parents of $firstname $lastname,

This is your monthly LWE Music IBA Statement.

Your current IBA is: $iba
Your current Marching Band Fees are: $mbd
Your current Choir Trip Fees are: $ctd

Thank You,
LWE Music \n");
pclose($fd);
?>
<br>

<br>

<?php
++$i;
} 
?>

[thomashw]

So a way to loop through all the rows in a table would be like this:

while( $row = mysql_fetch_assoc( $result ) )

 

You can get rid of your $num variable, and replace your while loop with the loop I just posted. You'll also need to move your mysql_close(); to the bottom since your while loop will now be connecting to your database constantly.

 

The cool thing about mysql_fetch_assoc is it lets you use the actual names of your columns, like this:

$row["id"];

 

Here's your code switched around. It should work, but you may need to mess with it a bit.

 

<?php
// Start a session
session_start();


include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM members";
$result=mysql_query($query);

while ($row = mysql_fetch_assoc( $result ) ) {
$id = $row["id"];
$username = $row["username"];
$firstname = $row["firstname"];
$lastname = $row["lastname"];
$address = $row["address"];
$city = $row["city"];
$state = $row["state"];
$zip = $row["zip"];
$phone = $row["phone"];
$email = $row["email"];
$iba = $row["iba"];
$mbd = $row["marchingband_currentdue"];
$ctd = $row["choir_currentdue"];

$fd = popen("/usr/sbin/sendmail -t","w") or die("Couldn't Open Sendmail");
fputs($fd, "To: $email \n");
fputs($fd, "From: \"LWE Music\" <[email protected]> \n");
fputs($fd, "Subject: $firstname $lastname IBA \n");
fputs($fd, "X-Mailer: PHP3 \n\n");
fputs($fd, "To the parents of $firstname $lastname,

This is your monthly LWE Music IBA Statement.

Your current IBA is: $iba
Your current Marching Band Fees are: $mbd
Your current Choir Trip Fees are: $ctd

Thank You,
LWE Music \n");
pclose($fd);

echo '<br /><br />';
}

mysql_close();
?>