How do I select distinct values from multiple mysql columns and put them in one PHP array?

I have a table for songs, where each song can have up to 3 different genres. So in my table, for each song I have column genre1, genre2, and genre3. I'm trying to display all the genres available in a list.

Here's a random example set:

genre1    genre2    genre3
metal     jazz
metal     country   pop
oldies    metal
rap
jazz      hip-hop   choir
choir     metal     jazz

I want the list, done in php, to display in alphabetical order the available distinct genres to choose from. So it should list this:

  • Choir
  • Country
  • Hip-hop
  • Jazz
  • Metal
  • Oldies
  • Pop
  • Rap

All help is appreciated. Perhaps I'm not going about this the smartest way, but I couldn't think of a better way.

Answers


So the separate columns don't make a difference? If that is the case you can use a UNION

SELECT genre1 AS g FROM t UNION SELECT genre2 AS g FROM t UNION SELECT genre3 AS g FROM t

If you have a WHERE clause, then you will need to copy it 3 times, or use an intermediate temporary table

Good luck!


table:

mysql> SELECT genre1, genre2, genre3 FROM music;
+--------+---------+--------+
| genre1 | genre2  | genre3 |
+--------+---------+--------+
| metal  | jazz    |        | 
| metal  | country | pop    | 
| oldies | metal   |        | 
| rap    |         |        | 
| jazz   | hip-hop | choir  | 
| choir  | metal   | jazz   | 
+--------+---------+--------+
6 rows in set (0.00 sec)

grouping:

mysql> SELECT genre1 AS g FROM music UNION ALL
          SELECT genre2 AS g FROM music UNION ALL
          SELECT genre3 AS g FROM music
+---------+
| g       |
+---------+
| metal   | 
| metal   | 
| oldies  | 
| rap     | 
| jazz    | 
| choir   | 
| jazz    | 
| country | 
| metal   | 
|         | 
| hip-hop | 
| metal   | 
|         | 
| pop     | 
|         | 
|         | 
| choir   | 
| jazz    | 
+---------+
18 rows in set (0.00 sec)

count:

mysql> SELECT g, COUNT(*) AS c FROM
             (SELECT genre1 AS g FROM music UNION ALL
              SELECT genre2 AS g FROM music UNION ALL
              SELECT genre3 AS g FROM music)
       AS tg GROUP BY g;
+---------+---+
| g       | c |
+---------+---+
|         | 4 | 
| choir   | 2 | 
| country | 1 | 
| hip-hop | 1 | 
| jazz    | 3 | 
| metal   | 4 | 
| oldies  | 1 | 
| pop     | 1 | 
| rap     | 1 | 
+---------+---+
9 rows in set (0.01 sec)

Need Your Help

What's the name of MS's (ongoing?) embedded (serverless) SQL Server version with stored procedures support?

.net sql-server stored-procedures sql-server-express embedded-database

Some months ago I've read an announce of Microsoft to be going to release an embedded (serverless, linked to an application as a library/assembly and using a simple file to store data) SQL Server v...

Data storage for an iPhone application

ios objective-c iphone storage

I am writing an application that has all the buildings on my campus and in a tableview. When you click a building it will retrieve info on it such as the longitude, latitude, building name, and an ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.