in a page called albums.php you would have:
SELECT * FROM albums
You then iterate the query and display the album data with a link something like:
echo "<a href=songs.php?id=" . $record["album_id"] . ">View Songs</a>";
In the page songs.php you run another SQL statement which gets all the songs for the given album. ie:
"SELECT * FROM songs WHERE song_album_id=" . $_GET["id"];
Iterate that and you get a list of songs. You will probably also want to display more information about the album, so you would also have:
"SELECT * FROM albums WHERE album_id=" . $_GET["id"];
To get the album record and maybe, display more information about the making of the album, or maybe an album image. etc etc.
Hope that helps.
You can also attempt a JOIN between songs and albums, that would only be required if you were showing say, all songs from a given year, or artist. So then for each song you would want the album name.
SELECT * FROM songs LEFT JOIN albums ON songs.song_album_id = albums .album_id;
Note: I know this isnt correct PHP code. Im assuming you know how to run SQL queries in PHP and then iterate the results. I tend to run all SQL in a function that returns a PHP array, which I then iterate. It all depends on what you beleive to be "correct" coding practices.
Something like this works for me:
function SQLDataSet($sql)
{
$result = array();
$query = mysql_query($sql);
while($row = mysql_fetch_array($query)) if (is_array($row)) $result[] = $row;
mysql_free_result($query);
return $result;
}
foreach (SQLDataSet("SELECT * FROM albums") as $record)
{
echo .... << Insert code to display fields
}
