Simple Join Question

[bostonmacosx]

Two tables:

Users: usr_id name and email
affiliations: usr_id affiliation_id

What I need to do is join them. I can do this however in displaying the resulting data I get a return set of three records if I have three affiliation_id's' to the specified user id. This is a one-to-many join I believe. I'm wondering how to correctly display the data.

If I just mydql_fetch_array I will get a loop through the name three times etc etc.
Below is how I want to display the data.

Thanks
Robert

usr_id name email affiliation_id's'

[bretticus]

You can do it programmatically or just do it in the query. Here's a programmatic example (not your variable names.)

//make sure query sorts by user id.

$current_person = "";
while ($row = mysql_fetch_assoc($result)) {
	if ($row['name'] != $current_person) {
		//print name, email, etc. PLUS first affiliation
	} else {
		//print out affiliations
	}
	$current_person = $row['name'];
} 

SELECT usr_id, name, email, GROUP_CONCAT(affiliation_id) FROM users INNER JOIN affiliations USING (usr_id) GROUP BY usr_id

See GROUP_CONCAT for more details.