Group BY with Having or Where clause?

[stryka]

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

[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.

[lieut_data]

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:

$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'];
} 

[stryka]

I tried to do it but got a parse error... this is what is in my code... aside from the select statement...

 
$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... 

}

[lieut_data]

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?

[stryka]

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

$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')."\" );" );

[lieut_data]

I visited the site -- but received no parse error

[stryka]

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);

[lieut_data]

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 $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.