Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Re: Storing multiple value field in a MySQL database

by davido (Archbishop)
on Nov 25, 2013 at 18:57 UTC ( #1064277=note: print w/replies, xml ) Need Help??

in reply to Storing multiple value field in a MySQL database

Wrong solution; If you have a field that needs to hold more than one value, that field usually deserves a new table:

Table1: userid email age john 45 jerry 22 Table2: userid memberof john admins john wizzards jerry newbies jerry trainees jerry newhires


Replies are listed 'Best First'.
Re^2: Storing multiple value field in a MySQL database
by mba777 (Novice) on Nov 25, 2013 at 19:28 UTC

    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


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

      Table1: userid email age memberof john 45 admins john 45 wizzards jerry 22 newbies jerry 22 trainees jerry 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 45 admins!wizzards jerry 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 45 admins,wizzards jerry 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 ;)

      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.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1064277]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (7)
As of 2018-06-20 20:05 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (117 votes). Check out past polls.