The proper way to insert a NULL into a database with CodeIgniter

Question

I have ExtJS post data from a combofield to CodeIgniter. CodeIgniter reads the posted data and runs db->insert to update the mssql database.

The problem is if the user doesn't choose a selection from the combobox it is sent to CodeIgniter as nothing, Codeignighter then reads this piece of posted data as an empty string and attempts to write that to the int field in the database causing an error.

I believe I need CodeIgniter to treat the string as NULL if it's empty and a number if it is such. I have tried using php type juggling (int) to convert it but it makes it a 0. This is an invalid value because it doesn't match any selection in my one to many int field in my DB.

Is there a way to make CodeIgniter treat empty strings as NULLS instead of empty strings?

[EDITED TO ADD CODE]

{
//ExtJS combo object
xtype: 'combo',
id: 'Referrer',
store: new Ext.data.Store({
    proxy: new Ext.data.HttpProxy({
        url: '/referrals/index.php/referrers/read',
        method: 'POST'
    }),
    reader: new Ext.data.JsonReader({
        root: 'results',
        fields: [
            {name: 'ID'},
            {name: 'Referrer'}
        ]
    })
}),
displayField: 'Referrer',
valueField: 'ID',
value: 1,
typeAhead: true,
hiddenName: 'Referrers_ID',
mode: 'remote',
triggerAction: 'all',
fieldLabel: 'Referrer',
selectOnFocus: true,
anchor: '100%'
}

//CI CODE TO GRAB FROM POST INTO AN ARRAY THEN OUT TO THE DB
public function create(){
    $data = array(  
        'FirstName' => $this->input->post('FirstName', false),
        'LastName' => $this->input->post('LastName', false),
        'DOB' => $this->input->post('DOB', false),
        'Email' => $this->input->post('Email', false),
        'Phone' => $this->input->post('Phone', false),
        'StudentNo' => $this->input->post('StudentNo', false),
        'Sex' => $this->input->post('Sex', false),
        'Advisors_ID' => $this->input->post('Advisors_ID', false),
        'DateSeen' => $this->input->post('DateSeen', false),
        'Classifications_ID' => join(",", $this->input->post('Classifications_ID', false)),
        'Referrers_ID' => $this->input->post('Referrers_ID', false),
        'Referrals' => $this->input->post('Referrals', false),
        'ReferralNotes' => $this->input->post('ReferralNotes', false),
        'Registration1' => $this->input->post('Registration1', false),
        'Registration2' => $this->input->post('Registration2', false),
        'Notes' => $this->input->post('Notes', false)
    );

    $this->db->insert('Clients', $data);
    $insert_id = $this->db->insert_id();
}

Answer 1

Assuming your variable is named $myvar AND assuming you've done all the error checking, validation and casting before this.

$myvar  = empty($myvar) ? NULL : $myvar;

This will give the correct data to codeignitor.

Answer 2

There's two ways of solving the issue aforementioned.

Firstly, you can just change the database (assuming you're using MySQL) field from "NOT NULL" to "NULL" so empty strings are automatically converted to a NULL by MySQL and would require no change from your code.

Secondly, you can run a query to tell CodeIgniter to null the string as such

$r = ($this->input->post('r') != FALSE) ? $this->input->post('r') : NULL;

If you're going to need the 'null' function more often, consider creating a library or helper within CodeIgniter with the code above in the form of a function.