如何从多个mysql列中选择不同的值并将它们放在一个PHP数组中？

Question

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.

Answer 1

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)