Jump to content


This topic is now archived and is closed to further replies.


Multiple rows into one line -> problem

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?
[code]//=================================PRODUCT COLORS
(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">
<td colspan="4" class="infoBoxHeading" align="center"><b>Price List</b></td>
$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>";

Share this post

Link to post
Share on other sites
Why not have something like this:

$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";

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.


Share this post

Link to post
Share on other sites


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.