Checkbox Empty - NULL, or default value of 'no'?

A small issue, but I have never come across this problem before:

I have a checkbox in my form that has a value of ‘yes’. I want ‘no’ to be inserted into the database if the checkbox is not checked. I tried two strategies:

  1. I made the field nullable, with a default value of ‘no’ - the result was NULL being inserted into the database.

  2. I made the field not nullable, with a default value of ‘no’, thinking that ‘no’ would be inserted automatically - the result was an error message that the field could not be NULL.

How do I get the default value of ‘no’ to work without having to resort to coding it into my PHP controller?

1 Like

here you go, a test suite just for you

run each INSERT statement separately, then run a SELECT to inspect the rows that were inserted

CREATE TABLE checkboxtable ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT , foobar VARCHAR(9) , checkbox CHAR(3) NOT NULL DEFAULT 'no' ); INSERT INTO checkboxtable ( foobar , checkbox ) VALUES ( 'foo' , 'yes' ) , ( 'bar' , 'no' ) ; INSERT INTO checkboxtable ( foobar , checkbox ) VALUES ( 'fap' , NULL ) ; INSERT INTO checkboxtable ( foobar ) VALUES ( 'foobar' ) ;

1 Like

Thank you. I got the expected results.

I’m using

`guidelines_signed` varchar(3) NOT NULL DEFAULT 'no'

But still, when I complete the form and leave the checkbox empty, I get the error message

Column 'guidelines_signed' cannot be null 
INSERT INTO `clients`
 (`account_num`, `title`, `first_name`, `middle_name`, `last_name`, 
`age`, `sex`, `guidelines`, `guidelines_signed`, `signed_date`, 
`home_address1`, `home_address2`, `home_city`, `home_prov`, 
`home_postal`, `home_phone`, `phone_msg`, `cell_phone`, `work_phone`, 
`work_ext`, `email`, `home_mail`, `mail_address1`, `mail_address2`, 
`mail_city`, `mail_prov`, `mail_postal`, `contact_notes`, 
`inquiry_type`, `other_inquiry_type`, `ask_notes`, `visit_date`, 
`time_spent`, `assignment_date`, `coordinator_id`, `referred_by`, 
`repeat_client`, `repeat_notes`, `careplanner_id`, `case_status`, 
`status_change_date`, `church_id`) VALUES ('16-00028-ff', 'Ms', 'f', '',
 'f', '17', 'Female', 'yes', NULL, '', 'f', '', 'd', 'ON', '', '', 'no',
 '', '', '', '', 'yes', '', '', '', 'ON', '', '', 'General Counselling',
 '', '', '2016-03-11', '4', '', '3', '', 'not sure', '', '', 'Active', 
NULL, '1')"

I’m using phpmyadmin in XAMPP to develop this application. Could it be that there is an issue with my installation of phmyadmin?

1 Like

nope, it appears to be an issue of you assigning NULL to that column

that’s not what my example should have shown you

But I didn’t assign it the value NULL. It comes from leaving a checkbox with name ‘guideline_signed’ unchecked. And my database fields was set as NOT NULL with a default value of ‘no’.

If the checkbox were checked, its value would have been set to ‘yes’.

Where did the NULL come from?

Could this be CodeIgniter 3 playing tricks behind the scenes?

When the input from any form is sent to PHP for any checkbox fields, if the checkbox isn’t checked/selected then absolutly nothing will be sent to PHP for that field (nothing will appear for it at all in the POST or GET array).

Where you’re inserting the value for guidelines_signed do:

COALESCE( ?, 'No')

You need to change the ? to whatever the placeholder is for the prepared statement that you’re using

So that means that my setting the default value of the field in the database to ‘no’ when I constructed the mySQL table has absolutely no effect?

on the contrary, it works just fine, as my example proved

what you are doing wrong is not using the correct syntax in your INSERT statement

to spell it out, you want to ~omit~ the column name from the INSERT statement’s column list, along with the NULL in the VALUES list

I’m using CodeIgniter 3 and its database query functions instead of building my own. I’m beginning to think there’s something off about the way CodeIgniter deals with things because this is not the first time I’ve gotten unexpected results in this project. And it always has to do with inserting and empty fields, NULLS and default values.

Okay, problem solved. I finally figured out where my thinking was going wrong (and it wasn’t with the database, it was with my logic).

There’s a difference between leaving a checkbox unchecked but passing a value to the $_POST array, and leaving a checkbox unchecked and skipping over this field when all the values are inserted in the database.

So I used a conditional to skip over ‘guidelines_signed’ if it was not checked and the default value of ‘no’ was entered instead of NULL as desired.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.