CODEWITHSUNDEEP
phpmysqlwhile-loop
pcapelo
pcapelo

Reputation: 77

php mysql while - loop while condition is the same

I have a db that have this kind of structure:

name|color

paul|blue
mary|red
paul|green
joe |yellow
paul|purple
mary|orange
paul|white
etc |etc

What am I trying to achieve here is to list the colors associated with a name, something like this:

paul=blue,green,purple,white
mary=red,orange
joe=yellow

I was checking some examples and found this:

$query="SELECT * FROM  mytable order by name";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
    echo $row['name']. " - ". $row['color'];
    echo "<br />";
}

To be honest I just don't know how to go from this to what an I trying to achieve. How can I create a condition that will list all the color associated with one name and then jump to the next and so on?

Upvotes: 2

Views: 255

Answers (3)

pcapelo
pcapelo

Reputation: 77

thank you for pointing me into the right direction. I manage to gather more info and make it work with this: 

$query="SELECT name, GROUP_CONCAT(color) FROM mytable GROUP BY name";
$result = mysql_query($query) or die(mysql_error());

while($row=mysql_fetch_array($result))  
{  
echo "<tr>";  
echo "<td>" . $row['name'] . "</td>";  
echo "<td>" . $row['GROUP_CONCAT(color)'] . "</td>";  
echo "</tr>";  
}

Thank you again :)

Upvotes: -1

Mark Miller
Mark Miller

Reputation: 7447

You need to use GROUP_CONCAT. Try something like this:

SELECT
  name,
  GROUP_CONCAT(color) AS color
FROM mytable
GROUP BY name

See this fiddle - updated

If you need to account for duplicates, use GROUP_CONCAT(DISTINCT color). You can also include a custom SEPARATOR, but if you leave it out the default is ,. So in your case you don't need to specify. Also, as can be seen in the documentation linked above, you can, if desired, order the colors in whichever way you see fit - although, note that the default order is ASC, so you don't need to specify that, either, unless you want to change it.

Upvotes: 5

Casimir et Hippolyte
Casimir et Hippolyte

Reputation: 89584

You can do all with the SQL query:

SELECT name, GROUP_CONCAT(DISTINCT color ORDER BY color DESC SEPARATOR ',')
FROM mytable
GROUP BY name;

Upvotes: 3

Related Questions