How do I select distinct values from multiple mysql columns and put them in one PHP array?

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)

Answer 2

SELECT genre1, genre2, genre3 FROM table

Assuming that this comes back as an array of arrays, then:

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);

However, I would not recommend this in a serious application. The database design is bad. Read about database normalization to find out how to improve it.

Answer 3

Unless you've denormalized (2) genres into the three columns for performance reasons, there should be a separate table relating songs and genres:

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;

This does away with the requirement for ("Cross Road Blues" can be filed under "Blues" and "Delta Blues", but that's about it) and artificial limitation of (A3's country acid house gospel comes to mind) three genres per song. If you have a limited set of genres, you might want to make the genre column enumerated. The SongGenres table simplifies getting all genres:

SELECT UNIQUE genre FROM SongGenres;

Alternatively, you could normalize further and create a separate table for genres:

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;

which simplifies getting all genre names even more (though this is only a secondary advantage):

SELECT name FROM Genres;

A primary advantage to a Genres table is data correctness: if someone misspells a genre, it won't be found in the Genres table. A potential disadvantage is that it limits the valid genres to those in the table. Of course, it makes sense to give the user accounts that have INSERT privileges on SongGenres, so this limitation isn't a serious one. Once you start adding new genres, you're faced with the same problem as when there wasn't a Genre tables: typos. Rather than adding new genres that aren't found in the Genres table, look for similar ones (using e.g. the Levenshtein distance or SOUNDS LIKE) and, if any are found, ask the user if they want to replace the genre with one of what was found or keep the original genre (and add it to the genre list).

Here's what the data would look like in the first case (two tables, Songs and 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)

With a separate Genres table, the data in Songs would look the same, but in the other tables we'd have something like:

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)

Answer 4

SELECT DISTINCT genre1, genre2, genre3 FROM tabel

Description

Maybe you need a better database design... songs | genres | [song_id|genre_id]