CODEWITHSUNDEEP
phpmysqldatabasetags
MrQwest
MrQwest

Reputation: 13

Appending tags to a row in a MySQL db

I'm generally a front end developer (HTML/CSS etc) but currently getting my chops stuck into some back end development for a challenge. Please bear in mind I've only been doing this back-end thing for a few weeks so my terminology / logic is in its infancy... Go easy ;)

As a starting point, I'm building a thing in PHP & MySQL so I can catalogue my vinyl collection. It's a really simple form that takes artist, title, label etc and stores that into a MySQL db. I also want to implement tags for each record so one track might be tagged 'House | funky | vocal' whilst another would be tagged 'dubstep | MC | Bass' or something like that.

I can't store these tags as an array in MySQL so I understand that I need to insert them as a comma separated value and then when I want to work with these at a front-end level, I need to pull the CSV out of the database, EXPLODE that value and display each element as a separate list element.

That part I understand. But how do I go about adding additional tags? I can't seem to find a definitive answer (maybe I'm searching the wrong thing?) but so far, all I can work out is that I'd have to take the current database value of 'tags' from the db and store that as a variable. Delete the 'tags' value from the db, append a new tag to the variable and then store the new variable back into the database.

Surely there's an easier way though?

Upvotes: 1

Views: 2148

Answers (1)

WebChemist
WebChemist

Reputation: 4411

You should study up on the concept of relation tables and foreign keys. You would be better breaking up the tags into its own table, something like this:

Album table

album_id  album_artist    album_title
---------------------------------------------------
1         Nirvana         In Utero
2         Noisia          Split the Atom

Tags table

album_id  tag
--------------------------------------------------
1         Rock
1         Grunge
1         Alternative
2         Dubstep
2         Drum & Bass
2         Experimental

Then you can join them back into a comma delimited list using GROUP CONCAT

SELECT       a.album_artist
            ,a.album_title
            ,GROUP_CONCAT(b.tag) AS tags
FROM        Album a
JOIN        Tags b
USING       (album_id)
WHERE       album_id = xxx

results:

album_artist    album_title       tags
---------------------------------------------------
Nirvana         In Utero          Rock, Grunge, Alternative
Noisia          Split the Atom    Dubstep, Drum & Bass, Experimental

You could further break up the data into an artists table, where the album_artist name is replaced by the artist id in the artists table, and the tag names are separated into another table and the tag names are replaced with the tag_ids...

Note that GROUP_CONCAT is specific to MySQL... Pretty sure SQL SERVER 2005 and later has something similar but I don't remember what they call it. Also USING (col_name) is MySQL specific, its just an easier way of writing an ON clause like ON a.album_id = b.album_id and can only be used when the joining columns have the same name. I just prefer USING to ON because its shorter and makes complicated queries more readable.

https://en.wikipedia.org/wiki/Database_normalization

Edit Per your comment, here is what it would look like if you broke tags off into its own table. I added 2 more entries to show value repetition - notice how Rock, Dubstep and Experimental are used more than 1 time but their text values are only listed once...

Album table:

album_id  album_artist        album_title
---------------------------------------------------
1     Nirvana                 In Utero
2     Noisia                  Split the Atom
3     Nero                    Welcome Reality
4     Pink Floyd              The Wall

Tags table:

tag_id    tag
--------------------------------------------------
1         Rock
2         Grunge
3         Alternative
4         Dubstep
5         Drum & Bass
6         Experimental

Album_Tags table:

album_id  tag_id
--------------------------------------------------
1         1
1         2
1         3
2         4
2         5
2         6
3         4
4         1
4         6

Query:

SELECT       a.album_artist
            ,a.album_title
            GROUP CONCAT(c.tag) AS tags
FROM        Album a
JOIN        Tags b
USING       (album_id)
JOIN        Album_Tags c
USING       (tag_id)
WHERE       album_id = xxx
ORDER BY    album_title ASC

Results:

album_artist  album_title         tags
---------------------------------------------------
Nirvana       In Utero            Rock, Grunge, Alternative
Noisia        Split the Atom      Dubstep, Drum & Bass, Experimental
Pink Floyd    The Wall            Rock, Experimental
Nero          Welcome Reality     Dubstep

Upvotes: 2

Related Questions