How to email group based on dropdown selection? Data from DB.

[SEVIZ]

How would you all go about this?  I have a table in my database setup like this:

 

dept | sup | id | num | name | loc

 

Content in the rows below look like this:

 

lead  | bob | 7111 | 5552224444 | George | North

sup  | Tito | 7222 | 5552225555 | Tim | South

tech  | Tito | 7333 | 5552226666 | Tina | North

 

Now in a php page I am needing show a dropdown with choices that are basically groups of the above information. 

 

Such as:

 

leads

leads and sups

Techs Only

Everyone

All of North

All of South

 

When the above is chosen it queries the DB and gets the data from row 'num' and emails it.  The other info in the database is used for another script so any unused rows are used elsewhere.  For instance, I click leads and sups in the dropdown and it emails everyone that have a lead or sup name in row 'dept'.  I created a script with help from this forum for this but it does not work beyond one group.

 

Here is the code being used:

 

<?php 

mysql_connect("localhost", "XXXXXXX", "XXXXXXXXXXX") or die(mysql_error());
mysql_select_db("XXXXXXXXXXXX") or die(mysql_error());

if (!empty($_POST["recip"])) { 
    $q = "select `num` from sprint WHERE `dept` = '".mysql_real_escape_string($_POST["recip"])."'";
    $ret = mysql_query($q);
    $emails = array();
    while ($row = mysql_fetch_assoc($ret)) {
        $emails[] = $row['num'].'@messaging.sprintpcs.com';
    }
    if ($emails) {
        $ToEmail = implode(', ', $emails); 
        $EmailSubject = $_POST["subject"]."\r\n";
        $mailheader = "From: ".$_POST["email"]."\r\n"; 
        $MESSAGE_BODY = nl2br($_POST["message"]);
   // $MESSAGE_BODY .= ($_POST["auth"]); 
        mail($ToEmail,$EmailSubject, $MESSAGE_BODY,$mailheader) or die ("Failure"); 
    }

?>
<font color="red"><b>Your Text has been sent. The message sent was:</b></font><br /><br />
<font face="tahoma" size="4"><?php echo $_POST["message"]; ?></font>

<br /><br />
- <b><a href="text_tool.php">GO BACK</a></b>
<?php 
} else { 
?>


<form action="text_tool2.php" method="post">
<table width="100%" border="0" cellspacing="2" cellpadding="0">
<tr>
      <td valign="top">
      Send To:<br />

<select name="recip">
<?php 

$q = "select `all` from sprint GROUP BY `all`";
$ret = mysql_query($q);

while ($row = mysql_fetch_assoc($ret)) {
    $email_sec = $row['all'];
    print "<option value=\"$email_sec\">$email_sec North and South</option>";
}
?>
<?php 

$q = "SELECT DISTINCT(`dept`) FROM `sprint` WHERE dept='T_LEAD' OR dept='T_SUP' GROUP BY `dept`";
$ret = mysql_query($q) or die(mysql_error());

while ($row = mysql_fetch_assoc($ret)) {
    $email_sec = $row['dept'];
    print "<option value=\"$email_sec\">Leads and Sups</option>";
}
?>
<?php 

$q = "select `loc` from sprint GROUP BY `loc`";
$ret = mysql_query($q);

while ($row = mysql_fetch_assoc($ret)) {
    $email_sec = $row['loc'];
    print "<option value=\"$email_sec\">$email_sec Only</option>";
}
?>
<?php 

$q = "select `dept` from sprint WHERE dept='S_REP' GROUP BY `dept`";
$ret = mysql_query($q);

while ($row = mysql_fetch_assoc($ret)) {
    $email_sec = $row['dept'];
    print "<option value=\"$email_sec\">All Sales</option>";
}
?>
<?php 

$q = "SELECT `sup` FROM `sprint` WHERE `sup` IS NOT NULL GROUP BY `sup`";
$ret = mysql_query($q) or die(mysql_error());

while ($row = mysql_fetch_assoc($ret)) {
    $email_sec = $row['sup'];
    print "<option value=\"$email_sec\">Team $email_sec</option>";
}
?>


         </select>
      </td>
   </tr>


<tr>
<td>Message:<br /><textarea name=message wrap=physical cols="20" rows="6" id="comment" maxlength="143"></textarea><br /><input readonly type=text name=remLen size=3 maxlength=3 value="143" /> characters left
</td>
</tr>
<tr>
<td>
<!-- Reply to email (If applicable):<br /> --><input name="email" type="hidden" id="email" size="20" value="[email protected]"></td>
</tr>
<tr>

<td valign="top"><font color="red" size="2"><b>Do not hit enter for a new line. This will give you less characters to use due to the text limits.</b></font><br /><input type="submit" name="Submit" value="Send"></td>
</tr>

</table>
</form>
</body>
</html>
<?php 
}; 
?>

 

I am at a loss here guys and really need help on where to go next.  Is there some easier way I am missing?  I simply need to email certain people depending on what choice is selected in the dropdown.

 

Now I know in my code in the top line I am only looking for "num" based on "dept".  And in my other dropdown choices I have other rows.  Can I just alter this top line to "dept" OR "sup" OR "loc" ? 

 

Thank you for any help or guidance you provide.  I took this on at work because we do not have anyone to do this kind of thing.

 

[Ken2k7]

Can you var_dump($_POST['recip'])? What value does that hold?

 

Edit: actually it would help more if you just list the values of your options instead. To me, it seems like the value changes to use different columns and your SQL always use dept.

[SEVIZ]

 

Edit: actually it would help more if you just list the values of your options instead. To me, it seems like the value changes to use different columns and your SQL always use dept.

 

Exactly.  That is the main problem.  How can I change the query for the email part to depend on the selection and not use dept only?

[Ken2k7]

In your form, put a hidden field called selection or something and put what column to search by.

[SEVIZ]

I apologize I am very new to this.  How would I do that?  How would that change the query only searching for dept row?

[Ken2k7]

In your form, put a hidden field called selection or something and put what column to search by.

^ do that. In your form, add a new input field and set it to hidden. Google if you don't know how to do that. Give it a name and a value. The value should be the COLUMN_NAME to select by.

 

Then when the form is submitted, use that hidden field as the column to select by rather than using dept.

[SEVIZ]

I understand that part.  But the column to search by is different depending on which item is grabbed in the dropdown.  How does putting a hidden field with the column name help?  I would then have to find a way to have the hidden item change depending on the dropdown list.

 

Or am I missing something?

[Ken2k7]

Ugh, fine, here's another way. Hopefully you get how to implement this - In the options, set the value to be 'col_name+value' instead of just 'value'.

 

[SEVIZ]

That makes sense.  What if for each option I removed the php and queries all together.  Then just had each option with a value of lt, t, etc.

 

Then in the actual email query I change it from searching dept via recip.  And create an if statement that if recip = X do this query, and if recip =C, do this query.

 

Would that work?