mr_badger Posted December 14, 2007 Share Posted December 14, 2007 I have a site that has an index page with a list of music artists, when you click on an artist it takes you to albumdetail.php with the title of the album, artwork and tracklisting, this all works fine but I want to have on this page a section that has the artists other albums that this artist has done. You then click on another album and that page opens, I just can't seem to figure out how to make this work. I have only 2 mysql tables in php admin: Album table and Artist table. <?php require_once('Connections/albums.php'); ?> <?php $maxRows_getAlbum = 10; $pageNum_getAlbum = 0; if (isset($_GET['pageNum_getAlbum'])) { $pageNum_getAlbum = $_GET['pageNum_getAlbum']; } $startRow_getAlbum = $pageNum_getAlbum * $maxRows_getAlbum; mysql_select_db($database_albums, $albums); $query_getAlbum = "SELECT * FROM artist ORDER BY artist.artist_id"; $query_limit_getAlbum = sprintf("%s LIMIT %d, %d", $query_getAlbum, $startRow_getAlbum, $maxRows_getAlbum); $getAlbum = mysql_query($query_limit_getAlbum, $albums) or die(mysql_error()); $row_getAlbum = mysql_fetch_assoc($getAlbum); if (isset($_GET['totalRows_getAlbum'])) { $totalRows_getAlbum = $_GET['totalRows_getAlbum']; } else { $all_getAlbum = mysql_query($query_getAlbum); $totalRows_getAlbum = mysql_num_rows($all_getAlbum); } $totalPages_getAlbum = ceil($totalRows_getAlbum/$maxRows_getAlbum)-1; ?> <body> <div id="artist"> <table width="250" border="0" cellspacing="5" cellpadding="2"> <?php do { ?> <tr> <td><a href="artist_detail.php?album_id=<?php echo $row_getAlbum['album_id']; ?>"><?php echo $row_getAlbum['artist']; ?></a></td> </tr> <?php } while ($row_getAlbum = mysql_fetch_assoc($getAlbum)); ?> </table> <br /> </div> </body> </html> <?php mysql_free_result($getAlbum); ?> Sorry if this isn't enough information. Quote Link to comment Share on other sites More sharing options...
Cep Posted December 14, 2007 Share Posted December 14, 2007 Why not on your second page, just query your database and return the result of all albums by the artist and create the same type of links as you have in your original page. Quote Link to comment Share on other sites More sharing options...
adam291086 Posted December 14, 2007 Share Posted December 14, 2007 Well you are getting the album info from the url right? Then you are querying your database on this information. Why do you then find out the artist of the album and create a new query searching the albums for the same artist. Then echo out those albums. Whats your table structure? Quote Link to comment Share on other sites More sharing options...
mr_badger Posted December 14, 2007 Author Share Posted December 14, 2007 Thanks for replying, I have tried to query the albums but only get the same album that is shown originally. Obviously I'm not querying the database right, just need help with that bit. My table structure is this: Table 1 is called Album and has - album_id, artist_id, album_name, album_year, album_image, album_tracklist Table 2 is called artist and has - album_id, artist_id, artist Hope this helps. Quote Link to comment Share on other sites More sharing options...
mr_badger Posted December 15, 2007 Author Share Posted December 15, 2007 can anyone help with this? Quote Link to comment Share on other sites More sharing options...
tippy_102 Posted December 15, 2007 Share Posted December 15, 2007 SELECT album_name, artist_id FROM album WHERE artist_id = $selected_artist_id Quote Link to comment Share on other sites More sharing options...
mr_badger Posted December 16, 2007 Author Share Posted December 16, 2007 thankyou for replying, I will try this out. Quote Link to comment Share on other sites More sharing options...
mr_badger Posted December 16, 2007 Author Share Posted December 16, 2007 Still not getting this to work right. Now I have on my album index page a list of all the music artists, but if an artists has say 3 albums then the artist is listed 3 times with the differing album_id linked. I only want the artist listed once and then when you click on the artist it will show all the albums the artist has produced. the mysql query for the index page is: SELECT DISTINCT artist.artist, artist.album_id, artist.artist_id FROM artist ORDER BY artist.artist_id I then need to know what to put on the artist_detail page to show all related albums to that one artist? Quote Link to comment Share on other sites More sharing options...
mr_badger Posted December 17, 2007 Author Share Posted December 17, 2007 This is enough info to get an answer?? Quote Link to comment Share on other sites More sharing options...
Cep Posted December 17, 2007 Share Posted December 17, 2007 If you want the Artist ID to appear once in your result then use a GROUP command in your statement. Quote Link to comment Share on other sites More sharing options...
mr_badger Posted December 17, 2007 Author Share Posted December 17, 2007 Thankyou, using the GROUP command worked, I just need to know how to list the other albums on the artistdetail.php page. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.