Proper displaying of a MySQL query result set

[ShootingBlanks]

Hello -

 

Suppose I had a MySQL result set that was something like this:

ITEM:

COLOR:

ball

red

ball

blue

book

red

book

green

book

black

 

If I were to use the code:

do {
   echo $row_myQuery['item'] . " - " . echo $row_myQuery['color'] . "<br />";
} while ($row_myQuery= mysql_fetch_assoc($myQuery));

 

Then I end up with this:

 

ball - red
ball - blue
book - red
book - green
book - black

 

MY QUESTION:

 

What I'd like to know is, how could I re-write that php code so that I could get an output like this?:

 

ball - red, blue
book - red, green, black

 

Thanks!

[Psycho]

There are many ways to accomplish that. Two come to mind:

 

You can use a variable to act as a "flag" to test when the 'item' changes and only echo the item then. However, you should make sure to ORDER the result set otherwise the items could be duplicated.

 

Another option is to dump the results into a mufti-dimensional array and then use the array to generate the output.

 

I typically go with the first option. But, based on the specific format you show above I would go with the second option.

 

Also, you should use a while() loop instead of a do . . . while() loop. The above would fail if there were no results. Even if THIS situation is always expected to have a result, it's just good practice IMHO.

 

//Dump results in array
$data = array();
while ($row= mysql_fetch_assoc($myQuery))
{
    $data[$row['item']][] = $row['color'];
}
//Output the results
foreach($data as $item => $colors)
{
    echo "{$item} - " . implode(', ', $colors) . "<br>\n";
}

[ShootingBlanks]

Very helpful...thank you!