Emailing to multiple recipients in a database

[httpgal]

I have a bit of code I'm using to generate a report from my MySQL database - what I want to do is email it out to multiple recipients (whose email addresses are also stored in the database).

The email addresses (up to 12 of them) are located in fields s1e - s12e. What I want to do is check these fields and only send emails if the seat is filled (field is populated) and preferable, if the seat status is not yet confirmed.

I have tried both getting all the recipients at once:

//Get recipients from CourseQuery
  $recipient1 = $row["s1e"];
  $recipient2 = $row["s2e"];
  $recipient3 = $row["s3e"];
  $recipient4 = $row["s4e"];
  $recipient5 = $row["s5e"];
  $recipient6 = $row["s6e"];
  $recipient7 = $row["s7e"];
  $recipient8 = $row["s8e"];
  $recipient9 = $row["s9e"];
  $recipient10 = $row["s10e"];
  $recipient11 = $row["s11e"];
  $recipient12 = $row["s12e"];

or doing if statements to check for the seats being filled:

//Get sign ups for course 1
$courseQuery = 'SELECT s1e,s2e,s3e FROM class WHERE CID = "'.$cid.'"';

//Execute query and get resultSet
$result = mysql_query($courseQuery );   

//Loop through sign ups and generate email report to each candidate (using code from report.php)
$i=1;
while($row = mysql_fetch_array($result))
{


    if ($s1i <> "" and $s1s <> "confirmed")
    {
	 $recipient1 = $row["s1e"];
    }
    elseif ($s2i <> "" and $s2s <> "confirmed")
    {
	 $recipient2 = $row["s2e"];
    }
	elseif ($s3i <> "" and $s3s <> "confirmed")
    {
	 $recipient3 = $row["s3e"];
    }

    else
    {
    echo "no match";
    }

Nothing I have tried has worked yet. Any help would be appreciated.

[broj1]

What are variables $s1i, $s2i... and $s1s, $s2s... and where they get their values from? Do you get any error messages? What is the structure of the class table?

[calebcook]

$dbc = mysql_connect('host', 'user', 'pass', 'dbname') or die('Could not connect!!!!!');
$query = "SELECT * FROM `email_receipients` WHERE field != '' AND status != 'confirmed'";
$result = mysql_query($dbc, $query) or die('Error querying database!!!!!');

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

$sql = "SELECT * FROM `report` WHERE name = '" . $row['receipient_name'] . "' LIMIT 1";
$rs = mysql_query($dbc, $sql) or die('Could not query database!!!!!');
$row1 = mysql_fetch_array($rs);

$to = $row['receipient_email']
$from = "[e-mail]";
$subject = "Here is your report";
$message = "Hello!\nHere is the report details:\n\n" . $row1['report_details'] . "\n\nThanks!";

mail($to, $subject, $message, "From: " . $from);

}
mysql_close($dbc);

Or something like that. :)

[httpgal]

What are variables $s1i, $s2i... and $s1s, $s2s... and where they get their values from? Do you get any error messages? What is the structure of the class table?

Those are all individual database fields (s1i, s1s, s1e stands for seat 1 id, seat 1 status and seat 1 email). Good point, I should be declaring those variables or just using the database field names (which they are).

Perhaps the IF statement should look more like this?

//if statement to set recipients for this email based on whether or not that seat is filled
while ($row = mysql_fetch_array($result))
{


    if (s1i <> "" and s1s <> "confirmed")
    {
	 $recipient1 = $row["s1e"];
    }
    elseif (s2i <> "" and s2s <> "confirmed")
...

Or

//if statement to set recipients for this email based on whether or not that seat is filled
while ($row = mysql_fetch_array($result))
{
$seat1 = $row["s1i"]
$status1 = $row["s1s"]

    if ($seati <> "" and $status1 <> "confirmed")
    {
	 $recipient1 = $row["s1e"];
    }
  

}

[httpgal]

$dbc = mysql_connect('host', 'user', 'pass', 'dbname') or die('Could not connect!!!!!');
$query = "SELECT * FROM `email_receipients` WHERE field != '' AND status != 'confirmed'";
$result = mysql_query($dbc, $query) or die('Error querying database!!!!!');

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

$sql = "SELECT * FROM `report` WHERE name = '" . $row['receipient_name'] . "' LIMIT 1";
$rs = mysql_query($dbc, $sql) or die('Could not query database!!!!!');
$row1 = mysql_fetch_array($rs);

$to = $row['receipient_email']
$from = "[e-mail]";
$subject = "Here is your report";
$message = "Hello!\nHere is the report details:\n\n" . $row1['report_details'] . "\n\nThanks!";

mail($to, $subject, $message, "From: " . $from);

}
mysql_close($dbc);

Or something like that. :)

Caleb, would that work if the classes table structure contains 36 fields for the 12 seats (student id, email, status)?

If you make the first query

$query = "SELECT * FROM `classes` WHERE CID = '6' and s1i != '' AND s1s != 'confirmed'";

would you even need a second query? Or could you use those results to specify the recipient email address for seat 1 ($to = $row)
and if so, how do you go back to specify the next email recipient ($to = $row)

I guess when it comes down to it, I'm not sure whether to do this as a loop, or as a series of IF statements on a single query result. Any thoughts?

[broj1]

My opinion: since you have data for all 12 seats in one row you have to select all entire rows with selected CID and then for each row loop through all 12 seats to see wheter email is not empty and the seat is confirmed. So the following query will get all rows for chosen CID:

$courseQuery = 'SELECT * FROM class WHERE CID = "'.$cid.'"';

and then the following code will send email just to the recipients with email filled in and who confirmed the course:

while ($row = mysql_fetch_array($result))
{
    for($s=1; $s<=12; $s++){

        $email = 's' . $s . 'e';
        $status = 's' . $s . 's';

        if($row[$email] != '' and $row[$status] == 'confirmed') {

            // send email
            mail($email, 'Put subject here', 'Put message here', 'Put From: address here);
        }
    }
}

How to prepare a formated email message was nicely shown in calebcook's post above.

Just a thought: there is a question wheter is it OK to have all seats as fields in one row. Maybe they should be in a new table called e.g. seats and have fields cid, seat_number, email, confirmed (of type boolean with values true/false or 1/0) etc. This way the space would be used more efficiently since only taken seats would be stored. Then you would join the two tables and select by CID and where confirmed equals 1. Just an idea.

[calebcook]

My opinion: since you have data for all 12 seats in one row you have to select all entire rows with selected CID and then for each row loop through all 12 seats to see wheter email is not empty and the seat is confirmed. So the following query will get all rows for chosen CID:

$courseQuery = 'SELECT * FROM class WHERE CID = "'.$cid.'"';

and then the following code will send email just to the recipients with email filled in and who confirmed the course:

while ($row = mysql_fetch_array($result))
{
    for($s=1; $s<=12; $s++){

        $email = 's' . $s . 'e';
        $status = 's' . $s . 's';

        if($row[$email] != '' and $row[$status] == 'confirmed') {

            // send email
            mail($email, 'Put subject here', 'Put message here', 'Put From: address here);
        }
    }
}

How to prepare a formated email message was nicely shown in calebcook's post above.

Just a thought: there is a question wheter is it OK to have all seats as fields in one row. Maybe they should be in a new table called e.g. seats and have fields cid, seat_number, email, confirmed (of type boolean with values true/false or 1/0) etc. This way the space would be used more efficiently since only taken seats would be stored. Then you would join the two tables and select by CID and where confirmed equals 1. Just an idea.

Yep, broj1's solution should work