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.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Jan 2007
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    yes/no data type

    Does this exist in mySQL?

  • #2
    New to the CF scene
    Join Date
    Apr 2007
    Location
    United States
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Use the field type "enum" with two options being specified as 'y' and 'n'. So, in a sense, yes, the yes/no data type is supported.

    But the values in an enum field don't HAVE to be 'y' and 'n'. It can be 't' & 'f' (true/false), or anything else your imagination takes you.

    Hope this helps,
    David

  • #3
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Although ENUM does exist, you are better off setting it to a TINYINT(1) UNSIGNED field, using 1=true/yes and 0=false/no. Smaller table size and (slightly) faster indexes.

    ENUM data type: http://dev.mysql.com/doc/refman/5.0/en/enum.html

    TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT data types: http://dev.mysql.com/doc/refman/5.0/...ric-types.html

    HTH!

  • #4
    Regular Coder
    Join Date
    Jun 2004
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts
    indeed,

    and IF you use ENUM, don't use values like y or n or things like that, use 1 and 0 or true and false

  • #5
    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
    Don't bother using ENUM, use tinyint as suggested. There are no real benefits from using ENUM and there are many flaws with it.

    If you set up ENUM to use 'T' and 'F' for instance you can insert 'Scribble' if you want. It doesn't get stored as T/F or scribble, nor does it get stored as a NULL.

  • #6
    New Coder
    Join Date
    Jan 2007
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank for the replies, I've set it up as

    `Garden` tinyint(1) NOT NULL COMMENT 'Option: YES/No',
    `Garage` tinyint(1) NOT NULL COMMENT 'Option: YES/No',
    `Driveway` tinyint(1) NOT NULL COMMENT 'Option: YES/No',

    Which i assume is correct.

    I then converted an access database to MySQL for one of my friends, and as he already had values in these 3 fields, it has come up using 1 and 0 instead of YES or No..does this matter? Obviously Yes/No would be preferred for when i echo these fields onto a php page.

    I'm kind of new to MySQL and I'm used to using constraints for Yes/No data types
    Last edited by ClammyDoo; 04-11-2007 at 08:43 PM.

  • #7
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Your table definition is a little off. You cannot have a NOT NULL value without setting the DEFAULT.

    `Garden` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Option: YES/No',
    `Garage` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Option: YES/No',
    `Driveway` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Option: YES/No',

    I set your DEFAULT = 1 because you have YES in uppercase. Set the DEFAULT = 0 for a NO value.

    I am assuming that you are using phpMyAdmin, as it would have set your DEFAULT = '' rather than give you an error.

    When converting MSAccess to MySQL, Access sets Yes/No = 1/0. TINYINTs will only store integers, not string values 'Yes' and 'No'.

    As to your PHP echo, just use

    IF(value == 0) echo 'No'
    IF(value == 1) echo 'Yes'

    *Not a PHP programmer, please use the correct syntax in your app.

    HTH!

  • #8
    New Coder
    Join Date
    Jan 2007
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I see what you mean, thanks.

    The uppercase YES is only in the comment though so that doesn't affect anything.

    Kinda annoying tinyints don't store string values

  • #9
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    TINYINT = Tiny Integer, -64 to 64 or UNSIGNED (not negative), 0 to 127.

    That's why it's called that. If you want to use 'Yes' and 'No', then you would be better off changing the fields to VARCHAR(3)

    VARCHAR = Variable Character, length of 3.

    Differences is that TINYINT can have an index, making searches faster, while VARCHAR(3) cannot, as indexes on fields must have a minimum length of 4.

    Believe it or not, Access stores YES/NO as TINYINT(1) UNSIGNED, but the front-end client shows you YES/NO. It's just a display thing.

  • #10
    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 Daemonspyre View Post
    TINYINT = Tiny Integer, -64 to 64 or UNSIGNED (not negative), 0 to 127.
    Actually unsigned tinyint holds values of 0 to 255 and signed values of -128 to 127. Not sure where you got those other values from.

  • #11
    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 ClammyDoo View Post
    Kinda annoying tinyints don't store string values
    TINYINT is a numeric storage type, hence it doesn't store strings. Take a look at the various data types in the MySQL resources thread at the top of this forum.

  • #12
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Not thinking. That's where I got it from. I had 127 as the max stuck in my head.

  • #13
    Regular Coder ralph l mayo's Avatar
    Join Date
    Nov 2005
    Posts
    951
    Thanks
    1
    Thanked 31 Times in 29 Posts
    ENUM('Yes', 'No') is a better solution to this problem. MySQL itself uses ENUM('Y', 'N') internally. It's stored numerically internally and takes 1 byte total to store and index, just like TINYINT, and it lets you name the values like the OP seems to want. It's more obviously boolean so its meaning is more discoverable.


  •  

    Posting Permissions

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