MySQL error 1366 appears when leaving non-null columns with default values as blank

Question

I am new to CodeIgniter and PHP in general. I made a new table with certain non-null columns. The non null columns all have defaults set according to their data type, so a VARCHAR has an empty string, while numeric types have 0 as default.

However, once I fill out the form (where I purposely leave the non-null columns blank to test them), and hit the submit button, it gives the following error:

Error Number: 1366

Incorrect integer value: '' for column 'salary' at row 1

It is inserting the double quotation marks for an empty string when it finds the user has not entered any value. I checked checked the mysql mode and it was turned to strict mode. However as I am using a multitenant DB (Azure-clearDB), they won't allow me super privileges and I cannot turn off the strict mode (or could I?)

Is there a way to turn off this mode, or is there any other workaround? I don't want to explicitly add an SQL if-else clause for each column, as that would be hard coded. Please help The code is below:

CONTROLLER:

$additional_data = array(
                'first_name'    => $this->input->post('first_name'),
                'last_name'     => $this->input->post('last_name'),
                'phone'         => $this->input->post('phone'),
                'salary'        => $this->input->post('salary'));

if ($this->form_validation->run() == true && $this->ion_auth->register($username, $password, $email, $additional_data))
        {
        $identity = $this->ion_auth->where('email', strtolower($this->input->post('email')))->users()->row();
        $forgotten = $this->ion_auth->forgotten_password($identity->{$this->config->item('identity', 'ion_auth')});
        $this->session->set_flashdata('message', $this->ion_auth->messages());
        redirect('auth/success', 'refresh');

MODEL:

    //filter out any data passed that doesnt have a matching column in the users table
    //and merge the set user data and the additional data
    $user_data = array_merge($this->_filter_data($this->tables['users'], $additional_data), $data);

    $this->trigger_events('extra_set');

    $this->db->insert($this->tables['users'], $user_data);

    $id = $this->db->insert_id();

    //add in groups array if it doesn't exits and stop adding into default group if default group ids are set
    if( isset($default_group->id) && empty($groups) )
    {
        $groups[] = $default_group->id;
    }

    if (!empty($groups))
    {
        //add to groups
        foreach ($groups as $group)
        {
            $this->add_to_group($group, $id);
        }
    }

    $this->trigger_events('post_register');

    return (isset($id)) ? $id : FALSE;

UPDATE: I am inserting multiple column values with the same insert statement.

Answer 1

According your description, you have set the column 'salary' an integer column, but you insert '' as an empty string.

so you need to set 0 instead of '' in column 'salary'.