• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Codeigniter Activerecord update method refuses to insert NULL value

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?

$data['reservation_id']    = '';

Try that and see if it works


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?

[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);

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


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?

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.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

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