Professional Web Applications Themes

default value problem. - MySQL

Have created table CREATE TABLE `com_member` ( `dist_no` int(2) unsigned zerofill NOT NULL, `fname` varchar(14) NOT NULL, `lname` varchar(14) NOT NULL, `address1` varchar(30) default NULL, `address2` varchar(30) default NULL, `city` varchar(26) default NULL, `state` varchar(2) NOT NULL default 'WI', `zip` varchar(5) default NULL, `ac` varchar(3) default '920', `phone` varchar(8) default NULL, `email` varchar(50) default NULL, `website` varchar(70) default NULL, `pos` varchar(40) default NULL, PRIMARY KEY (`dist_no`) ) ENGINE=InnoDB DEFAULT CHT=latin1; Then entered data insert into com_member (dist_no, fname, lname, address1, address2, city, state, zip, ac, phone, email, website, pos) values ('01','Adam', 'Somebody','760 Chapel View RR #1',"",'Fish Creek',"",'54301','999','666-9277','com',"",'chairperson'), ('02','Thomas', 'De noone','2535 ...

  1. #1

    Default default value problem.

    Have created table

    CREATE TABLE `com_member` (
    `dist_no` int(2) unsigned zerofill NOT NULL,
    `fname` varchar(14) NOT NULL,
    `lname` varchar(14) NOT NULL,
    `address1` varchar(30) default NULL,
    `address2` varchar(30) default NULL,
    `city` varchar(26) default NULL,
    `state` varchar(2) NOT NULL default 'WI',
    `zip` varchar(5) default NULL,
    `ac` varchar(3) default '920',
    `phone` varchar(8) default NULL,
    `email` varchar(50) default NULL,
    `website` varchar(70) default NULL,
    `pos` varchar(40) default NULL,
    PRIMARY KEY (`dist_no`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    Then entered data

    insert into com_member (dist_no, fname, lname, address1, address2, city,
    state, zip, ac, phone, email, website, pos) values
    ('01','Adam', 'Somebody','760 Chapel View RR #1',"",'Fish
    Creek',"",'54301','999','666-9277','com',"",'chairperson'),
    ('02','Thomas', 'De noone','2535 Delwood
    Drive',"",'Milwaukee',"",'54302','999','999-7803',"","","");

    problem is that the default value for state is not entered. I originally
    used '' but then tried "" and about everything else I could think of.
    Been on google but to this point have not found answer.

    TIA


    sk Guest

  2. #2

    Default Re: default value problem.

    forgot to add that the following insert does work.

    insert into com_member (dist_no, fname, lname) values
    ('01','Adam', 'Eve'),
    ('02','Thomas', 'Jefferson'),
    ('03','Andy', 'Griffith'),


    sk wrote:
     
    sk Guest

  3. #3

    Default Re: default value problem.

    Forgot to add that the following insert does work.

    insert into com_member (dist_no, fname, lname) values
    ('01','Adam', 'Eve'),
    ('02','Thomas', 'Jefferson'),
    ('03','Andy', 'Griffith');

    sk wrote:
     
    sk Guest

  4. #4

    Default Re: default value problem.


    sk wrote: [/ref]
    It seems to me that you have answered it yourself, simply leave the
    state field out of the input field list.

    Captain Guest

  5. #5

    Default Re: default value problem.

    insert using just comma gets me

    insert into com_member (dist_no, fname, lname, address1, address2, city,
    state, zip, ac, phone, email, website, pos) values
    ('01','Adam', 'Somebody','760 Chapel View RR #1','','Fish
    Creek',,,'54301','999','666-9277','com',"",'chairperson'),
    ('02','Thomas', 'De noone','2535 Delwood
    Drive','','Milwaukee',,,'54302','999','999-7803','','','');

    You have an error in your SQL syntax; check the manual that corresponds
    to your MySQL server version for the right syntax to use near
    ','54301','999','666-9277','com',"",'chairperson'),
    ('02','Tho' at line 2

    insert without any reference to state... no , no '' .... get me

    insert into com_member (dist_no, fname, lname, address1, address2, city,
    state, zip, ac, phone, email, website, pos) values
    ('01','Adam', 'Somebody','760 Chapel View RR #1','','Fish
    Creek',,'54301','999','666-9277','com',"",'chairperson'),
    ('02','Thomas', 'De noone','2535 Delwood
    Drive','','Milwaukee',,'54302','999','999-7803','','','');

    You have an error in your SQL syntax; check the manual that corresponds
    to your MySQL server version for the right syntax to use near
    ''54301','999','666-9277','com',"",'chairperson'),
    ('02','Thom' at line 2

    Could you explain please?




    Captain Paralytic wrote: [/ref]
    >
    > It seems to me that you have answered it yourself, simply leave the
    > state field out of the input field list.
    >[/ref]
    sk Guest

  6. #6

    Default Re: default value problem.

    In the one that you posted that you said worked, you had no reference
    to 'state' and no value in the value list for it.

    That is the answer to your question. Do not include it in either the
    list of fields or teh list of values.

    Captain Guest

  7. #7

    Default Re: default value problem.

    insert using just comma gets me

    insert into com_member (dist_no, fname, lname, address1, address2, city,
    state, zip, ac, phone, email, website, pos) values
    ('01','Adam', 'Somebody','760 Chapel View RR #1','','Fish
    Creek',,,'54301','999','666-9277','com',"",'chairperson'),
    ('02','Thomas', 'De noone','2535 Delwood
    Drive','','Milwaukee',,,'54302','999','999-7803','','','');

    You have an error in your SQL syntax; check the manual that corresponds
    to your MySQL server version for the right syntax to use near
    ','54301','999','666-9277','com',"",'chairperson'),
    ('02','Tho' at line 2

    insert without any reference to state... no , no '' .... get me

    insert into com_member (dist_no, fname, lname, address1, address2, city,
    state, zip, ac, phone, email, website, pos) values
    ('01','Adam', 'Somebody','760 Chapel View RR #1','','Fish
    Creek',,'54301','999','666-9277','com',"",'chairperson'),
    ('02','Thomas', 'De noone','2535 Delwood
    Drive','','Milwaukee',,'54302','999','999-7803','','','');

    You have an error in your SQL syntax; check the manual that corresponds
    to your MySQL server version for the right syntax to use near
    ''54301','999','666-9277','com',"",'chairperson'),
    ('02','Thom' at line 2

    Column count doesn't match value count at row 1


    Could you explain please?


    Captain Paralytic wrote: [/ref]
    >
    > It seems to me that you have answered it yourself, simply leave the
    > state field out of the input field list.
    >[/ref]
    sk Guest

  8. #8

    Default Re: default value problem.

    Just figured out what you just stated. However, my problem is obviously
    an understanding of 'default'. I will study manual more but I thought it
    was a item insert into a row if that value was not input by the user.
    Ie... is the user does not input a state value the value would default
    to WI. But if the field is left out of the insert statement then that
    field can not be input so I will always be 'WI' with no options.



    Captain Paralytic wrote:
     
    sk Guest

  9. #9

    Default Re: default value problem.

    >Just figured out what you just stated. However, my problem is obviously 

    If the value of 'state' is mentioned in the field list, a value *WAS*
    specified by the writer of the SQL. SQL does not assume that there's
    a user with a web page form with too-simple-minded substitutions
    being made into the SQL statement.

    There is no:
    INSERT INTO foo(state) VALUES(duh fault);
     

    '' is a *REAL VALUE*. Believe it. Worship it.
    null is a *REAL VALUE*. Believe it. Worship it.


    Gordon Guest

  10. #10

    Default Re: default value problem.

    Gordon Burditt wrote: 

    Hmm...

    CREATE TABLE foo (state CHAR(2) NOT NULL DEFAULT 'WI');
    INSERT INTO foo (state) VALUES (DEFAULT);
    SELECT * FROM foo;

    state
    ----
    WI

    That seems to work fine, at least in MySQL.

    Regards,
    Bill K.
    Bill Guest

  11. #11

    Default Re: default value problem.

    And we have a winner!!! And it ain't you babba!

    Gordon Burditt wrote: 
    >
    >
    > If the value of 'state' is mentioned in the field list, a value *WAS*
    > specified by the writer of the SQL. SQL does not assume that there's
    > a user with a web page form with too-simple-minded substitutions
    > being made into the SQL statement.
    >
    > There is no:
    > INSERT INTO foo(state) VALUES(duh fault);
    >

    >
    >
    > '' is a *REAL VALUE*. Believe it. Worship it.
    > null is a *REAL VALUE*. Believe it. Worship it.
    >
    >[/ref]
    sk Guest

  12. #12

    Default Re: default value problem.

    Thank you Mr. Karwin, you made my day and also proved that some folks on
    this group know what they are talking about.

    Again, thanks.


    Bill Karwin wrote:
     
    >
    >
    > Hmm...
    >
    > CREATE TABLE foo (state CHAR(2) NOT NULL DEFAULT 'WI');
    > INSERT INTO foo (state) VALUES (DEFAULT);
    > SELECT * FROM foo;
    >
    > state
    > ----
    > WI
    >
    > That seems to work fine, at least in MySQL.
    >
    > Regards,
    > Bill K.[/ref]
    sk Guest

  13. #13

    Default Re: default value problem.

    We have a winner Bubba and it ain't you!


    Gordon Burditt wrote:
     
    >
    >
    > If the value of 'state' is mentioned in the field list, a value *WAS*
    > specified by the writer of the SQL. SQL does not assume that there's
    > a user with a web page form with too-simple-minded substitutions
    > being made into the SQL statement.
    >
    > There is no:
    > INSERT INTO foo(state) VALUES(duh fault);
    >

    >
    >
    > '' is a *REAL VALUE*. Believe it. Worship it.
    > null is a *REAL VALUE*. Believe it. Worship it.
    >
    >[/ref]
    sk Guest

  14. #14

    Default Re: default value problem.

    sk wrote: 
    >>
    >>
    >>
    >> If the value of 'state' is mentioned in the field list, a value *WAS*
    >> specified by the writer of the SQL. SQL does not assume that there's
    >> a user with a web page form with too-simple-minded substitutions
    >> being made into the SQL statement.
    >>
    >> There is no:
    >> INSERT INTO foo(state) VALUES(duh fault);
    >>
    >> 
    >>
    >>
    >>
    >> '' is a *REAL VALUE*. Believe it. Worship it.
    >> null is a *REAL VALUE*. Believe it. Worship it.
    >>
    >>[/ref][/ref]

    Also, this is not standard SQL and will not work if you change
    databases. It may ever stop working if MySQL continues to move towards
    more standard SQL and fewer extensions.

    Gordon's answer is the correct one, you sanctimonious twit.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  15. #15

    Default Re: default value problem.

    On Mon, 16 Oct 2006 22:58:19 -0000, Gordon Burditt wrote: 
    >
    > If the value of 'state' is mentioned in the field list, a value *WAS*
    > specified by the writer of the SQL. SQL does not assume that there's
    > a user with a web page form with too-simple-minded substitutions
    > being made into the SQL statement.
    >
    > There is no:
    > INSERT INTO foo(state) VALUES(duh fault);

    >
    > '' is a *REAL VALUE*. Believe it. Worship it.
    > null is a *REAL VALUE*. Believe it. Worship it.[/ref]

    Nope. It's a real result, but not a real value. Beliveing it's a real
    value leads people to things like

    where FOO_DATE > '2005-10-31' and FOO = 'NULL'

    and wondering why they get no hits.

    --
    CS is about lofty design goals and algorithmic optimization. Sysadmining
    is about cleaning up the fscking mess that results.
    Peter Guest

  16. #16

    Default Re: default value problem.

    "Sanctimonious twit"..... ouch. Boy, (and I mean boy) you got me.

    Hey Jerry, you ever say anything like that to someones face? Didn't
    think so.

    Jerry Stuckle wrote: [/ref]
    >
    > Also, this is not standard SQL and will not work if you change
    > databases. It may ever stop working if MySQL continues to move towards
    > more standard SQL and fewer extensions.
    >
    > Gordon's answer is the correct one, you sanctimonious twit.
    >[/ref]
    sk Guest

  17. #17

    Default Re: default value problem.

    sk wrote: [/ref]

    Sure have. And you've once again proven my point.

    Don't even know the difference between top and bottom posting.

    At least I'm not afraid to use my real name.

    And don't call me "boy" - toddler.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Replies: 1
    Last Post: March 30th, 10:03 PM
  2. Problem with default Java File Encoding
    By Bulkis in forum Coldfusion Server Administration
    Replies: 0
    Last Post: November 21st, 10:53 PM
  3. Default job options problem
    By nick_dring@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 2
    Last Post: July 30th, 11:09 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139