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 ...