Complex PHP/MYSQL (for me!)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nicnic
Forum Newbie
Posts: 1
Joined: Thu Nov 09, 2006 10:08 am

Complex PHP/MYSQL (for me!)

Post 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
User avatar
theFool
Forum Newbie
Posts: 17
Joined: Thu Oct 26, 2006 2:00 am
Location: Berlin, DE

Post 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?
mtfoley
Forum Newbie
Posts: 2
Joined: Sat Nov 11, 2006 1:14 pm

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
Post Reply