SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru laflair13's Avatar
    Join Date
    Nov 2004
    Location
    TN
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can I do this with MYSQL

    Hopefully I can explain this right.

    Right now I have a Category column in my table. Well I am wanting to take the "Category" from varchar to ENUM so I can change the values easily.

    Can I do that without losing any data?

    What I have right now is the Category column has comments but the comments wont let me add anything in it.

    You can see what I mean from the screenshot below.

    Thanks In Advance

    David

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by laflair13 View Post
    Well I am wanting to take the "Category" from varchar to ENUM so I can change the values easily.
    my advice is to stay well clear of ENUM

    i doubt you can make the change easily in any case


    Quote Originally Posted by laflair13 View Post
    What I have right now is the Category column has comments but the comments wont let me add anything in it.
    that's not a mysql problem, though

    VARCHAR can take anything

    i would check your application code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru laflair13's Avatar
    Join Date
    Nov 2004
    Location
    TN
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, Much appreciated. Any ideas of how I can add like 3 more items to the comments? I can to a SQL Query but will that mess up the existing data?
    Thanks In Advance

    David

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm not sure i understand what you mean by "3 more items"

    your category column can hold as many items as you can fit into 2000 characters

    oh, wait a sec... you're talking about the column comments!

    column comments are strictly for documentation purposes

    perhaps you could explain a bit more about the table that your category column is in, and how it is to be used?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru laflair13's Avatar
    Join Date
    Nov 2004
    Location
    TN
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a site where I add products through an admin panel. The "comments" are the categories that I use for a drop down. I need to add 3 more categories to that, But this is all I can see when I go to add it.

    Code:
    'Automation-Controls','Baggers','Bagger-Scales','Bins','Cappers','Case-Erectors','Carton-Formers','Check-Weighers','Complete-Lines','Confectionary-Equipment','Continuous-Mixers','Control-Panels','Conveyors','Cookie-Lines','Cooling-Tunnels','Cutting-Slitti
    Thanks In Advance

    David

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    those are the comments of the category column, correct?

    they have no bearing on what values the column can hold

    what you should do is create a categories table, that has one row for every allowable category value

    then tie this to your items table with a foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru laflair13's Avatar
    Join Date
    Nov 2004
    Location
    TN
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know this is a late reply, But is there any tutorial that will show me how to do that?
    Thanks In Advance

    David

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    CREATE TABLE categories
    ( category VARCHAR(937) NOT NULL PRIMARY KEY
    );
    
    INSERT INTO categories VALUES
     ( 'Automation-Controls' ) 
    ,( 'Baggers' ) 
    ,( 'Bagger-Scales' ) 
    ,( 'Bins' ) 
    ,( 'Cappers' ) 
    ,( 'Case-Erectors' ) 
    ,( 'Carton-Formers' ) 
    ,( 'Check-Weighers' ) 
    ,( 'Complete-Lines' ) 
    ,( 'Confectionary-Equipment' ) 
    ,( 'Continuous-Mixers' ) 
    ,( 'Control-Panels' ) 
    ,( 'Conveyors' ) 
    ,( 'Cookie-Lines' ) 
    ,( 'Cooling-Tunnels' ) 
    ,( 'Cutting-Slitting ' )
    ;
    
    ALTER TABLE new_equip
    ADD CONSTRAINT cat_fk
        FOREIGN KEY ( category )
        REFERENCES categories ( category ) 
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru laflair13's Avatar
    Join Date
    Nov 2004
    Location
    TN
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I appreciate that. My issue is I have everything going into one table. "new_equip" and in that table I have a column called Category and thats where all the categories are being stored. I have no clue how to edit it with using tables ad not messing up the site.

    I have attached a screenshot to help it make more sense.

    v4xeo1.png
    Thanks In Advance

    David

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    did you try running the code i gave you? what happened?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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