Jump to content


Photo

Multiple rows into one line -> problem


  • Please log in to reply
2 replies to this topic

#1 greg

greg
  • Members
  • PipPipPip
  • Advanced Member
  • 49 posts

Posted 26 September 2006 - 02:53 PM

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
//=================================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>";
}
}


#2 greg

greg
  • Members
  • PipPipPip
  • Advanced Member
  • 49 posts

Posted 27 September 2006 - 01:33 AM

Please, can anyone help me on this?
Thanks


#3 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 27 September 2006 - 12:59 PM

Why not have something like this:

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

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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users