Resolved Three Table query sort

[rgEffects]

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.

[Ctechinfo]

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

[TFlan]

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>';
}
?>

[hinch]

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

[rgEffects]

Except for three missing ';' TFlan got it. Thanks very much. I was missing the second part of the loop...

[TFlan]

Apologies, wrote that on the fly.. didn't see those. Fixed my post.

Happy to help