PHP/MySQL - Displaying Data From Two Tables

[flannerz]

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

[Barand]

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?

[flannerz]

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

[Barand]

prevAlbum = ''
while fetch next record
     does album == prevAlbum?
     if no
         output new album name
         set prevAlbum = album
     end if
     output track
end while

[flannerz]

Thanks for that Barand, but I'm unsure how I would integrate that into the code I have already got?

[flannerz]

$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