Combining tables as a product list

[Rick Gerard]

I've been at this for about 4 days now. It's should be simple but I can't figure out how to display the data from a product table without duplicates. Here's a simplified description of what I've got.

Products table:

id	Product
1	extra large shirt
2	large shirt
3	medium shirt
4	small shirt

Colors Table

id	Color
1	red
2	white
3	blue

Available Colors table

id	productID	ColorID
1	1	1
2	1	2
3	1	3
4	2	1
5	2	3
6	3	2
7	4	1
8	4	2

Combining matching up the shirts and the colors is easy with a simple WHEN product.id = Available.productID AND color.id = Available.colorID

I get this result from the table:

Shirts:

• Extra Large Shirt
  • Red
• Extra Large Shirt
  • White
• Extra large Shirt
  • Blue
• Large Shirt
  • Red
• Large Shirt
  • Blue
• Medium Shirt
  • White
• Small Shirt
  • Red
• Small Shirt
  • Blue

Everything is correct but the display is awful as a list or a table. What I want is to eliminate the duplicate listing of the Shirt Type and end up with a clean list like this:

• Extra Large Shirt
  • Red
  • White
  • Blue
• Large Shirt
  • White
  • Blue
• Medium Shirt
  • White
• Small Shirt
  • Red
  • Blue

I've tried left join, unique, and if statements for hours. I've searched all over the web and I can't figure out how to get this code to work correctly.

Here's some code that produces the messy list:

<?php
echo '<ul>'; 
$sql = "SELECT *
FROM products, colors, available
WHERE products.id = available.productID
AND color.id = available.colorID
";
     $result = mysql_query($sql);
     echo '<ul>';
          if (mysql_num_rows($result) > 0) {
               while ($rec = mysql_fetch_assoc($result)) {
          echo '<li>', $rec['product'], 
          '<ul><li>', $rec['color'],'</ul></li>';
     }
}
echo '</ul>';
?>

[Rob_Hecker2]

Have you tried SELECT DISTINCT

[Rick Gerard]

Distinct will only return one value. It's got to be in the PHP loop. I'm working on that now. Still haven't got it.

[bregent]

PHP is not my strong suit, but try this:

<?php

echo '<ul>';

$sql = "SELECT *

FROM products, colors, available

WHERE products.id = available.productID

AND color.id = available.colorID";

     $result = mysql_query($sql);

     $last_product = ""

     echo '<ul>';

          if (mysql_num_rows($result) > 0) {

               while ($rec = mysql_fetch_assoc($result)) {

              if ($last_product <> $rec['product']){

            echo '<li>', $rec['product'];

        }

          echo '<ul><li>', $rec['color'],'</ul></li>';

    $last_product = $rec['product'];

     }

}

echo '</ul>';

?>

[Rick Gerard]

That was prety close. Here's the actual code that works. The tables are obviously different.

<?php 
$sql = "SELECT
pvtDocLog.id, pvtDocLog.title,
bizCats.bcName,
docBizCats.bizCatID, docBizCats.assignedDocID
FROM pvtDocLog, bizCats, docBizCats
WHERE bizCats.id = docBizCats.bizCatID
AND pvtDocLog.id = docBizCats.assignedDocID
ORDER BY bizCats.id, pvtDocLog.id
";
$result = mysql_query($sql); 
if(is_resource($result)){ 
    $results = array(); 
    while($rec = mysql_fetch_assoc($result)) { 
        if(array_key_exists($rec['bcName'], $results)){ 
            array_push($results[$rec['bcName']], $rec['title']); 
        }else{ 
            $results[$rec['bcName']] = array($rec['title']); 
        } 
    } 
    echo '<ul>'; 
    foreach($results as $class => $names){ 
        echo '<li>'.$class.'<ul>'; 
        foreach($names as $name){ 
            echo '<li>'.$name.'</li>'; 
        } 
        echo '</ul></li>'; 
    } 
    echo '</ul>'; 
} 
?>      

[bregent]

>I've tried left join, unique, and if statements for hours. I've searched all over

>the web and I can't figure out how to get this code to work correctly.

You're looking in the wrong place   The problem has nothing to do with your SQL. DISTINCT, LEFT or other modifications to your SELECT won't solve your problem - you are misunderstanding what they do. Your original SQL is fine.

You need to solve this in your PHP code. You can modify the code so it only prints the type the first time. Quite simply, in your loop you need to store the value of the ShirtType in a variable. Compare the value of this variable to the current record value and then supress the printing of the Type when the type has not changed since the last iteration.