Help with formatting data

[mclamais]

I have data in a table like

 

 

prod_id     Color
1      Blue
1      Red
2      Tan
3      Red
3      Black

 

And I need it to look like this

 

prod_id     Color
1      Blue, Red
2      Tan
3      Red, Black

 

So how can I format the data so that I have one prod_id, with the one or more associated color in a comma separate list in a single field.

 

Does anyone know how I can create a PHP page to query the database, and dump the data to a format like the one above.

 

Or if this can be done directly in MySQL that would be great.

 

 

[pdkv2]

use the query below

 

select prod_id,GROUP_CONCAT(Color)from emp GROUP BY prod_id;

 

Cheers

Sharad

[Aureole]

It doesn't matter how you put something into the Database, a text area a text input or whatever...

 

You can format the result though, if that's what you're asking - I'll show you.

 

Go set prod_id #1 to "Red, Blue, Green" for color, then do this...

 

<?php
// Connect to Database here...

$query = "SELECT * from `table` WHERE `prod_id` = '1'";
$result = mysql_query( $query );
$fetch = mysql_fetch_array( $result );

$id = $fetch['prod_id'];
$color = explode( ',', $fetch['color'] );

echo( $id . ' is available in the following colors: ');
echo( '<ul>' );
foreach( $color as $a => $b )
{
   echo( '<li><span style="color:' . strtolower( $b ) . ';">' . $b . '</span></li>' );
}
echo( '<ul>' );
?>

 

I'm just showing you that you can play with the commar-seperated list in the Database and format it in different ways, once you've retrieved it.

 

As for how you get it into the Database, it could be a lot of different ways and it wouldn't really matter... unless I misunderstood your question?