Codeigniter Activerecord update method refuses to insert NULL value

Codeigniter Activerecord update method refuses to insert NULL value - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Codeigniter Activerecord update method refuses to insert NULL value (/showthread.php?tid=33177)

Codeigniter Activerecord update method refuses to insert NULL value - El Forum - 08-18-2010

[eluser]jomanlk[/eluser]
I'm using Codeigniter's Active record library to carry out an update on a column on my DB.

Here's the SQL for the table

Code:
CREATE TABLE `schedules` (

    `id` INT(11) NOT NULL AUTO_INCREMENT,

    `reservation_id` INT(11) NULL DEFAULT NULL,

    `title` VARCHAR(255) NOT NULL,

    `description` VARCHAR(512) NULL DEFAULT NULL,

    `start_date` DATE NOT NULL,

    `start_time` TIME NOT NULL,

    `end_time` TIME NULL DEFAULT NULL,

    `enabled` TINYINT(1) NULL DEFAULT '1',

    `status` ENUM('OPEN','RESERVED') NULL DEFAULT 'OPEN',

    PRIMARY KEY (`id`),

    INDEX `fk_schedules_reservations` (`reservation_id`),

    CONSTRAINT `fk_schedules_reservations` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION

)

I've declared reservation_id as nullable

Code:
`reservation_id INT(11) NULL DEFAULT NULL`

The problem is that CI doesn't seem to want to send a NULL value when I create the statement.

Code:
$data['status']    = $this->Schedule->get_status_open();

    $data['reservation_id']    = null; 

    $this->Schedule->update($s_id, $data);

That bit of code just generates the following error message

Code:
Error Number: 1452

    Cannot add or update a child row: a foreign key constraint fails (`ethyme/schedules`, CONSTRAINT `fk_schedules_reservations` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

    UPDATE `schedules` SET `status` = 'OPEN', `reservation_id` = '' WHERE `id` = '4'

From what I know, all you have to do is set the value to NULL and AR should pick up that it's a NULL value, but that doesn't seem to be the case here. Just keeps sending empty values.

I'm new to CI, do I have to do anything else to make it work? any ideas?

Codeigniter Activerecord update method refuses to insert NULL value - El Forum - 08-18-2010

[eluser]InsiteFX[/eluser]

Code:
$data['reservation_id']    = '';

Try that and see if it works

InsiteFX

Codeigniter Activerecord update method refuses to insert NULL value - El Forum - 08-18-2010

[eluser]jomanlk[/eluser]
Nope, that just puts in an empty string. Is there any particular reason this should fail for me? I'd assume this is a pretty common use case. I tried putting TRUE in there, and CI converts it to '1'. And FALSE puts in an empty string as well.

Any way I can remove the single quotes?

Codeigniter Activerecord update method refuses to insert NULL value - El Forum - 08-18-2010

[eluser]Armchair Samurai[/eluser]
Set the third parameter to FALSE to stop CI escaping submitted data.

Code:
$this->db->set('status', 'OPEN');

$this->db->set('reservation_id', 'NULL', FALSE);

$this->db->where('id', 4);

$this->db->update('schedules');

Codeigniter Activerecord update method refuses to insert NULL value - El Forum - 08-18-2010

[eluser]jomanlk[/eluser]
excellent! that worked. Sadly this means I won't be able to use the $data approach unless I work in an intermediary method that iterates through the array and sets the data. Anyway, problem solved. Thanks for your help everyone

Codeigniter Activerecord update method refuses to insert NULL value - El Forum - 08-19-2010

[eluser]WanWizard[/eluser]
Odd.

I just tested

Code:
$data = array('status' => 'OPEN', 'reservation_id' => NULL);

$this->db->where('id', 4);

$this->db->update('schedules', $data);

echo $this->db->last_query();

on both CI 1.7.2 and CI 2.0, and both produce the query

Code:
UPDATE `schedules` SET `status` = 'OPEN', `reservation_id` = NULL WHERE `id` = 4

which looks fine to me. Which version of CI are you using?

Codeigniter Activerecord update method refuses to insert NULL value - El Forum - 08-19-2010

[eluser]jomanlk[/eluser]
Woah! I'm using 1.7.2. After you pointed out that it worked for you, I tried digging a bit deeper and found the culprit! We use a base model to store all the basic db methods. In that method there's a filter method that loops through the keys to clean them,

Code:
if($xss_clean === true && !in_array($v, $skip_types)) {

    $data[$k] = $this->input->xss_clean($v);

}

xss_clean($v) was the problem! The moment something like NULL, FALSE, TRUE went in, it came out as the string representation. I added an exclusion list and it solved the problem.

Totally felt like a noob for not having looked in there, but all's well that ends well, I guess.

Thanks!