I'm building a MP3 store for a record label. There are multile Artists who each have multiple Records and each Record has multiple Tracks. I've got everything working except for the checkout page where I want to display the data slightly differently to the rest of the site.
The 4 main tables involved are are:
cart
- cartId (primary key)
- cookieId (session value)
- itemId (joins tracks.id)
tracks
- id (primary key)
- artist_id (relates to artists.id)
- record_id (relates torecords.id)
- number (value)
- name (value)
records
- id (primary key)
- artist_id (relates to artists.id)
- record_name (value)
- released (value)
artists
- id (primary key)
- artists_name (value)
- description (value)
So basically on the checkout page I want to querry the cart table to pull out all the unique track ids a user has selected, from there I can find the artist, record for each track!
I can display the data easily as follows:
Artist Name1
Record Name1
Track1
Artist Name1
Record Name1
Track2
Artist Name1
Record Name2
Track1
(See below for the code I used to do this)
But I would prefer to list as:
Artist Name 1
Record Name 1
- Track 1
- Track 2
Artist Name 1
Record Name 2
- Track 1
- Track 2
Artist Name 2
Record Name 1
- Track 1
- Track 2
I hope that makes sense!!
Below is the querry I used to join the tables and retrieve the data I potentially need. I'm thinking it may be more of a PHP coding issue to get the formatting correct? I do appear to be getting back all the relevant data I need.
Code: Select all
$dsShowCart = mysql_query("
SELECT
cart.itemId,
tracks.number AS track_number,
tracks.name AS track_name,
artists.id AS artist_id,
artists.artist_name AS artist_name,
records.id AS record_id,
records.record_name AS record_name
FROM
cart
Inner Join tracks ON cart.itemId = tracks.id
Inner Join records ON tracks.record_id = records.id
Inner Join artists ON tracks.artist_id = artists.id
where cart.cookieId = '" . GetCartId() . "'
ORDER BY tracks.id ASC
"); Code: Select all
<?php
while($row = mysql_fetch_array($dsShowCart))
{
$number_of_tracks ++;// count tracks
$track_price_1 = $row_dsTrackPrices['track_price_1'];
?>
<?php echo $row["artist_name"]; ?>
<br />
<?php echo $row["record_name"]; ?>
<br />
<?php echo $row["track_name"]; ?>
<br />
£<?php echo $row_dsTrackPrices['track_price_1']; ?>
<?php
}
?>Thanks,
Dominic