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();
}

lets initally assume it's a problem with ***your*** code, and not codeigniter's code... speaking of, we have yet to see any of your code... — jondavidjohn, Mar 17 '11 at 17:56
My first thought is: "why is a NULL value OK, but 0 not?" By your own admission, a NULL value will be just as much an orphan as a 0 — horatio, Mar 17 '11 at 18:02
No I'm not assuming it's a problem with Codeignighter, I don't know I'd call it a problem with mine either. Really I'm wondering if there is a way for CI to take the empty strings it grabs from POST and pass them to the DB as NULLS instead of strings since that's what they enter CI, nothing, not an empty string. — Chris, Mar 17 '11 at 18:06

score 11 · Accepted Answer · answered Mar 17 '11 at 18:02

11

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.

answered Mar 17 '11 at 18:02

JohnP

49,507
13
108
140

I tried: $ktdata = array('kt_duration' => empty($this->input->post('duration')) ? NULL : $this->input->post('duration')); in my controller to pass on to $this->db->insert() and got error: Fatal error: Can't use method return value in write context – Zeeshan Jun 08 '14 at 08:47
OK I got an explanation here: http://stackoverflow.com/questions/6730216/cant-use-method-return-value-in-write-context and using a negation with ! helped instead of empty() in my case. – Zeeshan Jun 08 '14 at 09:04

score 1 · Answer 2 · answered Mar 17 '11 at 18:06

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.

JohnP's solution worked but thank you for pointing out != FALSE as well as the library suggestion. — Chris, Mar 17 '11 at 18:25

score 0 · Answer 3 · answered May 23 '16 at 07:56

This worked for me :) I assigned double single quotes string whenever the input value is NULL or empty

for($x = 0 ; $x < $count ; $x++) {
    foreach(array_keys($_POST) as $col) {
        $data[$this->col_prefix.$col] = empty($_POST[$col][$x]) ? "''" :  $_POST[$col][$x];
    }
    if($this->transaction_detail_model->add_transaction_detail($data)) {
        $flash_data = array('status' => 1, 'message'=> 'Transaction successful saved!');
    }   
}

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

3 Answers3