php array - sending a single email for each unique email address grouping there data together

Question

I have an indefinite amount of items in an array:

$query = mysql_query("SELECT * FROM orders WHERE orderID = '$orderID'");        

while($row = mysql_fetch_array($query)){
   $items[] = $row[itemnumber];
   $quantity[] = $row[quantity];
   $vendoremail[] = $row[vendoremail];
} 

The values in the arrays could be something like this:

 $items = ("A","B", "C");
    $quantity = ("2", "1", "3");
    $vendoremail = ("name1@email.com", "name2@email.com", "name1@email.com");

I want to group the data from the same email addresses and send a single email to each address.

So one email gets sent to name1@email.com with the items "A" and "C" and quantity "2" and "3" in the body of the email.

And another email gets sent to name2@email.com with the items "B" and quantity "1" in the body of the email.

Anybody know the best way to accomplish this?

Answer 1

Given the fact that each member of array-n maps to array-n+1 I would do the following:

$groups = array();
while ($r = mysql_fetch_assoc($query)){
  if (!isset($groups[$r['email']])) {
    $groups[$r['email']] = array();
  }
  $groups[$r['email'][] = array($r['items'], $r['quantities']);
}

and now you can iterate through the groups array which each email key having one or more arrays

Answer 2

Just making me cringe, but wrap your key names in arrays with quotes!

in your original while loop you could do something like.

while($row = mysql_fetch_array($query)){
   $items[] = $row['itemnumber'];
   $quantity[] = $row['quantity'];
   $vendoremail[] = $row['vendoremail'];

   $emailList[$row['vendoremail']][] = $row;
} 
foreach ($emailList as $email => $itemList)
{
    $emailMessage = "Dear vendor, here is information about new orders:\n\n\n";
    foreach($itemList as $itemInfo)
    {
       $emailMessage .= "------\n";
       $emailMessage .= "item num: #".$itemInfo['itemnumber'];
       $emailMessage .= "quantity: ".$itemInfo['quantity'];
       $emailMessage .= "------\n\n";
    }
   $emailMessage .= "Sincerely,\nUs";

   mail($email, 'New Orders', $emailMessage, 'From: orders@ourcompany.com');
}

This was just a quick thing I wrote up, so I just carelessly threw \n linebreaks in there, but the from above code you should hopefully get the gist using multi-dimensional arrays.

Edit: For bonus points, as a matter of style and database design, you really should have a vendor table with a vendor ID, vendor name, email, contact info, etc and then have a foreign key column on the order table that points to a vendor ID, so you can JOIN to it and not store potentially duplicate and volatile (email addrs can change at any moment) data in each row. Just my off-topic optimization $0.02.

Answer 3

You could create the two arrays ($items and $quantity) using the e-mail as the key:

$query = mysql_query("SELECT * FROM orders WHERE orderID = '$orderID'");

while($row = mysql_fetch_array($query))
{
    $items[$row['vendoremail']][] = $row['itemnumber'];
    $quantity[$row['vendoremail']][] = $row['quantity'];
}

Then, just iterate over them, retrieving all the data each one contains:

foreach ($items as $key => $val)
{
    /* where $key can also be used to retrieve from $quantity */
}

Answer 4

You can group the values into comma-separated strings via GROUP_CONCAT() and optionally split them back up in PHP:

SELECT 
  GROUP_CONCAT(itemnumber) AS items,
  GROUP_CONCAT(quantity) AS quantities,
  vendoremail
FROM orders
WHERE orderid = '$orderID'
GROUP BY vendoremail

This will produce results like:

name1@email.com    A,C    2,3
name2@email.com    B      1

In PHP, if you need to you can explode() the lists back out into arrays to display in the email body.

$results = array();
while($row = mysql_fetch_array($query)){
   $results[] = $row; 
}
foreach ($results as $result) {
  $email = $result['vendoremail'];
  $items = explode(",", $result['items']); // now it's an array...
  $quantities = explode(",", $result['quantities']);  // now it's an array...
}