SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Group BY with Having or Where clause?

    Scenario... i have a DB of music files with multiple songs from one artist... i want to list out all the songs and group them by the artist name.

    When I include "HAVING" it just returns one value... WHen I just have "GROUP BY", then the artist name is repeated.... any suggestions?

    SELECT a.filename, a.title, a.year, a.country, a.artistid, b.artistid, b.name
    FROM avroom a, artists b
    GROUP BY b.name
    // HAVING a.artistid = b.artistid
    ORDER BY b.name ASC LIMIT 0 , 30

    Is there a group by function that can be run on the "WHILE" statement as it spits out the result...hmmm

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You will have to do it in PHP. Just ORDER BY the name, then check and see if it was the same as the previous. If so, don't echo it.

  3. #3
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MattR
    You will have to do it in PHP. Just ORDER BY the name, then check and see if it was the same as the previous. If so, don't echo it.
    English->Psuedo-Code:

    PHP Code:
    $result mysql_query('SELECT blah, artist FROM table1 ORDER BY artist');

    $last_artist '';
    while (
    $row mysql_fetch_array($result)) {
       if (
    $row['artist'] != $last_artist) {
          echo 
    $row['artist'] . ':';
          
    $last_artist $row['artist'];
       }
        echo 
    '   ' $row['blah'];

    My name is Steve, and I'm a super-villian.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried to do it but got a parse error... this is what is in my code... aside from the select statement...
    Code:
     
    $avroom_audio=$DB_site->query("SELECT etc etc... " );
    
    $last_artist = '';
    while ($audio=$DB_site->fetch_array($avroom_audio)){
    
    if ($audio['name'] != $last_artist) { 
    $audio['name'] = unhtmlspecialchars($audio['name']); 
    $last_artist = $audio['name'];
    $audio['title'] = unhtmlspecialchars($audio['title']);
    $audio['year'] = unhtmlspecialchars($audio['year']);
    $audio['country'] = unhtmlspecialchars($audio['country']);
    $audio['avroomid'] = unhtmlspecialchars($audio['avroomid']);
    }
    $audio['name'] = '';
    $audio['title'] = unhtmlspecialchars($audio['title']);
    $audio['year'] = unhtmlspecialchars($audio['year']);
    $audio['country'] = unhtmlspecialchars($audio['country']);
    $audio['avroomid'] = unhtmlspecialchars($audio['avroomid']);
    } 
    
    /other code down below... 
    
    }

  5. #5
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by stryka
    I tried to do it but got a parse error... this is what is in my code... aside from the select statement...
    I'm afraid this doesn't help -- posting your previous code, not the code that we provided, and without the exact parse error, makes it very difficult for us to help.

    What parse error?
    What line?
    In our script? Your script?

    unhtmlspecialchars isn't a standard php function -- is this defined properly in your script?
    My name is Steve, and I'm a super-villian.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry bout that... the code is for a custom VB page so i can use unthml... here is how it looks currently: here is the acutal output http://64.191.13.160/avroom/index_audio.php
    Code:
    $avroom_audio=$DB_site->query("SELECT etc etc.. " );
    
    // other code above but it workz as is below...
    
    $last_artist = '';
    while ($audio=$DB_site->fetch_array($avroom_audio)){
    
    $audio['name'] = unhtmlspecialchars($audio['name']); 
    $audio['title'] = unhtmlspecialchars($audio['title']);
    $audio['year'] = unhtmlspecialchars($audio['year']);
    $audio['country'] = unhtmlspecialchars($audio['country']);
    $audio['avroomid'] = unhtmlspecialchars($audio['avroomid']);
    
    eval("\$audiobits .= \"".gettemplate('avroom_audiobits')."\";" );
    }
    $DB_site->free_result($audio);
    //unset($thread);
    eval("\$imix_left_navbar = \"".gettemplate('imix_left_navbar')."\";" );
    eval("\$imix_140banner = \"".gettemplate('imix_140banner ')."\";" );
    eval("\$events_intro = \"".gettemplate('events_intro')."\";" );
    eval("dooutput(\"".gettemplate('avroom_audio')."\" );" );
    Last edited by stryka; Sep 10, 2003 at 11:14.

  7. #7
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I visited the site -- but received no parse error
    My name is Steve, and I'm a super-villian.

  8. #8
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i know...the current link is an example of how it is working right now with the code above... i was hoping you can show me how to plug-in your suggestion with the code above... once i plugged your info in (incorrectly i assume) i got a parse error @ $DB_site->free_result($audio);

  9. #9
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ahhhhhh -- gotcha

    (actually, I didn't even see my code in the midst of yours, -- I'm blind!)

    Ok, I dont know how gettemplate handles the array that is created -- but the idea is, if the code inside the if clause is executed, we have a new author and new to create a new title. Otherwise, we simply execute the code to display the current music record along side.

    For example (removed templates, since I don't know what they do)

    PHP Code:
    <?php $last_artist ''?>

    <table>
    <?php
    while ($audio=$DB_site->fetch_array($avroom_audio)){
       echo 
    '<tr>';
       if (
    $audio['name'] != $last_artist) {
          echo 
    '<td><strong>', echo $audio['name'] . '</strong></td>';
          
    $last_artist $audio['artist'];
       } else {
          echo 
    '<td>&nbsp;</td>'//empty cell
       
    }
       
    $audio['name'] = unhtmlspecialchars($audio['name']); 
       
    $audio['title'] = unhtmlspecialchars($audio['title']);
       
    $audio['year'] = unhtmlspecialchars($audio['year']);
       
    $audio['country'] = unhtmlspecialchars($audio['country']);
       
    $audio['avroomid'] = unhtmlspecialchars($audio['avroomid']);

       echo 
    '<td>'$audio['name'], '</td>';
       
    // echo $audio[...];
       
    echo '</tr>';
    }
    ?>
    </table>
    Then, only the author is shown once, and the songs are listed out in separate rows alongside and beneath that author.
    My name is Steve, and I'm a super-villian.


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
  •