table join question

[Cabbage]

I have a NON database driven page at
http://www.jimmieschickenshack.net/audio.php

I have database driven page that I want to look exactly like the above page
http://www.jimmieschickenshack.net/mp3test2.php

Know I know I have to do a table join but I'm not sure which one or what the correct SQL would be for it. The problem is that the venue/city/state/date get printed for every mp3. I only want the venue/city/state/date to be printed once whether it has 1, 5, or 12 mp3's for that show.

Here is the SELECT statement I'm using now for mp3test2.php

$sql = "SELECT mp3_id, mp3_link, mp3_info, venue_name, city_name, state_name, date_tour, song_title FROM mp3, tourhistory th, venue v, city ci, state s, date d, songlist WHERE mp3.tourhistory_id = th.tourhistory_id AND mp3.song_id = songlist.song_id AND v.venue_id = th.venue_id AND ci.city_id = th.city_id AND s.state_id = th.state_id AND d.date_id = th.date_id";

If you can offer me any help it would be much appreciated.

Thanks!
~Cabbage

[freakysid]

I think that you should stick with the query you have and handle what you want in your php code. In the code below, I have included an ORDER BY clause to your query as it is necessary for the logic of the code that follows. Note that the variables I have used to determine a distinct gig may not match your business rules - they may need to be adjusted to suit - but the logic should meet your needs.

// note I have added an ORDER BY clause to your sql
// which is important to the php code that follows
// - note the conditions in the if statement!

$sql = "SELECT mp3_id, mp3_link, mp3_info, venue_name,
               city_name, state_name, date_tour, song_title 
        FROM   mp3, tourhistory th, venue v, city ci, state s, 
               date d, songlist 
        WHERE mp3.tourhistory_id = th.tourhistory_id 
        AND mp3.song_id = songlist.song_id 
        AND v.venue_id = th.venue_id 
        AND ci.city_id = th.city_id 
        AND s.state_id = th.state_id 
        AND d.date_id = th.date_id
        ORDER BY venue_name, city_name, date_tour"; 

$result = mysql_query($sql);

while ( $row = mysql_fetch_array($result) ) 
{
   // use extract to get each associative element of $row
   // into its own variable - I love this function
   extract($row);
   
   // now test to see whether this current row has the 
   // same values for venue_name/city_name/date_tour
   // as the previous row had
   if ( $venue_name == $prev_venue_name &&
        $city_name == $prev_city_name &&
        $date_tour == $prev_date_tour )
   {
      // this record is for the same gig as the last one
      // so no need to print out the venue and date details
      // just print out the mps details
   }
   else
   {
      // this row is for a different gig from the last one
      // so what we need to do is to copy this row's values
      // into the prev_ variables
      // and of course print out the new gig details as well
      // as the mp3 details
      $prev_venue_name = $venue_name;
      $prev_city_name = $city_name;
      $prev_date_tour = $date_tour;
   }
} // end while