Page 1 of 1

help with query, can't get my head around it

Posted: Mon Apr 14, 2008 10:07 pm
by invisibled
Hey everybody,

so i am building a site for a drum company. they want to be able to list shows from the artists they sponsor and group them acordingly (this site is all dynamic btw).

so i have a shows table and an artists table. When you go to add a new show you relate the show to the artist like so

Code: Select all

 
<select name="artist">
<?php
$query = "SELECT artist FROM ida_artists ORDER BY artist ASC";
$mysql = mysql_query($query);
while($d = mysql_fetch_assoc($mysql)):
    print '<option>'.$d['artist'].'</option>';
endwhile;
?>
</select>
 
simply, it loops through and gets all the artists and outputs them in a select so that the show can be related to an artist. My problem is, how can i build a loop that will display all the shows but also add in the band who's playing the show only once... That may be hard to understand so i will show you

ARTIST
-show
-show
-show
-show

ANOTHER ARTIST
-show
-show
-show

does that make sence? any suggestions? need any information just ask. Thanks in advance! :)

Re: help with query, can't get my head around it

Posted: Tue Apr 15, 2008 5:50 pm
by invisibled
Somthing like this:

Code: Select all

    $query = "SELECT * FROM ida_artists";
    $mysql = mysql_query($query);
    while($d = mysql_fetch_assoc($mysql)):
        $group = $d['group'];
        
        print $group.'<br />';
        
        $query = "SELECT * FROM ida_on_tour WHERE artist = '$group'";
        $mysql = mysql_query($query);
        while($d = mysql_fetch_assoc($mysql)):
            print 'On '.$d['date'].' in '.$d['city'].' at '.$d['venue'].'<br />';
        endwhile;
 
    endwhile;
sort of works but it only returns the first band with all there shows.

Re: help with query, can't get my head around it

Posted: Tue Apr 15, 2008 6:46 pm
by John Cartwright
Two schools of thought:

1. Track the last used artist on each iteration of the loop, and compare it to the current artist value. If it goes not match, then we proceed to output the artist name since we are now dealing with a different artist. If the values match, we simply output the data for that artist.

2. My preferred way, since we don't need to have all the entries in order by artists. This involves storing all the records in an array with the artist name as the key.

Code: Select all

 
$records = array();
while ($row = mysql_fetch_assoc($result)) {
   if (!isset($records[$row['artist']])) $records[$row['artist']] = array(); //new artist, initialiate array
   $records[$row['artist']][] = $row; //add the record to the array
}
 
foreach ($record as $artist => $data) {
   echo $artist .'<br>';
   foreach ($data as $row) {
      //each row for that particular aritst
   }
}
 
Enjoy.

Re: help with query, can't get my head around it

Posted: Tue Apr 15, 2008 7:20 pm
by invisibled
ahhhhh THANK YOU. saved me

It seems I need to get into array's and for loops a little better. Cause i would have never though of this!

thanks again!