SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru rockit's Avatar
    Join Date
    Sep 2005
    Location
    Canada
    Posts
    637
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do you nest Queries?

    Table Structure:

    table - category
    category_id, category_name, category_description, category_keywords, category_sort_index

    table - assets
    asset_id, asset_title, asset_keywords, asset_description, category_id (foreign key)

    Desired Output appearing on all one page.

    Category 1
    - Asset 1
    - Asset 2
    - Asset 3

    Category 2
    - Asset 1
    - Asset 2
    - Asset 3
    - Asset 4

    Category 3
    - Asset 1

    Category 4
    - Asset 1
    - Asset 2

    how is this achieved? i know that i need to loop through the categories, but how do i nest those assets under the categories? I know GROUP BY would group them together, but i need all those items in that format to appear under the header. So just curious how i output the data in php?

  2. #2
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT category.category_name, assets.asset_title
    FROM assets
    INNER JOIN category
        ON category.category_id = assets.category_id
    ORDER BY category.category_name, assets.asset_title
    You can then iterate through this result set, and each time the category changes you output that to your user and then continue echoing the asset names.
    PHP questions? RTFM
    MySQL questions? RTFM

  3. #3
    SitePoint Guru rockit's Avatar
    Join Date
    Sep 2005
    Location
    Canada
    Posts
    637
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
        
    while($row $result->fetch()) {
            echo 
    '<h2>'$row['category_name'] . '</h2>';
            echo 
    '<li>'$row['asset_title'] . '</li>';
        }
    ?>
    well the query works fine, just having trouble with the header (category_name) outputs each and everytime. i know i need to assign a variable in here somehow, but can't seem to get it working.

  4. #4
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Almost there.
    PHP Code:
    <?php
        $cur_category 
    "";
        while(
    $row $result->fetch()) {
            if(
    $cur_category != $row['category_name'])
            {
                echo 
    '<h2>'$row['category_name'] . '</h2>';
                
    $cur_category $row['category_name'];
            }
            echo 
    '<li>'$row['asset_title'] . '</li>';
        }
    ?>
    PHP questions? RTFM
    MySQL questions? RTFM

  5. #5
    SitePoint Guru rockit's Avatar
    Join Date
    Sep 2005
    Location
    Canada
    Posts
    637
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sweet. i think the fact i haven't slept in 36 hours has contributed to my IQ on this.

  6. #6
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, understood.
    PHP Code:
    $IQ -= pow($hoursSinceSleep-183); 
    PHP questions? RTFM
    MySQL questions? RTFM


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
  •