Arranging SELECT results, one person, many roles

[MetalAges]

Hey there everyone. This one has me currently stumped grrr. I have the results I desire via this SELECT statement:

SELECT person.name, instru.type
FROM person, instru
LEFT JOIN role, whatplay ON role.personid = person.id
AND whatplay.personid = person.id
AND whatplay.instruid = instru.id
WHERE role.albumid =51
AND whatplay.albumid =51

Results:
Nick D'Virgilio Lead Vocals
Nick D'Virgilio Guitar
Nick D'Virgilio Bass
Nick D'Virgilio Drums
Nick D'Virgilio Percussion
Alan Morse Vocals
Alan Morse Guitar
Alan Morse Theremin
Alan Morse Saw
Alan Morse Cello
Ryo Okumoto Vocals
Ryo Okumoto Keyboard
Dave Meros Vocals
Dave Meros Bass
Dave Meros Bass Pedals
Dave Meros French Horn

Now my goal is to display the results like this:

Nick D'Virgilio:
Lead Vocals, Drums, Percussion, Guitar, Bass
Alan Morse:
Guitar, Theremin, Saw, Cello, Vocals
Ryo Okumoto:
Keyboards, Vocals
Dave Meros:
Bass, Bass Pedals, French Horn, Vocals

I can't figure out how to only have the person's name show one time, then all the instruments he plays in a row like that. I can get them set up in an array easy enough but doing it that way I am not sure how to structure it so that the name only shows once. Any guidance appreciated!

[r937]

if you're on mysql 4.1, you can do this quite easily with the GROUP_CONCAT function

otherwise, do it in your application program

[MetalAges]

Thanks for the 4.1 info! I'm still on 4.0.22 so I don't have that option just yet and am too new at this more advanced coding to whip it up in PHP. Been at this (probably simple! lol) for about 8 hours now. (mod) Can this be moved to the PHP forum please?

Thanks Rudy! I can't wait for the day I can help others like you do.

[MetalAges]

I had someone that has helped me figure this out (I still don't know enough to know what the heck the code is doing but...) so I wanted to paste this here for the archives in case someone else needs guidance:

<?php
// Select IOMA database
if (!mysql_select_db('DBName')) {
exit('<p>Unable to locate the band' .
'database at this time </p>');
}
// grab current album variable
$result = mysql_query('SELECT albums.id FROM band, albums WHERE band.id=' . $id . ' AND albums.bandid=' . $id . ' ORDER BY albums.release ASC');
if (!$result) {
exit('<p>Error performing query: ' . mysql_error() . '</p>');
}
while ($row = mysql_fetch_array($result)) {
$current = $row[0];
}
// request results
$result = mysql_query('SELECT person.name, instru.type FROM person, instru LEFT JOIN role, whatplay ON role.personid = person.id AND whatplay.personid = person.id AND whatplay.instruid = instru.id WHERE role.albumid =' . $current . ' AND whatplay.albumid =' . $current . ' ');
if (!$result) {
exit('<p>Error performing query: ' . mysql_error() . '</p>');
}
$prevName = "";
while ($row = mysql_fetch_array($result)) {
if ($row['name'] != $prevName) {
if ($prevName != "") {
$instOut = substr($instOut , 0, -2);
echo '<span class="bold">' . $prevName . '/span><br>';
echo $instOut . '<br>';
}
$prevName = $row['name'];
$instOut = "";
}
$instOut .= $row['type'].", ";
}
if ($prevName != "") {
$instOut = substr($instOut , 0, -2);
echo '<span class="bold">' . $prevName . '/span><br>';
echo $instOut . '<br>';
}
?>

[r937]

tell the person who wrote this code for you that doing a query inside a loop is a performance killer and a poor coding habit

i'm not trying to be mean, just helpful

it should be a single query with joins to all necessary tables, returning a single result set, that is then processed in a loop to provide the formatted output

[swdev]

Can you post your table descriptions and some sample data (best done usig mysqldump or phpmyadmin) so I can setup a test database.
Then I'll see if I can write some code to do what you want.