CodeIgniter Forums

Full Version: Codeigniter Activerecord update method refuses to insert NULL value
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

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

CREATE TABLE `schedules` (
    `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',
    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
`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.
$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

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?

El Forum

$data['reservation_id']    = '';

Try that and see if it works


El Forum

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?

El Forum

[eluser]Armchair Samurai[/eluser]
Set the third parameter to FALSE to stop CI escaping submitted data.
$this->db->set('status', 'OPEN');
$this->db->set('reservation_id', 'NULL', FALSE);
$this->db->where('id', 4);

El Forum

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

El Forum


I just tested
$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

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

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

El Forum

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,

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.