i have two tables. artists and reviews.
reviews:
albumid (pk)
artistid (fk)
albumname
artists:
artistid (fk)
artistname
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
correction: artistid in the artists table is the primary key, i
mistyped, sorry
>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.
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
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
nevermind i sorted out a proper mysql statement and i turned up with this
+----------+------------------+---------+------------------- -----+
| artistid | artistname | albumid | atitle |
+----------+------------------+---------+------------------- -----+
| 1 | blah | 1 | blah album |
| 4 | cab | 2 | cab album |
| 3 | dab | 3 | dab album |
| 2 | fab | 4 | fab album |
| 2 | fab | 5 | fab album 2 |
+----------+------------------+---------+------------------- -----+
$oldArtistID = false;
for each row {
if($row['artistid'] != $oldArtistID) {
//it's a new artist, so show their name and stuff
$oldArtistID = $row['artistid'];
}
if you could just give me a description of how $oldartistID comes into
play? i think i understand the rest of it.
for every row it checks artist id against oldartistid and if its new it
lists the artist, if not then it skips it and moves on to where i'd then
place some code that will code each albumrow as a link with a php echo
calling the link with the url parameter. no?
nevermind i understand, it was just my limited knowledge of php that was
confusing me. i did some research and i think i've figured it out.
thanks for your patience and your help, i'll check back if i screw up
again.
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.
>
sorry to be a bother, ive got the recordset called in the page but i
cant seem to sort out the php calls. i get T string errors. going to
check around but i figured it couldnt hurt to ask for some input here.
maybe just a link to a tutorial that goes through step by step?