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

Question

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

这里是一个随便举个例子集：

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

我想要的清单，在PHP完成的，按字母顺序显示可用的不同类型可供选择。因此，它应该列出此：

• 合唱
• 国家
• 街舞
• 爵士
• 金属
• 老歌
• 流行
• 说唱

所有帮助表示赞赏。也许我不会以最聪明的方式去做这件事，但我想不出更好的办法。

Answer 1

所以单独列不有所作为？如果是这样的话，你可以使用一个UNION

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

如果你有一个WHERE条款，那么你将需要复制的3倍，或使用中间临时表

祝你好运！

---

表：

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) 

Answer 2

SELECT DISTINCT genre1，genre2，genre3 FROM TABEL

Description

也许你需要更好的数据库设计... songs | genres | [song_id|genre_id]

Answer 3

SELECT genre1, genre2, genre3 FROM table 

假设该共mes作为数组阵列，然后：

function coalesce_into_array($aggregate, $row) { 
    foreach ($row as $genre) { 
     $aggregate[] = $genre; 
    } 

    return $aggregate; 
} 

$data = array_unique(array_reduce($data, 'coalesce_into_array', array())); 
sort($data); 

但是，我不会在一个严重的应用程序中推荐这个。数据库设计不好。阅读有关数据库规范化的信息，了解如何改进它。

Answer 4

除非你denormalized（2）流派到三列出于性能的考虑，应该有相关的歌曲和流派一个单独的表：

CREATE TABLE SongGenres (
    song INT NOT NULL REFERENCES Songs (id) ON DELETE CASCADE, 
    genre VARCHAR(32) NOT NULL, 
    UNIQUE INDEX (song, genre), 
    INDEX genres (genre) -- improves performance for getting genre names 
) Engine=InnoDB; 

这摒弃了要求的（“十字街蓝调“可以在”蓝调“和”三角洲蓝调“下提交，但这就是关于它）和人工限制（A3的乡村酸屋福音浮现在脑海中）每首歌的三种流派。如果您有一套有限的流派，您可能需要制作流派专栏enumerated。该SongGenres表进行了简化让所有流派：

SELECT UNIQUE genre FROM SongGenres; 

另外，还可以进一步规范和创造流派单独的表：

CREATE TABLE Genres (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(32) NOT NULL, 
    UNIQUE INDEX (name) 
) Engine=InnoDB; 

CREATE TABLE SongGenres (
    song INT NOT NULL REFERENCES Songs (id) ON DELETE CASCADE, 
    genre INT NOT NULL REFERENCES Genres (id) ON DELETE RESTRICT, 
    UNIQUE INDEX (song, genre) 
) Engine=InnoDB; 

简化了获取所有种类名甚至更多（尽管这只是一个第二个优点）：

SELECT name FROM Genres; 

到流派表的主要优点是数据正确性：如果有人拼错流派，它不会被在流派发现吨能够。一个潜在的缺点是它将有效的流派限制在表格中的那些流派。当然，给予SongGenres上拥有INSERT权限的用户帐号是有意义的，所以这个限制并不严重。一旦你开始添加新的流派，你将面临与没有流派表的错误相同的问题：打字错误。而不是添加流派表中找不到的新流派，查找类似的流派（例如，使用Levenshtein distance或SOUNDS LIKE），如果找到任何流派，请询问用户是否要用发现的流派或保留原始类型（并将其添加到流派列表中）。

这里的数据是什么样子在第一种情况下（两个表，Songs和SongGenres）：

mysql> SELECT * FROM Songs; 
+----+---------------------+--------+---- 
| id | title    | artist | ... 
+----+---------------------+--------+---- 
| 1 | Cross Road Blues | ... 
| 2 | Peace In the Valley | ... 
+----+---------------------+--------+---- 
2 rows in set (0.00 sec) 

mysql> SELECT * FROM SongGenres; 
+------+-------------+ 
| song | genre  | 
+------+-------------+ 
| 2 | acid  | 
| 1 | blues  | 
| 2 | country  | 
| 1 | delta blues | 
| 2 | gospel  | 
| 2 | house  | 
| 2 | techno  | 
+------+-------------+ 
7 rows in set (0.00 sec) 

mysql> SELECT s.title, sg.genre FROM Songs AS s JOIN SongGenres AS sg ON s.id=sg.song; 
+---------------------+-------------+ 
| title    | genre  | 
+---------------------+-------------+ 
| Cross Road Blues | blues  | 
| Cross Road Blues | delta blues | 
| Peace In the Valley | acid  | 
| Peace In the Valley | country  | 
| Peace In the Valley | gospel  | 
| Peace In the Valley | house  | 
| Peace In the Valley | techno  | 
+---------------------+-------------+ 
7 rows in set (0.00 sec) 

设有一个独立的流派表，在歌曲中的数据会看起来一样，但在其他我们会有类似的表格：

mysql> SELECT * FROM Genres; 
+----+-------------+ 
| id | name  | 
+----+-------------+ 
| 1 | acid  | 
| 2 | blues  | 
| 3 | classical | 
| 4 | country  | 
| 5 | delta blues | 
| 6 | folk  | 
| 7 | gospel  | 
| 8 | hip-hop  | 
| 9 | house  | 
... 
| 18 | techno  | 
+----+-------------+ 
18 rows in set (0.00 sec) 

mysql> SELECT * FROM SongGenres; 
+------+-------+ 
| song | genre | 
+------+-------+ 
| 1 |  2 | 
| 1 |  5 | 
| 2 |  1 | 
| 2 |  4 | 
| 2 |  7 | 
| 2 |  9 | 
| 2 | 18 | 
+------+-------+ 
7 rows in set (0.00 sec) 

mysql> SELECT s.title, g.name AS genre 
    -> FROM Songs AS s 
    ->  JOIN SongGenres AS sg ON s.id=sg.song 
    ->  JOIN Genres AS g ON sg.genre=g.id; 
+---------------------+-------------+ 
| title    | genre  | 
+---------------------+-------------+ 
| Cross Road Blues | blues  | 
| Cross Road Blues | delta blues | 
| Peace In the Valley | acid  | 
| Peace In the Valley | country  | 
| Peace In the Valley | gospel  | 
| Peace In the Valley | house  | 
| Peace In the Valley | techno  | 
+---------------------+-------------+ 
7 rows in set (0.00 sec)