hyperlinking mysql results in php to drill in deeper

if i click on artist, open a new page showing the different albums

[youfoundjake]

Here is my current code:

$result = mysql_query("
SELECT
name AS artist_name
FROM `".$database_table_prefix."artist`
ORDER BY `name` ASC
") or die(mysql_error());
$grid = new grid;
$grid->columns = array(
'Artist' => 'artist_name'
);

Now this pull's the artist's name from the database.
What I would like to do next, is on the output of 'artist_name' is to be able to see the albums listed under that artist.

Ideally, all components should be linkable, album, artist, song
so that if i click on a song, it shows the artist and album its on, or if i click on an album, it shows the artist and what songs are on it.

Kind of a big project eh? would it best suit me to have include files that do all the queries?

[d40sithui]

well, its not too hard i dont think to list the albums, songs when you click on the artist or vice-versa. you just need the right table construct. I'm picturing about 3 tables: Album, Artist, Song. If you just tie their IDs together, you'll be all set.

[youfoundjake]

yup, 3 tables i have, what i need is to make each result linkable to the child or grandparent of the parent query

[d40sithui]

hmm, are you sure you need a parent query? I was thinking more on the lines of a simpler system, one without any children or grandparents. what if you just change the query on each page. if you're on the artist page, you select his/her songs and albums using the artist ID from the URL(typically). if you're on the album page, you select the artist name and other songs on this album ID (again provided by the URL). if you're on the song page, change your query to select the artist and other albums by the artist.

[youfoundjake]

heeh, ya, big project, artist names in URL? lol, someday I'll get there. Right now, I've got just a basic php setup, nothing fancy, pulling data from a mysql database full of itunes library.xml data. I'm having to learn php, mysql, and xml all in this little project of mine. So i'm grasping at huge concepts.

[d40sithui]

well, what i suggest is very simple even if you're starting out.
You just need to pass the artist Id, song ID, or album ID via the URL. Retrieve it using $_GET. and query your db using this value.

1.) Exampl
//current page
www.yoursite.com/index.php?artistId=10
//retrieving artist id
$artistId = $_GET['artistId']; //don't forget to properly clean this variable.
//quering for songs by this artist
$query = "select songId, songName from songs where artistId=$artistId";
//query for albums by artist
$query2 = "select albumId, albumName from album where artistId=$artistId";
//all thats left is to run the query and display the data

I've never played with xml stuff, so i wouldnt know what to do with that portion. but this is one basic way how you would form your queries and display data on your page.

[youfoundjake]

cool, let me try that out, thanks alot..