• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Database INSERT changes default 'no' to NULL

#1
I have a checkbox with a value of 'yes' :
PHP Code:
<div class="form-control">
       
Check the box if a signed confidentiality guideline is on file for this client.
       <?
php 
        $data 
= array(
            
'name'  => 'guidelines_signed',
            
'value' => 'yes',
            
'checked' => (set_value('guidelines_signed') === 'yes' TRUE FALSE),
            
'style' => 'margin-left: 20px;'
                 
);        
        echo 
form_checkbox($dataset_checkbox('guidelines_signed')); 
    
?>
</div><!-- end of .form-control --> 

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

So 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.
Code:
`guidelines_signed` varchar(3) NOT NULL DEFAULT 'no'

This is not the first time I have had a problem like this working with CodeIgniter. Is there something going on behind the scenes with the query builder that is doing this?
Reply

#2
You should show some code related to the actual insert, not just your form. It sounds like you are providing null, and if that's the case, neither CI or MySQL is going to magically turn that null into a no.
Reply

#3
Here is the code from my controller:

PHP Code:
$client_data = array(
                
'account_num' => $client_acct_num,
                
'title' => $this->input->post('title'),
                
'first_name' => $this->input->post('first_name'),
                
'middle_name' => $this->input->post('middle_name'),
                
'last_name' => $this->input->post('last_name'),
                
'age' => $this->input->post('age'),
                
'sex' => $this->input->post('sex'),
                
'guidelines' => $this->input->post('guidelines'),
                
'guidelines_signed' => $this->input->post('guidelines_signed'),
                
'signed_date' => $this->input->post('signed_date'),
                
'home_address1' => $this->input->post('home_address1'),
                
'home_address2' => $this->input->post('home_address2'),
                
'home_city' => $this->input->post('home_city'),
                
'home_prov'  => $this->input->post('home_prov'),
                
'home_postal'  => $this->input->post('home_postal'),
                
'home_phone' => $this->input->post('home_phone'),
                
'phone_msg' => $this->input->post('phone_msg'),
                
'cell_phone' => $this->input->post('cell_phone'),
                
'work_phone' => $this->input->post('work_phone'),
                
'work_ext' => $this->input->post('work_ext'),
                
'email' => $this->input->post('email'),
                
'home_mail' => $this->input->post('home_mail'),
                
'mail_address1' => $this->input->post('mail_address1'),
                
'mail_address2' => $this->input->post('mail_address2'),
                
'mail_city' => $this->input->post('mail_city'),
                
'mail_prov' => $this->input->post('mail_prov'),
                
'mail_postal' => $this->input->post('mail_postal'),
                
'contact_notes' => $this->input->post('contact_notes'),
                
'inquiry_type' => $this->input->post('inquiry_type'),
                
'other_inquiry_type' => $this->input->post('other_inquiry_type'),
                
'ask_notes' => $this->input->post('ask_notes'),
                
'visit_date' => $this->input->post('visit_date'),
                
'time_spent' => $this->input->post('time_spent'),
                
'assignment_date' => $this->input->post('assignment_date'),
                
'coordinator_id' => $user_id,
                
'referred_by' => $this->input->post('referred_by'),
                
'repeat_client' => $this->input->post('repeat_client'),
                
'repeat_notes' => $this->input->post('repeat_notes'),
                
'careplanner_id' => $this->input->post('careplanner_id'),
                
'case_status' => $this->input->post('case_status'),
                
'status_change_date' => $this->input->post('status_change_date'),
                
'church_id' => $this->user_model->get_user_church($user_id)->church_id
            
);
                    
            
            if (
$this->registration_model->create_client($client_data))     {
                
$this->session->set_flashdata('client_registered''New client has been registered.');
                
redirect('coordinators');
            } else {
                
$this->session->set_flashdata('client_registration_failed''New client could not be registered. Please try again or contact an administrator.');
                
redirect('coordinators');
            } 

and here is the method from the model that does the insert:
PHP Code:
    public function create_client($client_data) {
    
        
$query $this->db->insert('clients'$client_data);
                
        return 
true;
        
    } 

I am fairly new to CI, and I can't see how any of this will tell me exactly what value will be inserted for 'guidelines_signed'. The query translates as
PHP Code:
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')
Reply

#4
Hi

