a PHP/mySQL display problem.

[creole]

I am trying to get a set of data to display like this:

<table>
<tr>
<td>
Album Title<br>
Release Date: release date
Album Cover
</td>
<td>
List of Songs
<li><a>SONG TITLE</a>
<li><a>SONG TITLE</a>
<li><a>SONG TITLE</a>
etc...
</td>
</tr>
</table>

I basically want a self-containted table setup for each album. This is the query I am running:

SELECT album_list.title, path_to_cover, release_date, song_list.id, song_list.title FROM album_list, song_list

The problem I am having is that when the page loads, it spits out 3 sets of the text "Album Title, Release Date etc" (3 using this example) I only want it to show one album title, one release date and one cover. I know that I have done this before, buti for some reason I cannot get my mind around it right now.

[Son Nguyen]

Check your WHERE part, I don't see it in your query, so I think the condition you use returns result set with 3 rows

Or I misunderstand your problem/situation.

[freddydoesphp]

I think the problem is with your query since you are grabbing Album title, Release date and album cover which will always be one each and the song titles can be multiple items. So since you are doing it all in one query and in this case there was three song titles then it is going to spit out the other data three times as well does that make sense?

What is the relation form song table to album table I think you will want to do two queries here one to get the initial info the second to get the songs form that album, I don't see any other way unless maybe you use an array to stick the song titles into like

$result = mysql_query("SELECT album_list.title as albumtitle, path_to_cover, release_date, song_list.id as songid, song_list.title as songtitle FROM album_list, song_list");

while($row = mysql_fetch_array($result)) {
$albumtitle = $row["albumtitle"];
$release = $row["release_date"];
$cover = $row["cover"];
$songs[] = array("id" => $row["songid"],
"songtitle" => $row["songtitle"]
);
}

Now you can print
$albumtitle, $release and $cover once then loop through the song array like
while(list($key,$val) = each ($songs)) {
printf('<li><a href="somepgae.php?songid=%s">%s</a>', $songs[$key]["songid"], $songs[$key]["songtitle"]);
}

[creole]

I knew I left out something. Here is the description of the tables and their relationship.

ALBUM TABLE
id
title
path_to_cover
release_date


SONG TABLE
id
aid
title
lyrics
path_to_audio

I relate the song list to the album using song_list.aid=album_list.id