Using group by to get multiple names in same address?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Using group by to get multiple names in same address?

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It seems you need two different queries. One for the letter print out and one for the envelope print out.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
Post Reply