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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

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

Post 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! :)
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

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

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

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

Post 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.
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

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

Post 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!
Post Reply