SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Data formatting help please

    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.

    PHP Code:
    PHP
    $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:

    PHP Code:
                <?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

  2. #2
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Welcome to the forums

    PHP Code:
    <?php
    $artistrecords
    =array();

    while(
    $row mysql_fetch_array($dsShowCart))
    {
        
    $number_of_tracks ++;// count tracks
        
    $track_price_1 $row_dsTrackPrices['track_price_1'];
        
        if(!
    in_array($row["artist_name"].$row["record_name"],$artistrecords)){
            echo 
    $row["artist_name"]."<br />";
            echo 
    $row["record_name"]."<br />";    
            
    $artistrecords[]=$row["artist_name"].$row["record_name"];
        }
            
        echo 
    $row["track_name"]."<br />";
                                        
        echo 
    "<br />&pound".$row_dsTrackPrices['track_price_1'];
    }
    ?>
    Saul

  3. #3
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    give this a shot
    PHP Code:
    $groups = array();
    while (
    $row mysql_fetch_assoc($result)) {

        if (!isset(
    $groups[$row['artist_name']])) {
            
    $groups[$row['artist_name']] = array();
        }

        if (!isset(
    $groups[$row['artist_name']][$row['record_name']])) {
            
    $groups[$row['artist_name']][$row['record_name']] = array();
        }

        
    $groups[$row['artist_name']][$row['record_name']][] = $row;
    }


    foreach (
    $groups as $artist_name => $records) {
        foreach (
    $records as $record_name => $tracks) {
            
    printf("%s\n-%s\n"$artist_name$record_name);
            foreach (
    $tracks as $track) {
                
    printf("--%s\n"$track['track_name']);
            }
            echo 
    "\n";
        }

    if you need to sort it php has many array sorting functions that im sure could do what you need, allowing you to sort individual dimensions in specific ways.

    edit- im too slow

  4. #4
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Both worked first time! I'm very impressed that you both totally understood my question not to mention both gave working solutions

    I had an idea arrays would be involved, but just couldn't get my head round it - I need to study this code =)

    Thanks again.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •