flannerz Posted April 13, 2008 Share Posted April 13, 2008 Hi Guys I have a problem which I'm sure is pretty simple to solve but I just can't seem to get there myself! Here is the scenario: - First off, I am using an open source digital music downloads system which I am modifying to meet my clients needs. What they want on the music page is something very similar to the Juno website (http://www.juno.co.uk/all/this-week/) As you can see on that link, each artist has multiple tracks underneath them which can be previewed. This is something I am trying to achieve, however I have only been able to get 1 track to appear under the artist. So basically what I want to achieve is: Artist 1 - Track 1 - Track 2 - Track 3 Artist 2 - Track 1 - Track 2 Artist 3 - Track 1 - Track 2 - Track 3 It needs to display the data on one page in a list. I have been fiddling to extend the code below, but just keep getting errors. Below is the code that will let me display one record. I presume the track has to be in some kind of array to get it to display all the tracks belonging to that artist? <?PHP $q_music = mysql_query("SELECT *,".$database['prefix']."albums.id AS a_id FROM ".$database['prefix']."albums, ".$database['prefix']."tracks WHERE ".$database['prefix']."albums.status = '1' AND ".$database['prefix']."albums.id = ".$database['prefix']."tracks.track_album GROUP BY ".$database['prefix']."albums.name ORDER BY ".$database['prefix']."albums.name LIMIT $limitvalue,$limit ") or die(mysql_error()); while ($MUSIC = mysql_fetch_object($q_music)) { $find = array('{url}','{more_info}','{artist}','{album_title}','{tracks}','{image}','{title}'); $replace = array(($SETTINGS->mod_rewrite ? 'mp3-download/'.$MUSIC->a_id.'/'.addTitleToUrl(cleanData($MUSIC->artist)).'/'.addTitleToUrl(cleanData($MUSIC->name)).'.html' : 'index.php?cmd=album&album='.$MUSIC->a_id),$msg_music2,cleanData($MUSIC->artist),cleanData($MUSIC->name),str_replace("{count}",getTrackCount($MUSIC->a_id),$msg_music3),cleanData($MUSIC->image),cleanData($MUSIC->track_name)); $mData .= str_replace($find,$replace, file_get_contents(FOLDER_PATH.'templates/tpl_files/album_data.tpl') ); } ?> The data is then passed to a tpl file (hence why the system uses the replace function) and the following code is used to display the data <div class="albums"> <p class="view_more_info"><img src="{image}" title="{more_info}" alt="{more_info}" width="40" height="40" /></p> <div class="album_title"> <b><a href="{url}">{artist}</a></b><br />{album_title}<br /><i>{title}</i> </div> </div> The table structure for the two tables are as follows: CREATE TABLE mm_albums ( id INT(7) NOT NULL auto_increment, artist VARCHAR(250) NOT NULL default '', name VARCHAR(250) NOT NULL default '', image VARCHAR(250) NOT NULL default '', artwork VARCHAR(250) NOT NULL default '', comments MEDIUMTEXT NOT NULL default '', status ENUM('0','1') NOT NULL default '0', addDate DATE NOT NULL default '0000-00-00', keywords mediumtext not null default '', downloads INT(9) NOT NULL default '0', hits INT UNSIGNED NOT NULL default '0', rss_date VARCHAR(35) NOT NULL default '', PRIMARY KEY(id)) TYPE=MyISAM; CREATE TABLE mm_tracks ( id INT(9) NOT NULL auto_increment, track_name VARCHAR(250) NOT NULL default '', track_album INT(7) NOT NULL default '0', mp3_path VARCHAR(250) NOT NULL default '', preview_path VARCHAR(250) NOT NULL default '', track_length VARCHAR(50) NOT NULL default '', track_cost VARCHAR(5) NOT NULL default '', track_single ENUM('0','1') NOT NULL default '0', addDate DATE NOT NULL default '0000-00-00', track_order INT(9) NOT NULL default '0', downloads INT(9) NOT NULL default '0', PRIMARY KEY(id)) TYPE=MyISAM; The ID on the Albums table = track_album on the tracks table. I hope you understand what I'm trying to achieve and I can't imagine it is too difficult. I just can't for the life of me seem to be able to figure it out. Many thanks Quote Link to comment Share on other sites More sharing options...
Barand Posted April 13, 2008 Share Posted April 13, 2008 GROUP BY album that will give you 1 row per album - remove it. Why are you using "SELECT * " when you only want the album and track names? Quote Link to comment Share on other sites More sharing options...
flannerz Posted April 13, 2008 Author Share Posted April 13, 2008 My mistake before, it's not artists I'm trying to display, but Albums and then the tracks underneath. I think you could see I meant that though. Removing the group by gives the following result: Album 1 - Track 1 Album 1 - Track 2 Album 1 - Track 3 Whereas I want: Album 1 - Track 1 - Track 2 - Track 3 I am not just pulling across that data, also coming across is the ID, album image, artist. Besides this is the default statement that the system uses, a system I haven't developed. All I am doing is modifying it. Any ideas? Thanks Quote Link to comment Share on other sites More sharing options...
Barand Posted April 13, 2008 Share Posted April 13, 2008 prevAlbum = '' while fetch next record does album == prevAlbum? if no output new album name set prevAlbum = album end if output track end while Quote Link to comment Share on other sites More sharing options...
flannerz Posted April 13, 2008 Author Share Posted April 13, 2008 Thanks for that Barand, but I'm unsure how I would integrate that into the code I have already got? Quote Link to comment Share on other sites More sharing options...
flannerz Posted April 13, 2008 Author Share Posted April 13, 2008 $q_music = mysql_query("SELECT *,".$database['prefix']."albums.id AS a_id FROM ".$database['prefix']."albums, ".$database['prefix']."tracks WHERE ".$database['prefix']."albums.status = '1' AND ".$database['prefix']."albums.id = ".$database['prefix']."tracks.track_album ORDER BY ".$database['prefix']."albums.name LIMIT $limitvalue,$limit ") or die(mysql_error()); $prevAlbum = ''; while ($MUSIC = mysql_fetch_object($q_music)) { if (!$MUSIC->a_id == $prevAlbum) { $find = array('{url}','{more_info}','{artist}','{album_title}','{tracks}','{image}'); $replace = array(($SETTINGS->mod_rewrite ? 'mp3-download/'.$MUSIC->a_id.'/'.addTitleToUrl(cleanData($MUSIC->artist)).'/'.addTitleToUrl(cleanData($MUSIC->name)).'.html' : 'index.php?cmd=album&album='.$MUSIC->a_id),$msg_music2,cleanData($MUSIC->artist),cleanData($MUSIC->name),str_replace("{count}",getTrackCount($MUSIC->a_id),$msg_music3),cleanData($MUSIC->image)); $prevAlbum = $MUSIC->a_id; } else { $find = array('{title}'); $replace = array((cleanData($MUSIC->track_name)); } endif $mData .= str_replace($find,$replace, file_get_contents(FOLDER_PATH.'templates/tpl_files/album_data.tpl') ); } I have tried to modify the code to reflect Barand's solution but I can't see to even get it to display anything now. Can anyone see where I am going wrong? Thanks 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.