如何从多个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屋!