如何从多个mysql列中选择不同的值并将它们放在一个PHP数组中? [英] How do I select distinct values from multiple mysql columns and put them in one PHP array?

问题描述

我有一张歌曲表，其中每首歌曲最多可以有3种不同的流派.因此，在我的表格中，对于每首歌曲，我都有一列genre1，genre2和genre3.我正在尝试在列表中显示所有类型.

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.

这是一个随机示例集:

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

我希望该列表以php完成，以字母顺序显示可供选择的不同流派.因此，它应该列出以下内容:

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

• 唱诗班
• 国家
• 嘻哈
• 爵士
• 金属
• 老歌
• 流行
• 说唱

感谢所有帮助.也许我不是最聪明的方法，但是我想不出更好的方法.

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

推荐答案

所以单独的列没有区别吗?如果是这种情况，您可以使用UNION

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

如果有WHERE子句，则需要将其复制3次，或使用中间的 temporary 表

If you have a WHERE clause, then you will need to copy it 3 times, or use an intermediate temporary 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)

分组:

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)

计数:

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)

这篇关于如何从多个mysql列中选择不同的值并将它们放在一个PHP数组中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！