Jump to content

Multiple rows into one line -> problem


greg

Recommended Posts

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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.