First of all you need to check checkbox - checked or not like below:
if (isset($_POST['guidelines_signed']) && $_POST['guidelines_signed'] == 'on')
{
//if checked than append value in insert array
$client_data['guidelines_signed'] = $this->input->post('guidelines_signed');
}
else
{
//if uncheked skip to add check box value
}
and also set null default value for "guidelines_signed" column in database table
Reply

#5
You're explicitly inserting NULL ... of course that's what's going to be inserted. The default value is used only if you provide no value at all - i.e. if the field in question is not listed in the INSERT statement.
Reply

#6
(03-06-2016, 11:34 PM)Narf Wrote: You're explicitly inserting NULL ... of course that's what's going to be inserted. The default value is used only if you provide no value at all - i.e. if the field in question is not listed in the INSERT statement.

So if I test for a value

PHP Code:
if (isset($_POST['guidelines_signed']) && $_POST['guidelines_signed'] == 'on')
{
//if checked than append value in insert array
$client_data['guidelines_signed'] = $this->input->post('guidelines_signed');


and skip this field if the checkbox is not selected, the default value I gave it in the database will be inserted instead?
Reply

#7
Code:
$client_data['guidelines_signed'] = $this->input->post('guidelines_signed') == 'yes'
  ? 'yes'
  : 'no';
Reply

#8
(03-07-2016, 04:23 PM)webmachine Wrote:
(03-06-2016, 11:34 PM)Narf Wrote: You're explicitly inserting NULL ... of course that's what's going to be inserted. The default value is used only if you provide no value at all - i.e. if the field in question is not listed in the INSERT statement.

So if I test for a value

PHP Code:
if (isset($_POST['guidelines_signed']) && $_POST['guidelines_signed'] == 'on')
{
//if checked than append value in insert array
$client_data['guidelines_signed'] = $this->input->post('guidelines_signed');


and skip this field if the checkbox is not selected, the default value I gave it in the database will be inserted instead?

Yes
Reply

#9
You could also modify your insert array like this:


Code:
$client_data = array(
    'account_num' => $client_acct_num,
    'title' => $this->input->post('title'),
    'first_name' => $this->input->post('first_name'),
    'middle_name' => $this->input->post('middle_name'),
    'last_name' => $this->input->post('last_name'),
    'age' => $this->input->post('age'),
    'sex' => $this->input->post('sex'),
    'guidelines' => $this->input->post('guidelines'),
    'guidelines_signed' => ( $this->input->post('guidelines_signed') == 'yes' ? 'yes' : 'no' ),
    'signed_date' => $this->input->post('signed_date'),
    'home_address1' => $this->input->post('home_address1'),
    'home_address2' => $this->input->post('home_address2'),
    'home_city' => $this->input->post('home_city'),
    'home_prov'  => $this->input->post('home_prov'),
    'home_postal'  => $this->input->post('home_postal'),
    'home_phone' => $this->input->post('home_phone'),
    'phone_msg' => $this->input->post('phone_msg'),
    'cell_phone' => $this->input->post('cell_phone'),
    'work_phone' => $this->input->post('work_phone'),
    'work_ext' => $this->input->post('work_ext'),
    'email' => $this->input->post('email'),
    'home_mail' => $this->input->post('home_mail'),
    'mail_address1' => $this->input->post('mail_address1'),
    'mail_address2' => $this->input->post('mail_address2'),
    'mail_city' => $this->input->post('mail_city'),
    'mail_prov' => $this->input->post('mail_prov'),
    'mail_postal' => $this->input->post('mail_postal'),
    'contact_notes' => $this->input->post('contact_notes'),
    'inquiry_type' => $this->input->post('inquiry_type'),
    'other_inquiry_type' => $this->input->post('other_inquiry_type'),
    'ask_notes' => $this->input->post('ask_notes'),
    'visit_date' => $this->input->post('visit_date'),
    'time_spent' => $this->input->post('time_spent'),
    'assignment_date' => $this->input->post('assignment_date'),
    'coordinator_id' => $user_id,
    'referred_by' => $this->input->post('referred_by'),
    'repeat_client' => $this->input->post('repeat_client'),
    'repeat_notes' => $this->input->post('repeat_notes'),
    'careplanner_id' => $this->input->post('careplanner_id'),
    'case_status' => $this->input->post('case_status'),
    'status_change_date' => $this->input->post('status_change_date'),
    'church_id' => $this->user_model->get_user_church($user_id)->church_id
);
Reply

#10
i write this like that for me

'guidelines_signed' => isset($this->input->post('guidelines_signed')) ? 'yes' : 'no',

because guidelines_signed is not send if it's not checked.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.