Posted by Jim Hernandez on 11/12/05 03:59

ZeldorBlat wrote:
>>I want to implement data from these tables into a php page that lists
>>artists alphabetically and underneath each artist lists the albums that
>>they have reviews for.
>>e.g.,: blah band
>> -the blah bands greatest hits
>> -blah blah blah
>>
>>Any help is appreciated
>
>
> Your SQL will probably look something like this:
>
> select a.artistid, a.artistname, r.albumid, r.albumname
> from artist a
> left outer join review r
> on a.artistid = r.artistid
>
> Note the left outer join. I'm guessing (perhaps incorrectly) that you
> want to show all artists, even if they don't have reviews. If you want
> only those with reviews, change that to just 'join'.
>
> Now that you have your results, you need to do a little bit of work in
> PHP to make them look the way you want them to. What I typically do is
> something like this (semi-pseudocode) :
>
> $oldArtistID = false;
> for each row {
> if($row['artistid'] != $oldArtistID) {
> //it's a new artist, so show their name and stuff
> $oldArtistID = $row['artistid'];
> }
>
> //On each subsequent pass for the same artistid, we'll skip
> //the part above and just jump here:
>
> //Now show the link to the review from $row['albumid']
> }
>
> That should get you what you want.
>
> Also, to be picky for a minute (this *is* alt.php.sql, isn't it?) you
> shouldn't be storing albumname in the reviews table -- it belongs in
> the albums table. Same goes for reviews.artistid -- unless you want to
> allow multiple artists per album in which case you should have an
> artistAlbum table with a artistid, albumid.
>
first off thanks for taking the time out to help, im going to try your
method in a couple of hours. its greatly appreciated.
secondly i think i see what you mean about my db setup and thanks for
brirnging it up, learning process innit.

this is actually my first time working with mysql and php so im still
learning stuff about db design and normalization, etc. the reason i
have albumname on the review table primarily because each album is only
going to have one review. so all the information that has the potential
for a one to many (i.e., a year can have more than one album released in
it, and we can review more than one album from said year, and an artist
can have more than one album that we can review) has its own table,
while the information thats pretty much tied to the albumid (because one
album is only going to have one review) is in the reviews table and will
be called by albumid.

if you have any tips or recommendations dont hesitate to share im all
ears, i hope i explained my setup clearly enough. and thanks again

[Back to original message]