CODEWITHSUNDEEP
phpmysql
knard
knard

Reputation: 43

MYSQL select Distinct category and list items under that category

I have one table and in that table there are multiple columns. One column has numerous categories many of them duplicating categories. And another column of elements that belong under their respective categories which also have duplicating elements.

The two columns look like this for example 

categorytype    elements         

Fruits          Grapes
Fruits          Grapes
Vegetables      Carrots
Vegetables      Beets
Vegetables      Beets
Fruits          Apples
Fruits          Oranges
Meats           Beef
Meats           Pork
Meats           Pork
Juices          Orange Juice
Juices          Milk
Juices          GrapeFruit Juice

I want to be able to pull the category distinctly and the other elements distinctly under the category column. So the outputs looks like this:

  Fruits
   Grapes
   Apples
   Oranges

  Vegetables
   Beets
   Carrots 

  Meats
   Beef
   Pork

This is what my MYSQL statement looks like but it only returns the elements distinctly of course. So I'm trying figure out how to pull the categories distinctly as well.

mysql_query("SELECT DISTINCT element FROM fchart GROUP BY element ORDER BY categorytype");

Upvotes: 0

Views: 1330

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can get the list of elements for each category:

select categorytype, group_concat(distinct elements)
from t
group by categorytype;

This returns them as a comma-delimited list.

Upvotes: 2

Related Questions