Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts

    Three Table query sort

    I have 3 simple tables linking information. Lets call them product, category and assignment. These tables tie information together. For example a set of data like this:

    product
    id, name
    1, small toy
    2, middle sized toy
    3, big toy

    category
    id, class
    1, boy
    2, girl

    assignment
    id, productID, ClassID
    1, 1, 1
    2, 2, 2
    3, 2, 1
    4, 3, 2

    The result here is that the big toy is for girls, the middle sized toy is for a boy or a girl, and the small toy is for a boy.

    No I can't figure out how to generate a list that doesn't end up with duplicates. I'm getting this:
    • boy
      • small toy
    • boy
      • middle sized toy
    • girl
      • big toy
    • girl
      • middle sized toy



    What I want is this:
    • boy
      • small toy
      • middle sized toy
    • girl
      • big toy
      • middle sized toy


    Here's the code:
    PHP Code:

    <?php
    $sql 
    "SELECT *
    FROM product, category, assignment
    WHERE class.id = assignment.classID
    AND product.id = assignment.productID
    ORDER BY category.id
    "

         
    $result mysql_query($sql);
         echo 
    '<ul>';
        if (
    mysql_num_rows($result) > 0) {
            while (
    $rec mysql_fetch_assoc($result)) {
                echo 
    '<li />' $rec['class'], ' <ul><li />'$rec['name'],
                            
    '<ul>';
            }
        }
        echo 
    '</ul>';
    }
    ?>
    I've been looking for a way to keep the category from repeating that still allows the product to repeat. Any ideas would be appreciated.
    Last edited by rgEffects; 01-08-2013 at 06:50 PM. Reason: Problem Solved

  • #2
    Regular Coder
    Join Date
    Sep 2012
    Posts
    100
    Thanks
    5
    Thanked 3 Times in 3 Posts
    bear in mind I am a MySQL newb.. But I believe you have to joing tables to do the search as you are trying to do..

    http://dev.mysql.com/doc/refman/5.0/en/join.html

  • #3
    New Coder
    Join Date
    Dec 2012
    Location
    USA
    Posts
    82
    Thanks
    3
    Thanked 17 Times in 17 Posts
    I haven't tested this, but I hope, if it doesn't work canned, that you get the idea

    PHP Code:
    <?php
    $sql 
    "SELECT * FROM product, category, assignment WHERE class.id = assignment.classID AND product.id = assignment.productID ORDER BY category.id"
    $result mysql_query($sql);
    if(
    is_resource($result)){
        
    $results = array();
        while(
    $rec mysql_fetch_assoc($result)) {
            if(
    array_key_exists($rec['class'], $results)){
                
    array_push($results[$rec['class']], $rec['name']);
            }else{
                
    $results[$rec['class']] = array($rec['name']);
            }
        }
        echo 
    '<ul>';
        foreach(
    $results as $class => $names){
            echo 
    '<li>'.$class.'<ul>';
            foreach(
    $names as $name){
                echo 
    '<li>'.$name.'</li>';
            }
            echo 
    '</ul></li>';
        }
        echo 
    '</ul>';
    }
    ?>
    Last edited by TFlan; 01-08-2013 at 07:01 PM.

  • Users who have thanked TFlan for this post:

    rgEffects (01-08-2013)

  • #4
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    SELECT * FROM category OUTER JOIN assignment ON category.id=assignment.ClassID OUTER JOIN product ON assignment.productID=product.ID GROUP BY category.id

    something like that i don't really ahve time to run up a test
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #5
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    Except for three missing ';' TFlan got it. Thanks very much. I was missing the second part of the loop...

  • #6
    New Coder
    Join Date
    Dec 2012
    Location
    USA
    Posts
    82
    Thanks
    3
    Thanked 17 Times in 17 Posts
    Apologies, wrote that on the fly.. didn't see those. Fixed my post.

    Happy to help

  • Users who have thanked TFlan for this post:

    rgEffects (01-08-2013)


  •  

    Posting Permissions

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