Multiple rows into one line -> problem

[greg]

Hello Everyone,
The following code works good, only it doesn't make things the way I wanted.
Let me explain. The table product_colors contains the colors for each product. Because most products has more than one color, there is several records for each product_id in this table. I'm trying to get all color_names for each product into one single line but this code makes a separate row for each color. It looks like:

product_id      color
105                red
105                blue
105                black
106                yellow
106                black ......

And I want this:

105                red, blue, black
106                yellow, black, .....

Could anyone help me on this please?
Thanks
Greg
[code]//=================================PRODUCT COLORS
elseif
(isset($HTTP_GET_VARS['product_color']) && tep_not_null($HTTP_GET_VARS['product_color'])) {
      $keywords = tep_db_input(tep_db_prepare_input($HTTP_GET_VARS['product_color']));
      $ProductModel = " and p.products_model like '%" . $keywords . "%'";
   
?>
<table width="670" class="infoBoxContents" BORDER="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4" class="infoBoxHeading" align="center"><b>Price List</b></td>
</tr>
<?
$ProductPrice_query = "SELECT p.products_id, p.products_date_added, p.products_model, pd.products_name, p.products_quantity_order_min,
p.products_discount4_qty, pc.products_id, pc.color_name FROM products p LEFT JOIN products_colors pc on p.products_id=pc.products_id LEFT JOIN
products_description pd ON pc.products_id = pd.products_id WHERE p.products_date_added < '2006-01-01 01:01:05' and pd.language_id = '" . (int)
$languages_id . "'" . $ProductModel . "ORDER by p.products_id DESC";
    $ProductPrice_query = tep_db_query($ProductPrice_query);
    while ($ProductPrice = tep_db_fetch_array($ProductPrice_query))
{
echo "<table width='670' border='0'>";
echo "<tr class='infoBoxContent' valign='top'>";
echo "<td width='12%' align='left'>&nbsp".$ProductPrice["products_model"]."</td>\n";
            echo "<td width='250' align='left'>&nbsp".$ProductPrice["products_name"]."&nbsp</td>\n";
echo "<td width='5' align='left'>&nbsp".$ProductPrice["products_id"]."&nbsp</td>\n";
echo "<td align='center' width='100' align='left'><b>&nbsp".$ProductPrice["products_quantity_order_min"]."&nbsp</b></td>\n";
echo "<td align='center' width='10%' valign='top'><b>&nbsp".$ProductPrice["color_name"]."</b></td>\n";
echo "<td align='center' width='10%'><b>&nbsp".$ProductPrice["color_name"]."</b></td>\n";
echo "<td align='center' width='10%'><b>&nbsp".$ProductPrice["p.products_date_added"]."</b></td>\n";
echo "<td align='center' width='10%'><b>&nbsp".$ProductPrice["products_discount4_qty"]."</b></td>\n";
            echo "</tr>\n";
echo "</table>";
}
}[/code]

[greg]

Please, can anyone help me on this?
Thanks

[HuggieBear]

Why not have something like this:

[code]
<?php
$sql = "SELECT product_id, color FROM product_colors ORDER BY product_id, color";
$result = mysql_query($sql);
$product = null;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  $product_id = $row['product_id'];
  if (is_null($product) || strcmp($product, $product_id) !=0){
      $product = $product_id;
      echo "<br>$product_id<br>\n";
  }
  echo "$row['color']<br>\n";
}
?>
[/code]

I've used the same column headings as in your table, so you should just be able to provide your database connect string and give it a whirl.

Regards
Huggie