Combine items under a GROUP as one item?

[SEVIZ]

Yes here I am again.  Every time I learn something, a new thing pops up!!

 

<?php 

$q = "SELECT `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>";
}
?>

 

The above works but it shows up twice.  I know it is because it is breaking it into the two groups I queried for (ie one shows up for T_LEAD and one shows up for T_SUP).  How via php/mysql can I combine these two into one selection?  Is it even possible? 

 

Thanks as always guys!

[.josh]

hard to say for sure without knowing your database structure and more explanation, but offhand, you could try

 

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

[SEVIZ]

No luck with that.  My database structure is dept/sup/id/num/name/loc/all.  Under dept there is about 10 different choices.  T_LEAD and T_SUP being just two of them.  For this option in my dropdown I want these two options to show as one option.

 

I hope this makes sense.  Feel free to ask any other question you may need.  I once again appreciate any and all help I receive.

[SEVIZ]

I thought of a work around but I only want to use it if there is no way to do this.  What I was going to do is add another row to the DB with a subtitle of L_S for leads and sups.  Then add this title to all the leads and sups.  Then query based on that row.  The only issue is its another row that I do not think is needed.

 

Let me know if that would be my best bet.

 

Thanks

[.josh]

if all the other data in the rows are identical, and the only difference between one row and another is whether dept is T_LEAD or T_SUP, then why have that column at all, if you want them to be grouped anyways?  Or am I not understanding your intention here?  Because it sounds to me that the real problem is somewhere in your system you are putting data into the table twice, if you are only wanting to show data once.

[SEVIZ]

The data in all the rows are different.  This whole thing is an email script basically.  It lets you choose who to send a mass message to.  Choices are, All Leads, All Sups, Techs, etc.  And in this particular case my goal is to have one choice be Leads and Sups.  To do that I need to join the data as one choice.

 

In the DB each line is different.  They are just labeled under "Dept" as what they are.

 

I apologize if this makes no sense.

[gizmola]

what you are asking for doesn't make sense.

 

You want to get 2 different groups of departments and then GROUP BY on dept, yet you don't actually want the 2 groups?

 

In looking at your original code, there's no reason to even be doing the query, because all you're doing is creating a drop down list, and it seems you are trying to force that list to have a single value in it.

 

I'm completely confused -- perhaps it would be better if you explained what you want to achieve and why.

 

 

[soak]

From what you've said the combinations of groups are manually selected. Why not just hard code the entries in the HTML rather than hard coding them in a db query?

 

So your HTML would become:

<option value="T_LEAD|T_SUP">T_LEAD and T_SUP</option>

 

Then when you check the value to decide who to send to just explode it on | and loop it.

[SEVIZ]

Thank you again for your help.  I apologize that this is confusing.  I only posted a snippet of my code to highlight my problem but it appears the biggest problem is that you cannot see my entire goal with this snippet.

 

The point of this script is to let a user send an email to a phone from the app.  The dropdown holds many choices.  Right now I am using a couple queries for the info and I just posted one.  Here is my entire code to get a better idea of what I am doing.

 

<?php 

mysql_connect("localhost", "XXXXX", "XXXXX") or die(mysql_error());
mysql_select_db("TEXT_TOOL") 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 { 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="en-us" http-equiv="Content-Language" />
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Text Tool</title>
<style type="text/css">
.style1 {
text-align: center;
}
</style>
<SCRIPT LANGUAGE="JavaScript">
<!-- Begin
function textCounter(field, countfield, maxlimit) {
if (field.value.length > maxlimit) // if too long...trim it!
field.value = field.value.substring(0, maxlimit);
// otherwise, update 'characters left' counter
else 
countfield.value = maxlimit - field.value.length;
}
// End -->
</script>
</head>

<body>
<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>";
}
?
<!-- THIS ONE DOESNT WORK CURRENTLY -->
<?php 

//$q = "SELECT `dept` FROM `sprint` WHERE dept='T_LEAD' OR dept='T_SUP' GROUP BY `dept`";
$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 onKeyDown="textCounter(this.form.message,this.form.remLen,143);" onKeyUp="textCounter(this.form.message,this.form.remLen,143);" 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 hope this helps.  Now as of right now I know I am using multiple queries but its the only way I could break these choices down via my limited knowledge.  Now my original issue right now is that I need one of the options to include two or more of the previous options listed.  This is because the point of this script is for management at my work to be able to text a specific group by simply selecting them in the drop down.  Because of this sometimes a group will overlap.  For instance, they may want to send a text to leads and sups only.  Then it could be Sales and sups only, etc. 

 

Feel free to ask any other questions.

[soak]

You're trying to put different fields in your drop down and your send is only set to parse the `dept` field.

 

You first need to work out what groups you will need to send to and how you can represent them in your option drop down.

 

If you need to send to some dept's and some individual users then you need some sort of code for each,

 

i.e. your option value fields may look like this:

D-DEPT1

D-DEPT2

U-USER1

U_USER2

 

That way when your form is submitted, if the field starts with a D- then you need to email a department by the name of what follows the dash. If the field begins U- then you need to look up a user and email them.

 

Your script has grown since the last time I saw it so I suggest you work out exactly what you will need to represent in the drop down and work out a way to represent it so that you can send emails to the correct users. Then you can worry about getting the user data out of the database. 

 

[SEVIZ]

My very limited experience is showing here.  I appreciate your reply and I will look into what you are saying now. 

 

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 to call the above information into a dropdownlist.  But it is all done in groups.

 

Such as:

Text all leads

Text all leads and sups

Text Techs Only

Text Everyone

Text All of North

Text All of South

 

When the above is chosen it queries the DB and gets the 'num' and emails it.  The other info in the database is used for another script so any unused rows are used elsewhere.

 

[SEVIZ]

Should I start over from scratch?  Am I that off in how I'm doing this?  Any ideas are appreciated.

[gizmola]

Ok, so I think i understand what you have.

 

First off, forget about trying to find a query that will provide you what is in essence an arbitrary WHERE clause.  Build your drop down list explicitly in your PHP code.  Once the form is processed, depending on the value from the Option, you will set the WHERE clause appropriately to get the list of numbers you require. 

 

$sel = 

Text all leads
Text all leads and sups
---etc.


SEL;

echo $sel;

 

 

I'd suggest you create a function to do that, based on a switch statement, which could be something like:

 

 

function buildRecipeClause($option) {
  $where = '';

  switch ($option) {
    //  Leads and Sups
    case 1 :
      $where = "dept='T_LEAD'";   
      break;
    case 2 :
      $where = "dept='T_LEAD' OR dept='T_SUP'";   
      break;
    case 3 : etc.
    default :
  }
  return $where;      
}

 

You concat this where clause onto your numbers query, to get the list of people who should get the page emails.