I guess You could write Your query with JOIN statements to
to get all information by one single query, not having to store
things in PHP arrays.
a simpe one could look like this...
/* depending on what criteria is beeing used.... */
$match = "artist.name = $submitted_artist";
$match = "songs.name = $submitted_song";
/* axs = artists_x_songs */
$SQL = "SELECT artists.name, songs.name FROM artists, songs, axs WHERE ";
$SQL .= "artists.id=axs.artist AND songs.id=axs.song AND $match";
I'm sorry but I haven't time to test this in practice but i'm 97% that it
works... ofcource You have to modify the query to match Your DB layout
=d0Mi=
----- Original Message -----
From: "Matthew Smith" <[EMAIL PROTECTED]>
To: "mysql" <[EMAIL PROTECTED]>
Sent: Thursday, April 18, 2002 8:04 AM
Subject: Re: [MANY to MANY] relationship with MySQL ???
> 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)){
> > file://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
>
>
---------------------------------------------------------------------
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
