While loops within while loops

[unknown101]

Hi Guys,

 

Im just wondering what the best way to achieve a particular result from my sql database is (using my php website).

 

Basically i've only been doing relatively simple queries, occiasionally using a while within a while loop. Now I need to run more than just 2 queries to get my result but im not sure on the best way to go about it (Im guessing these queries can be tidied up into bigger single queries but im learning:)  Say I had the following steps which i needed to achieve:

 

SELECT song_id from ratings where user_id = $USerID;

 

$result (using a while loop to go through all songs)

 

      SELECT Artist from tracks_tbl WHERE song_id = $result_from first_query

 

      $result (again use a while loop to go through all songs to get all artists)

 

                    SELECT Artist FROM artist_tbl WHERE artist=$result_from previous_query AND GenreID=$genreID (This variable has alrdy been  created)

 

So I need the result of each query to run the next one to achieve my final result, but i guess i need to loop through each result so creating a while loop to find the result, but I dont want to keep puting while loops within further while loops.. What is the best way to achive the above without doing that?

 

Thanks in advance

[fenway]

Sounds like you need a three-table JOIN; just guessing which fields you want...

 

SELECT a.artist from ratings as r
INNER JOIN tracks_tbl as t ON ( t.song_id = r.song_id )
INNER JOIN artist_tbl AS a ON ( a.artist = t.artist )
where r.user_id = $USerID AND GenreID=$genreID

[unknown101]

Ok thanks, Ill have a look into JOIN's

 

Just another question, say for example I have the following:

 

$Find_Users = mysql_query("SELECT Users_ID FROM userRatings WHERE Track_ID='$TrackIDreturn' AND Rating > 5");
			    
				  while ($FoundUsers = mysql_fetch_array($Find_Users))
				  {

			           //code...
				  }	
      

 

So now I have all the returned data in the $FoundUsers array, but what's the best way to access all the elements of the array outside the while loop?  Say for example there are 10 User id's within that array and I want to print each one outside of that particular loop above?

 

Thanks in advance

[fenway]

Could you give me an example? Loops and sql don't mix.