Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Coder
    Join Date
    May 2009
    Location
    Bangalore
    Posts
    75
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Question How to insert multiple coma separated value in one particular column of DB

    Hi All,

    In one of my table column I need to enter multiple comma separated values.
    I have firstly changed that column type to varchar and then I am trying to enter those values which are in the format like (1,2,4). But it is giving some error.

    I need to enter some system ids in one of the column of Db. The query is something like

    INSERT INTO TableName(request_id, client,category, item, impact,start_date, end_date, summary, service, justification, testing,reason, imp_person, team,otherTeam, change_user)
    VALUES ("CHG", "weras", "asdasd", "asd", "449", "2010-03-24 15:11:15", "2010-03-31 15:11:11", "asdas", "dasdasd", "weqweqw","sadas","", "asd",1,(1,2,4),"444")

    Can anyone help how resolve this issue.

    Thanks in advance

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by Nirbhay View Post
    In one of my table column I need to enter multiple comma separated values.
    Don't. Doing so violates FIRST NORMAL FORM of database normalization. One value to one column.

  • #3
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    I agree with guelphdad... bad idea.

    But... where it's messing up is you must treat the ids as a string therefore the must be enclosed in quotes.

    "asd",1,"1,2,4","444")

  • #4
    Senior Coder DJCMBear's Avatar
    Join Date
    Mar 2010
    Location
    United Kindom
    Posts
    1,173
    Thanks
    14
    Thanked 136 Times in 136 Posts
    Quote Originally Posted by guelphdad
    Don't. Doing so violates FIRST NORMAL FORM of database normalization. One value to one column.
    It is a bad idea I know but there are popular php softwares which store a whole array into a database column cell now if your saying thats wrong then why do they do that for a main stream program?
    Official BinPress hand picked coder.
    For anyone worried about SQL injection go have a look at my small yet powerful script here.
    Go Pledge for Light Table, if it hit's $300,000 Python and other languages will get added.
    I am 1 of 65,608 people to get a Pebble Watch :P

  • #5
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    Quote Originally Posted by DJCMBear View Post
    if your saying thats wrong then why do they do that for a main stream program?
    Because many people take shortcuts, the programmers may have been under time restrictions, they may not have known, and plain and simple they're not as good as we are... hehehe jkn (or all of the above)

    Basically just because main stream programs do something doesn't make it okay. That's like saying because IE doesn't follow web standards than it is right.

  • #6
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    and then there is also the reality that after making sure the db is normalised that, sometimes, a specific part can be de-normalised to make a specific efficiency.

    I don't think this scenario is one of those occasions, however.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #7
    Senior Coder DJCMBear's Avatar
    Join Date
    Mar 2010
    Location
    United Kindom
    Posts
    1,173
    Thanks
    14
    Thanked 136 Times in 136 Posts
    well i see where everyone is coming from but just because your not ment to doesnt mean you cant. so if a person wants to have lets say 1,2 in one cell doesnt mean its not just a string and just to prove you wrong at the bottom of mysql5 it says you can store data in a cell as an array useing the method '1','2','3' so even the developers who make the software say you can.
    Official BinPress hand picked coder.
    For anyone worried about SQL injection go have a look at my small yet powerful script here.
    Go Pledge for Light Table, if it hit's $300,000 Python and other languages will get added.
    I am 1 of 65,608 people to get a Pebble Watch :P

  • #8
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    yep, you can do it. However, if you need to use that array col in a where clause, where you need to find one of the array values, the query wil likely be a pig and very inefficient.

    Best I think to store data like that if, and only if, it will always be used in a clause such as 'in'. And I may even be wrong about that occasion.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #9
    Senior Coder DJCMBear's Avatar
    Join Date
    Mar 2010
    Location
    United Kindom
    Posts
    1,173
    Thanks
    14
    Thanked 136 Times in 136 Posts
    Quote Originally Posted by bazz View Post
    yep, you can do it. However, if you need to use that array col in a where clause, where you need to find one of the array values, the query wil likely be a pig and very inefficient.

    Best I think to store data like that if, and only if, it will always be used in a clause such as 'in'. And I may even be wrong about that occasion.

    bazz
    I was saying it can be done not that it should be done plus your right about the query bit unless theres an id being used and you can call that then send the other col out as an array.
    Official BinPress hand picked coder.
    For anyone worried about SQL injection go have a look at my small yet powerful script here.
    Go Pledge for Light Table, if it hit's $300,000 Python and other languages will get added.
    I am 1 of 65,608 people to get a Pebble Watch :P

  • #10
    New Coder
    Join Date
    Feb 2010
    Location
    ahmedabad
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    don't do like this because i have insert mutiple value in mysql but not successfully extract back to modify or edit profile. so this is not right way. choose another field.
    ---------------------------------------------------
    Search Engine Script - Justdial Clone
    Download PHP Script - Complete Portal Website

  • #11
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    I'm a little confused as to why we are having this discussion... Nobody ever said that it couldn't be done and we all agree it's not the best way to do it.... so?????

  • #12
    Senior Coder DJCMBear's Avatar
    Join Date
    Mar 2010
    Location
    United Kindom
    Posts
    1,173
    Thanks
    14
    Thanked 136 Times in 136 Posts
    Quote Originally Posted by Coyote6 View Post
    I'm a little confused as to why we are having this discussion... Nobody ever said that it couldn't be done and we all agree it's not the best way to do it.... so?????
    I know, I was just answering what was being posted.
    Official BinPress hand picked coder.
    For anyone worried about SQL injection go have a look at my small yet powerful script here.
    Go Pledge for Light Table, if it hit's $300,000 Python and other languages will get added.
    I am 1 of 65,608 people to get a Pebble Watch :P

  • #13
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    storing multiple values in a field CAN be don't, but it SHOULDN'T be done.

    Searching multiple values is difficult enough, but it doesn't scale well and the queries used to extract data become exceedingly difficult.

    you are also left to use either a LIKE '%searchterm%' which can't use an index, to find data in the field, or to use FIND_IN_SET function which works, but is still cumbersome to use.

    I basically tell people to stay away from storing multiple values in a column, because those that do are usually newcomers to RDBMS, don't know about normalization and end up with more problems/headaches based on faulty design.

    Here's something simple to consider as an example.

    Let's have a table of people, a table of hobbies and a table listing people's hobbies:

    Code:
    CREATE TABLE people (
    pid int, 
    name varchar(10)
    );
    
    INSERT INTO people VALUES
    (1, 'Jim'),
    (2, 'Bob'),
    (3, 'Steve'),
    (4, 'Al'),
    (5, 'Glenn');
    
    CREATE TABLE hobbies (
    hid int,
    hobby varchar(25)
    );
    
    INSERT INTO hobbies VALUES
    
    (1, 'Skiing'),
    (2, 'Hockey'),
    (3, 'Swimming'),
    (4, 'Snooker'),
    (5, 'Darts'),
    (6, 'Football'),
    (7, 'Basketball'),
    (8, 'Tennis');
    
    CREATE TABLE normalized (
    pid int,
    hid int);
    
    INSERT INTO normalized VALUES
    (1,1),
    (1,4),
    (1,7),
    (2,1),
    (2,4),
    (2,6),
    (2,7),
    (3,1),
    (4,2),
    (4,4),
    (4,6),
    (4,8),
    (5,1),
    (5,2),
    (5,3),
    (5,4),
    (5,5),
    (5,7),
    (5,8);
    
    CREATE TABLE denormalized (
    pid int,
    hid varchar(40)
    );
    
    INSERT INTO denormalized VALUES
    (1,'1,4,7'),
    (2,'1,4,6,7'),
    (3,'1'),
    (4,'2,4,6,8'),
    (5,'1,2,3,4,5,7,8');
    How would you write queries for the following (note you can only join normalized or denormalized table with the others):

    1) Who likes Basketball?
    2) Does anyone like all hobbies?
    3) I'm Bob, my friend is Glenn, does he share any hobbies with me?
    4) I'm Glenn, I'm looking to find people who share at least three of my interests.
    a) Who are those people?
    b) Which things do we have in common?


    Hint, the queries are easier to write if your data is normalized.
    Hint 2, if instead of 8 hobbies we have 25 and instead of 5 people we have 5000 which table scales more easily the normalized one or the denormalized one?

  • #14
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Quote Originally Posted by DJCMBear View Post
    I know, I was just answering what was being posted.
    hmmm; this seems to be more of a question. lol

    .... if your saying thats wrong then why do they do that for a main stream program?
    and then you followed up with this comment, which seems to assert that there is merit in storing multiple values/arrays in a column.

    and just to prove you wrong at the bottom of mysql5 it says you can store data in a cell as an array
    I am not sure what your point was. In terms of how to follow 'best practice', the correct response should be don't do it, except in extreme, specific circumstances.

    That would be the more relevant/appropriate answer for the OP. Arguments or debates about what can and can't be done, often result in directionless squabbles with no value to the OP or to CF and its readers.


    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #15
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by CM670 View Post
    This only prints the first letter, the DPTdoc but not the second.

    Does anyone have any ideas on how to fix this?
    Indeed they do and they've even answered it in this two year old thread you've resurrected. Don't store multiple values in a single column. Instead normalize your data.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •