Search code examples
phpmysqlboolean

MySQL storing boolean - 1, 0 , or NULL

I've generally stored Boolean values in MyIASM MySQL DBs using TinytInt(1). In a site I'm currently working on, I need to be able to store 1, 0 or NULL. This field is being populated from a radio button in a PHP form.

When I choose 'Yes' in the form (with a value of 1), this gets stored accurately. When I choose 'No' however (with a value of 0), it gets stored as NULL.

I want to reserve NULL for if the user chooses neither 'Yes' or 'No'.

Any idea why the 'No' (0) values aren't storing as expected?

Here's the basic HTML:

Yes <input type='radio' name='video_transfer_dvd_question' value='1' /> 
No <input type='radio' name='video_transfer_dvd_question' value='0' />
Solution

  • In PHP, both '' and 0 are considered FALSE in a boolean context, so if you try to test '' == 0 you'll find the result is TRUE. And empty() only checks whether the argument is boolean FALSE, so it doesn't truly distinguish between an empty $_POST and a $_POST which contains a value that happens to evaluate as FALSE. My suggestion would be to change the values of your radio buttons so than they never evaluate as boolean FALSE, e.g.:

    Yes <input type='radio' name='video_transfer_dvd_question' value='yes' /> 
No <input type='radio' name='video_transfer_dvd_question' value='no' />

    This keeps things more explicit when you test the response and choose a value to send to MySQL:

    if (empty($_POST['button'])) {
    $value = NULL;
} elseif ($_POST['button'] == 'no') {
    $value = 0;
} elseif ($_POST['button'] == 'yes') {
    $value = 1;
}

    You could also use !$_POST['button'] in place of empty($_POST['button']), but that would generate a warning if $_POST['button'] is not set, which you would probably rather not happen.

    You could also change that last elseif block to a simple else, if you have no reason to expect that the possible responses to this radio button will ever change and don't care about being explicit.