Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

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

by mba777 (Novice)
on Nov 25, 2013 at 19:28 UTC ( #1064280=note: print w/ replies, xml ) Need Help??


in reply to Re: Storing multiple value field in a MySQL database
in thread Storing multiple value field in a MySQL database

Hi Dave, that's clear but i really want all values of the multi value field in one mysql column, is this not possible?

Thanks Andy


Comment on Re^2: Storing multiple value field in a MySQL database
Re^3: Storing multiple value field in a MySQL database
by GrandFather (Cardinal) on Nov 25, 2013 at 19:46 UTC

    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

    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

    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

      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

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

        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
Re^3: Storing multiple value field in a MySQL database
by davido (Archbishop) on Nov 25, 2013 at 19:52 UTC

    Of course you can create one VARCHAR(512), or whatever field, cram a bunch of comma-separated-values into it, and parse them out later. You can do that. But that's not an effective use of your database. It prevents you from easily searching in-database for all users who are a member of "wizzards". Instead you will have to use "LIKE" clauses, which are less efficient, or slurp all records into your script and do your own processing.

    SQL-style databases usually just use another table whenever a developer is tempted to cram more than one item into a given field. There's good reason for that.


    Dave

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1064280]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (6)
As of 2014-12-22 03:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (110 votes), past polls