Welcome Guest, Not a member yet? Register   Sign In
Codeigniter Activerecord update method refuses to insert NULL value
#1

[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?
#2

[eluser]InsiteFX[/eluser]
Code:
$data['reservation_id']    = '';

Try that and see if it works

InsiteFX
#3

[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?
#4

[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');
#5

[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
#6

[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?
#7

[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!




Theme © iAndrew 2016 - Forum software by © MyBB