Re^3: Storing multiple value field in a MySQL database

Why?

davido has illustrated the general layout of the tables for the normal solution. An alternative would be:

Table1:
userid       email              age memberof
john         john@example.com   45   admins  
john         john@example.com   45   wizzards
jerry        jerry@example.com  22   newbies 
jerry        jerry@example.com  22   trainees
jerry        jerry@example.com  22   newhires
[download]

which is obviously dumb because of all the repeated information and the slightly more subtle problem that if jerry's age changes (age does tend to change) you have to fix every 'jerry' row. Or maybe you mean something like:

Table1:
userid       email              age memberof
john         john@example.com   45   admins!wizzards
jerry        jerry@example.com  22   newbies!trainees!newhires
[download]

but then there is no efficient way to search for all newhires.

So, again, why? What is it about davido's solution that you don't like or can't understand?

True laziness is hard work

Comment on Re^3: Storing multiple value field in a MySQL database Select or Download Code

Replies are listed 'Best First'.
Re^4: Storing multiple value field in a MySQL database by mba777 (Novice) on Nov 25, 2013 at 20:02 UTC
Hi, i understand davido's solution, and i know that it is the better solution, but i exactly mean something like that: `Table1: userid email age memberof john john@example.com 45 admins,wizzards jerry jerry@example.com 22 newbies,trainees,newhires` [download] And i do not need an efficient way to search for all newhires. The filed memberof should only be additional information. So can you only show me perhaps an code example how to store multiple value files in one single mysql column. Thanks ;)	[reply] [d/l]
Re^5: Storing multiple value field in a MySQL database by GrandFather (Saint) on Nov 25, 2013 at 20:51 UTC
My hesitation in showing code for simply concatenating the values together with join is that that is such a standard and trivial thing to do that I couldn't believe that that was the solution you were looking for. roboticus's reply++ tells you how to do that and also fills in details for using a table join to use davido's suggested technique. If it really is the case that you were struggling with concatenating a list of strings I strongly advise you show us more of your code and ask for comment in a new Seekers of Perl Wisdom node as there are very likely other areas where your code could use some constructive criticism. True laziness is hard work	[reply]