Storing a Yes / No radio button into a MySQL database

I've seen a Yes/No form radio buttons value be stored/saved in a couple different ways. I wonder which way is better and why? This is for a PHP/MySQL application with a typi开发者_StackOverflowcal Yes/No question as part of a form.

1.) Store it as 1, 0 or null. 1 being Yes, 0 being No and null being not answered.

2.) Store it as Yes, No, null. Assume a language conversion can be made.

3.) Use 1, 2 and null so as to better distinct the values.

Thanks, Jeff

Edit: I also must mention that most of the issues have been arising due to jQuery/JavaScript and the comparisons and $() bindings.

Use TINYINT(1). Allow NULL if you wish for the "not answered" option. You can also use BOOLEAN as it's just an alias for the aforementioned datatype. This way of storing boolean data is recommended by MySQL.

More details: Which MySQL data type to use for storing boolean values

Since MySQL has a BOOLEAN type, but it's simply an alias of TINYINT. I recommend against it because the equal sign in PHP == would not distinguish 0 from the lack of value. You'd always need to use triple equal === and it would be easy to make mistakes.

As for your options:

1. This seems the natural choice with PHP, but then you've to be careful to distinguish 0 from the lack of value, so I wouldn't recommend it.

2. I would not recommend this one.

3. Possible, but the assignment to 1 and 2 is somewhat arbitrary and might be difficult to remember and read in code.

What I usually do, is use "Y", "N" and NULL if needed, in a CHAR(1) field, it reads well in code and doesn't create problems.

I would go with the 0/1/null for No/Yes/Blank. 0 is always used as false and 1 for true.

I don't know if it helps, but in my system I use 1 as yes, 0 as no and just NO value as null - or if I have to specify I set a default value in the structure.

I think this system is more flexible, you can always manipulate with this data, for example if you don't want to display 0/1 values you can set something like

if(table.field == 1)
    echo yes;
else
    echo no;

Also comparing this value to any other database value is easier.

as numbers are processed faster (while searching, sorting,..) by mysql, it takes less space in ur case, and also there are only 3 values (1, 0 nul l) and in binary format ,1 and 0 have just one significant bit (0000000, & 0000001), the speed, in case of any comparisons, while traversing these columns, should remain higher and queries will take less time.

so i think u can go for first option.

I think that's what ENUM type is for. You can set your storage field type like this:

ENUM('no','yes')

and allow NULL as default value. If the answer is 'no' - the field value will actually be 0 (the index of 'no') and 1 if the answer is 'yes'. And you will have a nice representation of the column - 'yes' and 'no' instead of 1 and 0. Though the values will be actially stored as 0 and 1. I think its an advantage of using TINYINT.