Hi Robbie
Reading what I wrote wasn't very clear to me either ;-)
> How does song_x_artist reference the other databases? Is there a way to set
> up a field to always be a reference to another table?
I think you may be talking foreign keys here - something yet to come in
MySQL AFAIK.
>
> I am not quite sure what you mean... but here is my take anyways. Is this
> going around my elbow? Or is this just how you do relationals in SQL? ....
>
This is a standard way of doing many-to-many relationships in SQL; there
may be others.
> (For each relationship you may want to have in a system, you must set up a
> table to house that relationship?)
For each M2M, basically, yes.
> Table::"song_x_artist"
> *********************
> PrimID (can't duplicate)
> SongID (can duplicate)
> ArtistID (can duplicate)
Yep - I don't use the primary key when I do it, but by using one you can
obviously avoid unwanted duplications.
>
> (PHP) & MySQL
> *********************
> $songID = (the id of the song the user selected to sample)
> $artistsOnSong = Select * from "song_x_artist" where "songID" = $songID
> while ($artistArray= mySQL_Fetch_Array ($artistsOnSong)){
> $currentArtists = Select * from "Artists" where artistID =
> $artistArray[ArtistID]
> while ($artistInfo= mySQL_Fetch_Array ($currentArtists)){
> //start to build the html table of the related artists for the song
> here.
> }
> }
>
> Would that work? And if it does... I am not so sure about how to write it
> out, so if you have any suggestions on better syntax, please advise.
'Ang on - I'm not a PHP man so I've got to think my way through the
code... Yep - if I read that correctly, should be OK. Assuming that
we're starting with a song, the steps are:
1) Grab the song ID (primary key) from the songs table.
2) Select all the Artist IDs from the song_x_artist (x-reference) table
and push them into an array. (This is why we want sub-selects in MySQL 4!)
3) Pop each value from the array and use it as the criterion to select
all the details that you want from the artists table - what comes back
is your final results.
So, yes, what you've done looks right. Only one way to find out ;-)
Cheers
Matthew Smith
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
