Two tables, connecting the data

[TapeGun007]

Is there a way to check two tables and sort of nest the SQL?

 

This is what I want to do in theory:

$result = mysql_query("SELECT * FROM MusicFiles ORDER BY SongTitle ASC");
        while($row = @mysql_fetch_array($result)){

	$FileID = $row['MusicFiles_ID'];
	echo <all results>;
	$result2 = mysql_query("SELECT * FROM Chords_Charts_Lyrics WHERE MusicFiles_ID ='$FileID'");
        	
	while($row2 = @mysql_fetch_array($result2)){
		echo <all sub related results>;
	}

}

 

The tables are:

Table: MusicFiles

MusicFiles_ID

Type

SongTitle

SingerGroup

AlbumName

 

 

Table: Chords_Loops_Lyrics

CLL_ID

MusicFiles_ID

Type

FileName

 

The idea is that one song may have many other files attached to is... so I want the output to basically be like this:

 

1  | Song  |  Dead Or Alive  | Bon Jovi  |  Best Of Bon Jovi

2  | Song  |  Outlaws        | Bon Jovi  |  Best Of Bon Jovi

              -> Lyrics

              -> Chords

3  | Song  |  Stairway to... | Eagles    |  Classics

[TapeGun007]

Erm...uh... nvm... solved my own problem.

 

This works beautifully... not sure if it's the best method or not, but it works.

$result = mysql_query("SELECT * FROM MusicFiles ORDER BY SongTitle ASC");
        while($row = @mysql_fetch_array($result)){

	$FileID = $row['MusicFiles_ID'];

	echo $row['SongTitle']."<br>";

		$result2 = mysql_query("SELECT * FROM Chords_Loops_Lyrics WHERE MusicFiles_ID='$FileID'");
        	while($row2 = @mysql_fetch_array($result2)){
			echo "->".$row2['Type']."<br>";
		}
	}