SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot MetalAges's Avatar
    Join Date
    Jan 2003
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arranging SELECT results, one person, many roles

    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot MetalAges's Avatar
    Join Date
    Jan 2003
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Zealot MetalAges's Avatar
    Join Date
    Jan 2003
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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>';
    }
    ?>
    Last edited by MetalAges; Jan 30, 2005 at 23:42.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •