Emailing to multiple recipients in a database

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?

$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 = "me@example.com";
$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. :)

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"];
    }
  

}

$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 = "me@example.com";
$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?

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.

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