|
|
Posted by ZeldorBlat on 11/12/05 03:14
>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.
Navigation:
[Reply to this message]
|