Database / Form Select general question

So this question is really just a very generalised one, just for my own brain to make sure I am doing things correctly.

Here is the scenario
I have a form on my page that has a text field input and a select element and a submit button
text field is not required
select is not required options (Please Select - value="")(Yes - value=“yes”)(No - value=“no”)

I have a database that only takes these two entries, the first field in the database is set to varchar(100) and the second is set to enum(“yes”,“no”) and both allow NULL.

Now if I inserted the form to the database with no fields entered at all, just for testing purposes, why does the varchar have NULL inserted while the enum from the select is just blank, why is the value not equal to NULL rather, do I have to set the value of Please Select to NULL manually?

Just wondering how others handle this type of scenario.

Hi Paul,
value="" is an empty string, that’s why it is inserted as an empty string. If you want it to default to null use the default filter in the insert record step to add null.

That was what I thought however i think my confusion is still, if enum ONLY allows the set values of yes or no then that should be the only two values that could ever possibly go into there, if I then add that I will also allow NULL, then surely there are only 3 possible values of yes, no, or NULL, so why does it not fail if the value is actually just a blank?
Lets see what the database veterans think too @Hyperbytes @TomD @ben

Well, that’s something controlled by your database configuration. Check the discussion here: https://stackoverflow.com/questions/1868380/mysql-enums-always-contain-empty-string-in-possibilities
Also check MySQL manual :slight_smile: I’m also not a fan of using enum and avoid it.

1 Like

Thanks @Teodor, let me have a read, can you tell me what you would use rather than enum please. Is enum not a good thing to use?

Well, I’m not saying you shouldn’t be using it, but when having yes/no 1/0 true/false I prefer bool/tinyint(1) field types.

1 Like

Well looks like I learnt a new thing for today, haha, I have been using enum for so long and never realised that blank was a valid value or invalid value identifier should i say. haha, wonder how many apps I have out there with that little issue.
Thanks for confirming, I think I will change to bool in future for when there are only 2 possible outcomes.

2 Likes

Hi Paul, I would typically use tinyint(1) for this too and use a checkbox. However there is sometimes an advantage in having a field with yes/no - eg if you want users to be able to search by either value.

2 Likes

Yes, got to agree with all, i always use booleans as a tiny int value 1/0
I am not a fan of enum either; i prefer values in a separate table where possible and use keys to reference as this allows the table to be used in a dynamic form select rather than having to enter the values by hand. It makes managing changes so much easier. If a select is used ten times in a site then all 10 need to be updated with any new content and there is always a danger you miss one. With a separate table you simply need to add a new value and they is automatically reflected in the entire suite, no need for code changes, just a quick edit with your MySql editor if necessary.
It does of course make the SELECT a little more complex
Now if you could easily SELECT * from {enumfield} it may be different

3 Likes