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 12 of 12
Like Tree1Likes
  • 1 Post By tangoforce

Thread: Storing 3-state choice

  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts

    Storing 3-state choice

    I have a drop-down menu on a form that can either go unanswered, or be set to "Yes" or "No".

    What are the best values to associate with each state?

    Originally, I wanted to do this...
    Code:
    Dropdown	Database
    Value		Value
    ---------	---------
    --		NULL
    No		0
    Yes		1

    What I am most worried about is whether distinguishing between NULL (No Value Chosen) and "0" ("No") would cause problems in my PHP...


    Of course, I could do this...
    Code:
    Dropdown	Database
    Value		Value
    ---------	---------
    --		1
    No		2
    Yes		3

    But that is "non-standard" and I'd lose the benefits of doing BOOLEAN type logic with the default 0/1...


    Any thoughts or suggestions?

    Sincerely,


    Debbie

  • #2
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,439
    Thanks
    13
    Thanked 360 Times in 356 Posts
    What I am most worried about is whether distinguishing between NULL (No Value Chosen) and "0" ("No") would cause problems in my PHP
    that depends on how you test in PHP. if you use strict (in)equality, there should be no problems.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #3
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Dormilich View Post
    that depends on how you test in PHP. if you use strict (in)equality, there should be no problems.
    First of all, what do you think about the two options I presented above?

    What are your thoughts on each approach, and which would chose?

    Sincerely,


    Debbie

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,118
    Thanks
    80
    Thanked 4,555 Times in 4,519 Posts
    Why not just use an ENUM field in which you choose (and store) ' ' or 'Yes' or 'No'? Then you can transfer the value to the HTML page without manipulation.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Why not just use an ENUM field in which you choose (and store) ' ' or 'Yes' or 'No'? Then you can transfer the value to the HTML page without manipulation.
    First, because most database people I know say that ENUM's are evil and should be avoided. (I'm surprised you would recommend such a thing?!)

    Secondly, whether or not I use ENUM, I still have the same issues that I am asking about above, i.e. "Does having to discern between a NULL and 0 create more issues than it solves?"

    That is a PHP question and not a database one.

    Sincerely,


    Debbie

  • #6
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,439
    Thanks
    13
    Thanked 360 Times in 356 Posts
    Quote Originally Posted by doubledee View Post
    First of all, what do you think about the two options I presented above?
    Quote Originally Posted by doubledee View Post
    That is a PHP question and not a database one.
    the options you presented are database options, so they do not really apply to a PHP question.

    the answer I can give is that which option you should choose depends on how reliably you can convert the MySQL values into PHP values.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #7
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Dormilich View Post
    the options you presented are database options, so they do not really apply to a PHP question.

    the answer I can give is that which option you should choose depends on how reliably you can convert the MySQL values into PHP values.
    And that was my point...

    From a database standpoint, I think {NULL, 0, 1} works just fine.

    However, I was wondering if I might kick myself down the road when I have to discern between a NULL and a 0.

    For example, if I had this code...
    PHP Code:
    if (empty($agree)){ 
    PHP would treat both a NULL and a 0 as "empty", right?

    Sincerely,


    Debbie

  • #8
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Why not just use an ENUM field in which you choose (and store) ' ' or 'Yes' or 'No'? Then you can transfer the value to the HTML page without manipulation.
    FWIW...

    8 Reasons Why MySQL's ENUM Data Type Is Evil


    Or just Google...

    "Why is ENUM evil"





    Debbie

  • #9
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    And on that page, scroll down to and read "Criteria for when it might be okay to use enum:"

    All of which criteria apply in this situation.

    Would a separate lookup table be superior? Perhaps. And we could even argue that it's the best answer. But you don't seem to like to proliferate tables, so I thought ENUM a reasonable alternative in these circumstances.

    I think it's probably time I quite replying to you. We seem to do nothing but get into arguments. I will remove this post sometime later today.
    Whose arguing?

    I said above that most people I know think ENUMs are evil, and so I was surprised you would recommend it, since you don't seem to be one to cut corners in the database world.

    You are also correct that there are times where ENUM could be used, and this might be one of those times.

    FWIW, you seem to be sensitive and edgy these days... (Maybe the stress of being ill for so long?)


    I always thought we had a really nice, friendly relationship - even when we do debate certain things.

    Sorry you feel the way you do lately...

    Sincerely,


    Debbie

  • #10
    Regular Coder
    Join Date
    Aug 2006
    Location
    Richmond, CA
    Posts
    212
    Thanks
    3
    Thanked 11 Times in 10 Posts
    Can you revert to a NULL?

    That is, is there a case where a user will select Yes (or No) and later on be able to change it to NULL? Or is this a one-time write?

  • #11
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,429
    Thanks
    62
    Thanked 535 Times in 522 Posts
    Quote Originally Posted by doubledee View Post
    Whose arguing?
    You as usual.

    Quote Originally Posted by doubledee View Post
    I said above that most people I know think ENUMs are evil, and so I was surprised you would recommend it, since you don't seem to be one to cut corners in the database world.
    In fairness, I do agree that enums are a pain and while they do certainly make the job easier, they can make upgrades and database structures a pain to change at a later date. I suppose you already knew that though and just wanted to post another topic to stir up some action.

    Quote Originally Posted by doubledee View Post
    You are also correct that there are times where ENUM could be used, and this might be one of those times.
    So why not just get on with it? Why do you have to question every little step of your development only to turn around and tell us all you knew better all along? - Pointless.

    Quote Originally Posted by doubledee View Post
    FWIW, you seem to be sensitive and edgy these days... (Maybe the stress of being ill for so long?)
    I think thats a bit unfair. You post many topcs asking for advice on very basic issues, then you refuse to accept the answers given to you, bang on about how your a systems analyst by trade (so you *MUST* be right) and then start saying how healthy debates are. Why post a request for HELP if you really want a DEBATE? - There is a difference, you know it, we know it and you continue to do it under the innocent guise of needing help.

    Quote Originally Posted by doubledee View Post
    I always thought we had a really nice, friendly relationship - even when we do debate certain things.
    If you want to debate something, you need to make it clear that it is a debate and NOT a request for help so that those who do not wish to defend their answers are not drawn into an unwanted spat. You may have noticed very few people reply to you these days because you always challenge the answers they give you and then claim that it's a debate. This is unacceptable, anti sociable and frustrating for many people here who donate their time.

    To put it bluntly, you appear to ask for help. People then give you answers and then you turn around and challenge them claiming to know a better answer that you've obviously been sitting on all along. Then once you've upset more people you respond by claiming it is a debate. It's winding people up.

    Quote Originally Posted by doubledee View Post
    Sorry you feel the way you do lately...
    You seem to end up saying things like this to quite a lot of people on this forum lately. I wonder why?
    itxtme likes this.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #12
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Daniel Israel View Post
    Can you revert to a NULL?

    That is, is there a case where a user will select Yes (or No) and later on be able to change it to NULL? Or is this a one-time write?
    It is for a survey, so it is a one-trick pony.

    Either you feel the question does not apply - so you leave it blank - or you choose either Yes or No.


    Debbie


  •  

    Posting Permissions

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