Page 1 of 1

Using group by to get multiple names in same address?

Posted: Thu Jul 05, 2007 11:58 pm
by robster
Sounds cryptic I know :) let me explain.

I have a table of people and their details that have attended courses at our institution and we want to do a mailout to them.

That's fine and easy, but what is happening is that we are printing envelopes for 5 people that come from the same business, hence increasing our postage bills dramatically.

I would like to be able to do some kind of join / grouping where MySQL will return all the people that attended the courses, but only have it show the one address.... yet... erm... well, I think I just caught myself out.

I need the NAMES of the people and to know what salon they're from too don't I? If I want to put them in the same envelope... sigh..

I know how to show just the single salons. I do that with:

Code: Select all

select * from education_attendees group by salon_name order by salon_name asc
I guess what I need then, is to see a list of people that that DOESN'T return a result, so we can print them and stick them in the envelopes also...


I'm not even sure if there's a question here, but any advice would REALLY be appreciated :)


Rob

Posted: Fri Jul 06, 2007 12:02 am
by feyd
It seems you need two different queries. One for the letter print out and one for the envelope print out.

Posted: Fri Jul 06, 2007 12:12 am
by John Cartwright
It personally makes more sense to me to have php do the grouping. So you would select all of your users you want,

Code: Select all

SELECT * FROM education_attendees ORDER BY salon_name ASC

Code: Select all

$business = array();
while ($row = mysql_fetch_assoc($result)) {
  if (!array_key_exists($row['salon_name'], $business)) {
    $business[$row['salon_name']] = array();
  }

  $business[$row['salon_name']][] = $row['attendee_name'];
}
Now you have all your attendees grouped by the business (array key), you could easily print the envelopes doing something like

Code: Select all

foreach ($business as $businessname => $attendees) {
   echo 'Business: '. $businessname.' <br />';
   echo 'Attendees: '. implode(', ', $attendees);
}
Thats a little more code I usually write for people, but I've seen this question creep out quite a few times.