2

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?

Improve this question
1
  • you can use group by for grouping by name. Commented May 17, 2014 at 2:17

3 Answers 3

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hi Mark, thank you for your insight, apparently I am not good enough. could you tell me what am I missing here: $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 $row['name']. " - ". $row['color']; echo "<br />"; }
@pcapelo You need to add AS color to the GROUP_CONCAT select in order to use the variable $row['color']. See my updated answer - that should fix the problem. Also, it's none of my business but you should consider using mysqli or pdo instead of mysql_* - it is depreciated.
thank you again I will defenitely search into that, I still learning. Thanks again
3

You can do all with the SQL query:

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

Comments

-1

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 :)

Improve this answer

7 Comments

That works, yes, but is not necessary. Simply add AS color to the select statement and you can call the column by that name - color instead of GROUP_CONCAT(color)
If one of the answers works for you, you should accept it and not copy it into a new answer.
I don't get it. what I did was to put everything together, sorry you didn't like it. Marks was credited and I post his contribution and the rest I was missing to make it work.
Technically, my answer has not been credited until you click the check mark next to it. Jeroen is simply pointing out that instead of accepting my answer, you posted your own containing a nearly identical solution.
Mark, I am sorry then, I did post your answer with the extra part that worked for me because I wanted to have everything in one place, and yes I have marked your answer as well, my apologies on that.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.