Multiple table(s) query, display (2 columns)

[Kyrst]

This currently pulls up a table showing the albums listed under the artist clicked on the previous pages links.

What I want is another column showing all the tracks on corresponding album

Table set up
table (artist) art_id - art_name
table (album) alb_id - alb_name - alb_year - alb_label - alb_img
table (track) track_id - track_title - track_time - track_extra
table (aarelations//artist-album relation) aarel_id - relatedart_id - relatedalb_id
table (atrelation//album-track relation) atrel_id - relatedalb_id - relatedtrack_id

include('../incl/connect.php'); 
$row['art_id']= $_GET['art_id'];
$result = mysql_query("SELECT DISTINCT album.*, artist.*  FROM album, artist, aarelations WHERE artist.art_id = aarelations.relatedart_id AND album.alb_id = aarelations.relatedalb_id AND artist.art_id='$id' ORDER BY alb_year ASC");
echo mysql_error(); 
 if (mysql_num_rows($result)) 
while($row = mysql_fetch_array($result)) { 
$imagelink = $row['alb_img']; 

echo "<div align=center>";
echo "<table width=100% class=outside>";
echo "<tr>";
echo "<td align=left>" . $row['alb_name'] . " <br/>(" . $row['alb_year'] . ")<br/><img src=$imagelink><br/><b>" . $row['alb_label'] . "</b></td>";

echo "<td align=left>" .
<b>[B]Here is where I want track info[/B]
[list]
[*]track 1
[*]track 2
[*]track 3
[/list]  </b></font></td>";
	echo "</tr>";
	echo "</table>";
	echo "</div>";
 }

Not sure how to query that all in one, use a join... and if a join how (or lead me to some examples, searched but had little finding something dealing with a table layout).

Thanks ahead of time 🙂

[ShawnK]

All you gotta do is create a new query, result, etc... but make sure you name it like query2 and result2.

The longest, but easiest to understand is to do 2 connects....2 querys....2 results....and 2 mysql_close()'s

[Kyrst]

include('../incl/connect.php'); 
     echo "<table class=outside width=100%>";
      echo "<tr>";
      echo "<td valign=top width=50%>";
$row['art_id']= $_GET['art_id'];
$result = mysql_query("SELECT DISTINCT album.*, artist.*  FROM album, artist, aarelations WHERE artist.art_id = aarelations.relatedart_id AND album.alb_id = aarelations.relatedalb_id AND artist.art_id='$id' ORDER BY alb_year ASC");
while($row = mysql_fetch_array($result)) { 
$imagelink = $row['alb_img']; 
     echo "<table>";
      echo "<tr>";
 echo "<td align=left>" . $row['alb_name'] . " <br/>(" . $row['alb_year'] . ")<br/><img src=$imagelink><br/><b>" . $row['alb_label'] . "</b></td>"; 
       echo "</tr>";
      echo "</table>";
 }
  echo "</td>"; 
        echo "<td valign=top width=50%>";

$row['alb_id']= $_GET['alb_id'];
      $sub_result = mysql_query("SELECT track.* FROM track,  atrelation WHERE  track.track_id = atrelation.relatedtrack_id AND atrelation.relatedalb_id='$id'");
  while( $sub_row = mysql_fetch_array($sub_result) ) 
     {
     echo "<LI>" . $sub_row['track_title'] . "";
}
 mysql_close ( ); 
 echo "</td>"; 
        echo "<td colspan=2><hr></hr></td>";

   echo "</tr>";
  echo "</table>";

As a note, this is pulling up the data and it looks how I want it so far, just wondering if there is a cleaner way to go about it.

[Kyrst]

Nevermind, it is only pulling up info like this

Album 1
Album Name Track 1
Year Track 2
Image Track 3
Label Track 4

Album 2
Album Name

Year

Image

Label

I have two albums, tracks to these two albums as well as their relative tables with the proper info, in the database by this one artist. It is only pulling the albums but only one set of tracks..

any help would be appreciated.

[Kyrst]

Can anyone tell me what I am doing wrong here?