Page 1 of 1

Complex PHP/MYSQL (for me!)

Posted: Thu Nov 09, 2006 10:57 am
by nicnic
Hi all this is my first post. I've just started getting into PHP/MySQL after a couple of years of using .NET (urgh)..

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
"); 
And to display I'm using:

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 />
			&pound;<?php echo $row_dsTrackPrices['track_price_1']; ?>					
                            
            <?php
            }
            ?>
I'd really love some help with this please!!

Thanks,
Dominic

Posted: Fri Nov 10, 2006 2:19 am
by theFool
Mhh... I doubt that you can do this just with a single SQL query.
AHave yout hink about restructuring your results in PHP afterwards.

You could just run through the returned results and fill a new array... for example with this structure: array[artist][record][tracks] and then just take this array for the output.

Any other suggestions?

Posted: Sat Nov 11, 2006 1:34 pm
by mtfoley
Try:

Code: Select all

$cookie = GetCartId();
$dsShowCart  = mysql_query("
SELECT artists.name,records.name,GROUP_CONCAT(CONCAT('- ',tracks.name) SEPARATOR '<BR/>')
FROM cart,artists,records,tracks
WHERE
cart.cookieId = $cookie AND
cart.itemId = tracks.id AND 
artists.id = track.artist_id AND
records.id = track.record_id AND
GROUP BY artists.name ORDER BY records.name
");
That should spit records resembling:

Code: Select all

artist 1 | record 1 | - track 1<BR/>- track 2<BR/>- track 3

Posted: Sat Nov 11, 2006 2:08 pm
by timvw
First you have to make sure you get the data back in the right other, in this situtation that would be: order by artist asc, record asc, track asc

And then you would need to keep two variables to keep track which the previous artist and record were... Every record you check if they are still the same... If not, you start a new record (or artist) area...

Code: Select all

$artist = '';
$record = '';
$track = '';

$row = mysql_fetch_assoc($result);
while ($row) {
  echo "<artist>";

 while ($row && $row['artist'] == $artist) {
       echo "<record>";
  while ($row && $row['artist'] == $artist && $row['record'] == $record) {
         echo "<track/>";
 
         $row = mysql_fetch_assoc($result);
         $artist = $row['artist'];
         $record = $row['record'];
         $track = $row['track'];
  }
       echo "</record>";
 }

  echo "</artist>";
}
I would recommend that you consult a book on basic algorithms... I'm sure that you'll learn lots of other different appraoches too...

Posted: Sat Nov 11, 2006 4:02 pm
by califdon
mtfoley has the solution, I believe. What you say you want is exactly what GROUP BY does in a SQL statement. Look up GROUP BY in a SQL tutorial.