|
|
Posted by Jim Hernandez on 11/12/05 05:03
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.
>
i tried your method and mysql returned some errors, i dont think i
understand where you got the "a." and "r." from.
the tables in my database are actually called areviews and artists if
that helps, just used the examples before for simplicity's sake
Navigation:
[Reply to this message]
|