SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast Cabbage's Avatar
    Join Date
    Jun 2001
    Location
    New Jersey
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table join question

    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
    ~Cabbage
    http://www.jimmieschickenshack.net
    want to trade live recordings? JCS, Smashing Pumpkins, Local H, Toadies

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP Code:
    // 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 


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
  •