i'm front end developer (html/css etc) getting chops stuck end development challenge. please bear in mind i've been doing back-end thing few weeks terminology / logic in infancy... go easy ;)
as starting point, i'm building thing in php & mysql can catalogue vinyl collection. it's simple form takes artist, title, label etc , stores mysql db. want implement tags each record 1 track might tagged 'house | funky | vocal' whilst tagged 'dubstep | mc | bass' or that.
i can't store these tags array in mysql understand need insert them comma separated value , when want work these @ front-end level, need pull csv out of database, explode value , display each element separate list element.
that part understand. how go adding additional tags? can't seem find definitive answer (maybe i'm searching wrong thing?) far, can work out i'd have take current database value of 'tags' db , store variable. delete 'tags' value db, append new tag variable , store new variable database.
surely there's easier way though?
you should study on concept of relation tables , foreign keys. better breaking tags own table, this:
album table
album_id album_artist album_title --------------------------------------------------- 1 nirvana in utero 2 noisia split atom
tags table
album_id tag -------------------------------------------------- 1 rock 1 grunge 1 alternative 2 dubstep 2 drum & bass 2 experimental
then can join them comma delimited list using group concat
select a.album_artist ,a.album_title ,group_concat(b.tag) tags album join tags b using (album_id) album_id = xxx results:
album_artist album_title tags --------------------------------------------------- nirvana in utero rock, grunge, alternative noisia split atom dubstep, drum & bass, experimental
you further break data artists table, album_artist name replaced artist id in artists table, , tag names separated table , tag names replaced tag_ids...
note group_concat specific mysql... pretty sure sql server 2005 , later has similar don't remember call it. using (col_name) mysql specific, easier way of writing on clause on a.album_id = b.album_id , can used when joining columns have same name. prefer using on because shorter , makes complicated queries more readable.
https://en.wikipedia.org/wiki/database_normalization
edit per comment, here if broke tags off own table. added 2 more entries show value repetition - notice how rock, dubstep , experimental used more 1 time text values listed once...
album table: album_id album_artist album_title --------------------------------------------------- 1 nirvana in utero 2 noisia split atom 3 nero welcome reality 4 pink floyd 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) tags album join tags b using (album_id) join album_tags c using (tag_id) album_id = xxx order album_title asc results:
album_artist album_title tags --------------------------------------------------- nirvana in utero rock, grunge, alternative noisia split atom dubstep, drum & bass, experimental pink floyd wall rock, experimental nero welcome reality dubstep
Comments
Post a Comment